NPR and IRR

[dowerk]

So I am taking Econ 381 it is labor economics and we are currently working on calculating the NPR and IRR. I am trying to use my roommate financial calculator while he is abroad with little to no success. I even googled instructions on how to do this darn thing.

Anyway I did it in excel but my professor said I was still getting the wrong answer. It is as follows

Consider and 18-year old who is about to embark on a three-year course of study. The total costs of the course are $18,000, payable in advance. The return take the form of an annual $5,000 earnings premium (paid at year end) that the individual receives from age 21 through age 60. Calculate the net present value of this investment assuming a discount rate of 5%.


If anyone could explain how to accomplish that on a financial caculator or care to look at my excel spreadsheet and give me help or knows the right answer so I could work backwards. All help would be appreciated.

I hope this is in the right section I just stumbled upon this forum looking for help.

Sorry for it being so long but this is due tomorrow and this one question is killing me, thank you so much

Thank thank you
-dowerk


-I posted this in the student forum also just to make sure someone saw it, thanks so much

[kamranACA]

Dear,


Where is link of your excel spread sheet?

You please try to discount 60 instalments of 5000 using the discount rate of 5% and then get the total of present values of all such discounted values. Don't forget that for each of year the discounting factor will be powered/geared up. Say if for first year discounting factor to be multiplied is 1/1.05 then for the second year it would 1/1.05 x 1/1.05 and for third year it would be 1/1.05 x 1/1.05 x 1/1.05 and so on.

Deduct the initial cash investment of 18000 from the gross total of the present values of 60 instalments.

You may get the answer. You can also get such present values of 60 instalments of 5000 each by using annuity formula/table for arrear instalments. Do you know such formula.

However, post here your own calculation so that we may comment.


Regards,


KAMRAN.

[dowerk]

<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica, san" id="quote">quote<hr height="1" noshade id="quote"><i>Originally posted by kamranACA</i>
<br />Dear,


Where is link of your excel spread sheet?

You please try to discount 60 instalments of 5000 using the discount rate of 5% and then get the total of present values of all such discounted values. Don't forget that for each of year the discounting factor will be powered/geared up. Say if for first year discounting factor to be multiplied is 1/1.05 then for the second year it would 1/1.05 x 1/1.05 and for third year it would be 1/1.05 x 1/1.05 x 1/1.05 and so on.

Deduct the initial cash investment of 18000 from the gross total of the present values of 60 instalments.

You may get the answer. You can also get such present values of 60 instalments of 5000 each by using annuity formula/table for arrear instalments. Do you know such formula.

However, post here your own calculation so that we may comment.


Regards,


KAMRAN.
<hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote">

I am having trouble posting the excell spreadsheet however I can show the equation I used for it.

It was SIGMA (5,000/1.05^t)=
where t=years and Sigma tells you to sum all the equations.

Raising it to the t will account for the discounting factor being multiplied year after year.

My answer was $85,085.20 then I subtracted the 18,000 and got $67,085.20.

Thank you again for your response.

[kamranACA]

Dear,

Without going to check the arithmetic accuracy, I guess you have correctly gone through the calculation.


Regards,


KAMRAN.

[tariqab]

Age Period Cash flow Factor NPV Factor @5% PV
18 0 (18,000.00) 1.00 (18,000.00)
19 1 - 0.95 -
20 2 - 0.91 -
21 3 5,000.00 0.86 4,319.19
22 4 5,000.00 0.82 4,113.51
23 5 5,000.00 0.78 3,917.63
24 6 5,000.00 0.75 3,731.08
25 7 5,000.00 0.71 3,553.41
26 8 5,000.00 0.68 3,384.20
27 9 5,000.00 0.64 3,223.04
28 10 5,000.00 0.61 3,069.57
29 11 5,000.00 0.58 2,923.40
30 12 5,000.00 0.56 2,784.19
31 13 5,000.00 0.53 2,651.61
32 14 5,000.00 0.51 2,525.34
33 15 5,000.00 0.48 2,405.09
34 16 5,000.00 0.46 2,290.56
35 17 5,000.00 0.44 2,181.48
36 18 5,000.00 0.42 2,077.60
37 19 5,000.00 0.40 1,978.67
38 20 5,000.00 0.38 1,884.45
39 21 5,000.00 0.36 1,794.71
40 22 5,000.00 0.34 1,709.25
41 23 5,000.00 0.33 1,627.86
42 24 5,000.00 0.31 1,550.34
43 25 5,000.00 0.30 1,476.51
44 26 5,000.00 0.28 1,406.20
45 27 5,000.00 0.27 1,339.24
46 28 5,000.00 0.26 1,275.47
47 29 5,000.00 0.24 1,214.73
48 30 5,000.00 0.23 1,156.89
49 31 5,000.00 0.22 1,101.80
50 32 5,000.00 0.21 1,049.33
51 33 5,000.00 0.20 999.36
52 34 5,000.00 0.19 951.77
53 35 5,000.00 0.18 906.45
54 36 5,000.00 0.17 863.29
55 37 5,000.00 0.16 822.18
56 38 5,000.00 0.16 783.03
57 39 5,000.00 0.15 745.74
58 40 5,000.00 0.14 710.23
59 41 5,000.00 0.14 676.41
60 42 5,000.00 0.13 644.20
NPV 59,818.99

NPV should be 59,118.99 as per above calculation, if you can read

[Muhammad Amir]

Although, the above solution is right but for exam point of view this is laborious. Why don't you use annuity formula?

Present Value Of Annuity = (1/R) – [1/{R*(1+R)^n}].

Where;

R=Interest Rate or Cost of Capital.
n=Number of Periods.

Now, the solution of your question according to annuity formula is.

At Year 21 Annuity Factor is => (1/0.05) – [1/{0.05*(1+0.05)^40}] = 17.159

At Year 18, which is Time 0 Annuity Factor is = > (1/0.05) – [1/{0.05*(1+0.05)^2}] = 1.859

Now The Factor at Year 0 is => 17.159 – 1.859 => 15.3

Present Value of 40 Annuity Receipts is => 15.3 * 5000 => $76,500

<b>NPV of the project is => $76,500 – 18,000 => <font size="4">+</font id="size4"> $58,500</b>



<b>For IRR-</b>


We are already having + NPV of $58,500 at the discount rate of 5%.

For current pattern of cash flows, discount rate is inversely related to NPV. It means as we increase the interest rate, NPV will be reduced.

If you discount the above cash flows at 25% interest rate you will see a – NPV of -$5,203.

IRR formula = P rate + [+NPV/+NPV <font size="4"><b>-</b></font id="size4"> (<b>-</b>NPV)] * (P rate – N rate).

Where,

P rate = Rate at +ve NPV
N rate = Rate at –ve NPV
+NPV = Positive NPV of Cash flows
-NPV = Negative NPV of Cash flows

IRR = 5% + [58,500/58,500 – (-5,203)]*(25-5) => 23.4%.

<b>IRR = 23.4% at which NPV is Zero.</b>

Now, if you discount your cash flows at 23.4% then you roughly get Zero NPV.

[kamranACA]

Dear Amir,

You are right, for exam purpose one should use annuity formula.

Tariqab, thanks for correcting that the returns have to be received from 21 to 60 years and the installments were not 60. I just overlooked it.


Regards,


KAMRAN.

[tariqab]

Dear Amir

Thanks for reminding Annuity-PV but you have to check your IRR calculation. It should b 19.45 %.

[Muhammad Amir]

This is due to some decimal discrepancies. My NPV is also different than you.