NPR and IRR - Printable Version

NPR and IRR - Printable Version

+- Accountancy Forum (https://www.accountancy.com.pk/forum)
+-- Forum: The Profession (https://www.accountancy.com.pk/forum/forumdisplay.php?fid=4)
+--- Forum: Accounting and Audit (https://www.accountancy.com.pk/forum/forumdisplay.php?fid=7)
+--- Thread: NPR and IRR (/showthread.php?tid=5347)

NPR and IRR - dowerk - 01-29-2009

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 - 01-29-2009

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 - 01-30-2009

<blockquote id="quote">quote<hr height="1" noshade id="quote">Originally posted by 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.
<hr height="1" noshade 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 - 01-30-2009

Dear,

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

Regards,

KAMRAN.

- tariqab - 02-01-2009

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 - 02-02-2009

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

NPV of the project is => $76,500 â 18,000 => + $58,500

For IRR-

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 - (-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%.

IRR = 23.4% at which NPV is Zero.

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

- kamranACA - 02-02-2009

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 - 02-02-2009

Dear Amir

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

- Muhammad Amir - 02-02-2009

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