Question

Could you please calculate the NPV. I always get the wrong answer.

Tom now considers getting a two-year graduate degree - Tuition is $10,000 per year payable at the beginning of each year - Tom assumes he gets a job right upon graduation. The expected job pays a sign-on bonus of $10,000 immediately when he starts the job and then a salary (paid at the end of each year) of $104.000. Tom expects the salary to grow by 3.5% every year and still wants to retire 37 years from now. - Does it pay to get the degree (discount rate=4%)?

Answer 1

B2 X fic end of year end of year cash flow present value factor @ 4% | present value of cash flow $ 10,000.00 1.000 $ 10,000.00 $ 10,000.00 0.962 -$ 9,615.38 $ 10,000.00 0.925 $ 9,245.56 $ 104,000.00 0.889 $ 92,455.62 $ 107,640.00 0.855 $ 92,011.12 $ 111,407.40 0.822 $ 91,568.76 $ 115,306.66 0.790 $91,128.53 $ 119,342.39 0.760 $ 90,690.41 $ 123,519.38 0.731 $ 90,254.40 $ 127,842.55 0.703 $ 89,820.48 $ 132,317.04 0.676 $ 89,388.65 $ 136,948.14 0.650 $ 88,958.90 $ 141,741.32 0.625 $ 88,531.21 $ 146,702.27 0.601 $ 88,105.58 $ 151,836.85 0.577 $ 87,682.00 $ 157,151.14 0.555 $ 87,260.45 $ 162,651.43 0.534 $ 86,840.93 $ 168,344.23 0.513 $ 86,423.42 $ 174,236.28 0.494 $ 86,007.93 $ 180,334.55 0.475 $ 85,594.43 $ 186,646.26 0.456 $ 85,182.92 $ 193,178.88 0.439 $ 84,773.38 $ 199,940.14 0.422 $ 84,365.82 $ 206,938.04 0.406 $ 83,960.21 $ 214,180.87 0.390 $ 83,556.56 $ 221,677.20 0.375 $ 83,154.84 $ 229,435.91 0.361 $ 82,755.06 $ 237,466.16 0.347 $ 82,357.20 $ 245,777.48 0.333 $ 81,961.25 $ 254,379.69 0.321 $ 81,567.21 $ 263,282.98 0.308 $ 81,175.06 $ 272,497.88 0.296 $ 80,784.79 $ 282,035.31 0.285 $ 80,396.40 $ 291,906.55 0.274 $ 80,009.88 $ 302,123.27 0.264 $ 79,625.22 $312,697.59 0.253 $ 79,242.41 $ 323,642.00 0.244 $78,861.43 $ 334,969.47 0.234 $ 78,482.29 net present value (NPV) = $ 2,974,564.95

for formulas and calculations, refer to the image below -

B2 X face end of year B wMDN 00 end of yearl cash flow present value factor @ 4% -10000 -10000 =D3/1.04 10000 =D4/1.04 104000 =D5/1.04 =C6*1.035 =D6/1.04 =C7*1.035 =D7/1.04 =C8*1.035 =D8/1.04 =C9*1.035 =D9/1.04 =C10*1.035 =D10/1.04 =C11*1.035 =D11/1.04 =C12*1.035 =D12/1.04 =C13*1.035 =D13/1.04 =C14*1.035 =D14/1.04 =C15*1.035 =D15/1.04 =C16*1.035 =D16/1.04 =C17*1.035 =D17/1.04 =C18*1.035 =D18/1.04 =C19*1.035 =D19/1.04 =C20*1.035 =D20/1.04 =C21*1.035 =D21/1.04 =C22*1.035 =D22/1.04 =C23*1.035 =D23/1.04 =C24*1.035 =D24/1.04 =C25*1.035 =D25/1.04 =C26*1.035 =D26/1.04 =C27*1.035 =D27/1.04 =C28*1.035 =D28/1.04 =C29*1.035 =D29/1.04 =C30*1.035 =D30/1.04 =C31*1.035 =D31/1.04 =C32*1.035 =D32/1.04 =C33*1.035 =D33/1.04 =C34*1.035 =D34/1.04 =C35*1.035 =D35/1.04 =C36*1.035 =D36/1.04 =C37*1.035 =D37/1.04 =C38*1.035 =D38/1.04 =C39*1.035 =D39/1.04 net present value (NPV) = present value of cash flow =C3*D3 =C4*D4 =C5*D5 =C6*D6 =C7*D7 =C8*D8 =C9*D9 =C10*D10 =C11*011 =C12*D12 =C13*D13 =C14*D14 C15*D15 =C16*D16 =C17*D17 =C18*D18 =C19*D19 =C20*D20 =C21*D21 =C22*D22 =C23*023 =C24*D24 =C25*D25 =C26*D26 =C27*D27 =C28*D28 =C29*D29 =C30*D30 =C31*031 =C32*D32 =C33*D33 =C34*D34 =C35*D35 =C36*D36 =C37*D37 =C38*D38 =C39*D39 =C40*D40 =SUM(E3:E40)

In case you have any query, kindly ask in comments.