01292009, 02:18 PM,


dowerk
Junior Member

Posts: 4
Threads: 2
Joined: Jan 2009
Reputation:
0


NPR and IRR
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 18year old who is about to embark on a threeyear 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


01292009, 08:37 PM,


kamranACA
Posting Freak

Posts: 2,351
Threads: 42
Joined: Feb 2007
Reputation:
0


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.


01302009, 12:25 AM,


dowerk
Junior Member

Posts: 4
Threads: 2
Joined: Jan 2009
Reputation:
0


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


01302009, 05:24 PM,


kamranACA
Posting Freak

Posts: 2,351
Threads: 42
Joined: Feb 2007
Reputation:
0


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


02012009, 10:45 PM,


tariqab
Junior Member

Posts: 47
Threads: 1
Joined: Feb 2009
Reputation:
0


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


02022009, 02:00 AM,


Muhammad Amir
Posting Freak

Posts: 782
Threads: 59
Joined: Dec 2006
Reputation:
0


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)]*(255) => 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.


02022009, 06:14 PM,


kamranACA
Posting Freak

Posts: 2,351
Threads: 42
Joined: Feb 2007
Reputation:
0


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.


02022009, 08:13 PM,


tariqab
Junior Member

Posts: 47
Threads: 1
Joined: Feb 2009
Reputation:
0


Dear Amir
Thanks for reminding AnnuityPV but you have to check your IRR calculation. It should b 19.45 %.


02022009, 09:07 PM,


Muhammad Amir
Posting Freak

Posts: 782
Threads: 59
Joined: Dec 2006
Reputation:
0


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


