Question

A new furnace for your small factory will cost $28,000 and a year to install, will require ongoing maintenance expenditures of $1,600 a year. But it is far more fuel-efficient than your old furnace and will reduce your consumption of heating oil by 2,500 gallons per year. Heating oil this year will cost $3 a gallon; the price per gallon is expected to increase by $0.50 a year for the next 3 years and then to stabilize for the foreseeable future. The furnace will last for 20 years, at which point it will need to be replaced and will have no salvage value. The discount rate is 10%. a. What is the net present value of the investment in the furnace? (Do not round intermediate calculations. Round your answer to the nearest whole dollar.) b. What is the IRR? (Do not round intermediate calculations. Enter your answer as a percent rounded to 2 decimal places.) c. What is the payback period? (Do not round intermediate calculations. Round your answer to 2 decimal places.) d. What is the equivalent annual cost of the furnace? (Do not round intermediate calculations. Round your answer to 2 decimal places.) e. What is the equivalent annual savings derived from the furnace? (Do not round intermediate calculations. Round your answer to 2 decimal places.) f. Compare the PV of the difference between the equivalent annual cost and savings to your answer to part (a). Are the two measures the same or is one larger? a. NPV IRR Cumulative cash flows are positive in: Equivalent annual cost Equivalent annual savings Are the two measures the same or is one larger?

Answer 1

a.	NPV	$ 50,850
b.	IRR	33.5921%
c.	Cumulative cash glows are positive in	Year 5
d.	Equivalent Annual Cost	$ 3,288.87

Screenshots are given below for related workings and understanding.

A95 D E F G H I J K L M N O I R S T U V W X 60 61 Cost of Furnace $ -28,000 63 Year 0 1 2 3 4 5 7 8 9 10 11 12 13 14 15 16 17 18 19 20 64 Maintenance Cost $ - $ -1,600 $ -1,600 $ -1,600 $ -1,600 $ -1,600 $ -1,600 $ -1,600 $ -1,600 $-1,600 $ -1,600 $ -1,600 $ -1,600 $ -1,600 $ -1,600 $ -1,600 $ -1,600 $ -1,600 $ -1,600 $ -1,600 $ -1,600 65 Savings in Fuel Cost $ - $ 8,750 $ 10,000 $ 11,250 $ 11,250 $ 11,250 $ 11,250 $ 11,250 $ 11,250 $11,250 $ 11,250 $ 11,250 $ 11,250 $ 11,250 $ 11,250 $ 11,250 $ 11,250 $ 11,250 $ 11,250 $ 11,250 $ 11,250 66 Net Cashflow $ -28,000 $ 7,150 $ 8,400 $ 9,650 $ 9,650 $ 9,650 $ 9,650 $ 9,650 $ 9,650 $ 9,650 $ 9,650 $ 9,650 $ 9,650 $ 9,650 $ 9,650 $ 9,650 $ 9,650 $ 9,650 $ 9,650 $ 9,650 $ 9,650 67 Present Value Factor at 10% 1.0000 0.9091 0.8264 0.7513 0 .6830 0.6209 0.5645 0.5132 0.4665 0.42410.3855 0.3505 0.3186 0.2897 0.2633 0.2394 0.2176 0.1978 0.1799 0.1635 0.1486 68 Present Value $ -28,000 $ 6,500 $ 6,942 $ 7,250 $ 6,591 $ 5,992 $ 5,447 $ 4,952 $ 4,502 $ 4,093 $ 3,720 $ 3,382 $ 3,075 $ 2,795 $ 2,541 $ 2,310 $ 2,100 $ 1,909 $ 1,736 $ 1,578 $ 1,434 69 Net Present Value $ 50,850 70 Cumulative Cashflow $ -28,000 $ -21,500 $ -14,558 $ -7,308 $ -717 $ 5,275 $ 10,722 $ 15,674 $ 20,176 $24,269 $ 27,989 $ 31,372 $ 34,446 $ 37,242 $ 39,783 $ 42,093 $ 44,193 $ 46,102 $ 47,838 $ 49,416 $ 50,850 $ 28,000 $ 6,500 $ -8,058 $-15,366 $ -16,082 $-10,807 $ -84 $ 15,590 $35,766 $60,035 $ 88,024 $ 1,19,396 $ 1,53,842 $ 1,91,084 $ 2,30,867 $ 2,72,960 $ 3,17,153 $ 3,63,255 $ 4,11,093 $4,60,508 $ 5,11,358 72 73 Payback Period = Completed Years + Remaining Amount/Available Amount 6.98 Years $ 6 7 8 9 9,650 $ 9,650 $ 9,650 $ 9,650 0.1652 0.1224 0.0906 0.0671 1,594 $ 1,181 $ 875 $ 648 10 $ 9,650 $ 0.0497 $ 480 $ 11 9,650 $ 0.0368 356 $ 12 9,650 $ 0.0273 263 $ 13 9,650 $ 0.0202 195 $ 14 9,650 $ 0.0150 144 $ 15 9,650 $ 0.0111 107 $ 16 9,650 0.0082 79 17 9,650 $ 0.0061 59 $ 18 9,650 $ 0.0045 44 $ 75 Calculation of IRR 76 Let's assume discount rate @ 35% and caculate NPV 77 Year 0 1 2 3 4 5 78 Net Cashflow $ -28,000 $ 7,150 $ 8,400 $ 9,650 $ 9,650 $ 9,650 $ 79 Present Value Factor at 35% 1.0000 0.7407 0.5487 0.4064 0.3011 0.2230 80 Present Value $ -28,000 $ 5,296 $ 4,609 $ 3,922 $ 2,905 $ 2,152 $ 81 Net Present Value IRR = Lower Rate + (Lower Rate NPV*Difference in rates)/(Lower Rate NPV-Higher Rate NPV) 83 33.5921 84 IRR (%) 34 85 Note : Present Value Factor has been calculated using 86 =(1/(r)^n) where r = discount rate and n = period 19 9,650 $ 0.0033 32 $ $ 20 9,650 0.0025 24 3,034 $ 87 88 Equivalent Annual Cost = (Asset Price x Discount Rate)/(1-1/(1+Discount Rate)^n) 89 where n = no. of periods 90 = $3,288.87 91 Equivalent Annual Cost = $3,288.87 92 93

C D E 61 Cost of Furnace 1-28000 =B63+1 -1600 =2500*3.5 =SUM(C64:C65) =(1/(1+0.1))^C63 =C66*C67 =C63+1 -1600 =2500*4 =SUM(D64:D65) =(1/(1+0.1)) ^D63 =D66*D67 =D63+1 -1600 =2500*4.5 =SUM(E64:E65) =(1/(1+0.1))^E63 =E66 E67 =E63+1 - 1600 =2500*4.5 =SUM(F64:F65) =(1/(1+0.1)) ^F63 =F66*F67 =F63+1 -1600 =2500*4.5 =SUM(G64:665) =(1/(1+0.1))^663 =G66*667 G6341 -1600 =2500*4.5 =SUM(H64:H65) =(1/(1+0.1))^H63 =H66*H67 =H63+1 -1600 =2500*4.5 =SUM(164:165) = (1/(1+0.1))^163 =166*167 =B70+C68 =B71+C70 =C70+D68 =C71+D70 =D70+E68 =D71+E70 =E70+F68 =E71+F70 =F70+G68 =F71+G70 =G70+H68 =G71+H70 =H70+168 =H71+170 63 Year 64 Maintenance Cost 65 Savings in Fuel Cost 66 Net Cashflow =B61 67 Present Value Factor at 10% =(1/(1+0.1))^363 68 Present Value =B66*867 69 Net Present Value 70 Cumulative Cashflow =B68 71 Remaining Amount --B66 72 73 Payback Period = Completed Years + Remaining Amount/Available Amount 74 75 Calculation of IRR 76 Let's assume discount rate @ 35% and caculate NPV 77 Year 78 Net Cashflow =B66 79 Present Value Factor at 35% =(1/(1+0.35))^B77 80 Present Value =B78*B79 81 Net Present Value IRR = Lower Rate + (Lower Rate NPV*Difference in rates)/(Lower Rate NPV-Higher Rate NPV) = =10+(V69+(35-107(V69-(V81))) 84 IRR (%) =B83 85 Note : Present Value Factor has been calculated using =(1/(r)^n) where r = discount rate and n = period 87 88 Equivalent Annual Cost = (Asset Price x Discount Rate)/(1-1/(1+Discount Rate)^n) where n=no. of periods 90 = (28000*0.1)/(1-1/(1+0.1) 20) 91 Equivalent Annual Cost = =B90 =B77+1 =C66 =(1/(1+0.35))^C77 =C78*079 =C77+1 =D66 =(1/(1+0.35))^D77 =D78*D79 =D77+1 =E66 =(1/(1+0.35)) ^E77 =E78*E79 =E77+1 =F66 =(1/(1+0.35))^F77 =F78*F79 =F77+1 =G66 =(1/(1+0.35))^G77 =G78*G79 =G77+1 =H66 =(1/(1+0.35))^H77 =H78*179 =H77+1 =166 =(1/(1+0.35))^177 =178*179 83 86 89 93 94

E101 : X for M NO 62 63 =F63+1 64 -1600 65 =2500*4.5 66 =SUM(G64:G65) 67 =(1/(1+0.1))^G63 68 =G66*667 69 70 =F70+G68 71 =F71+G70 G6341 - 1600 =2500*4.5 =SUM(H64:H65) =(1/(1+0.1))^H63 =H66*H67 =H63+1 - 1600 =2500*4.5 =SUM(164:165) =(1/(1+0.1))^163 =166*167 =163+1 =J63+1 -1600 - 1600 =2500*4.5 =2500*4.5 =SUM(J64:J65) =SUM(K64:K65) =(1/(1+0.1))^J63 =(1/(1+0.1)) AK63 =J66*J67 =K66*K67 =K63+1 -1600 =2500*4.5 =SUM(L64:L65) =(1/(1+0.1))^L63 =L66*L67 =L63+1 -1600 =2500*4.5 =SUM(M64:M65) =(1/(1+0.1))^M63 =M66*M67 =M63+1 - 1600 =2500*4.5 =SUM(N64:N65) =(1/(1+0.1))^N63 =N66*N67 =N63+1 -1600 = 2500*4.5 =SUM(064:065) =(1/(1+0.1))^063 =066*067 =063+1 =P63+1 - 1600 -1600 =2500*4.5 =2500*4.5 =SUM(P64:P65) SUM(Q64:Q65) =(1/(1+0.1))^P63 =(1/(1+0.1)) ^Q63 =P66*P67 =Q66*Q67 =Q63+1 -1600 =2500*4.5 SUM(R64:R65) =(1/(1+0.1)) ^R63 =R66*R67 =R63+1 - 1600 =2500*4.5 =SUM(S64:565) =(1/(1+0.1))^563 =S66*567 =S63+1 -1600 =2500*4.5 =SUM(T64:T65) =(1/(1+0.1)) ^T63 =T66*T67 =T63+1 =U63+1 - 1600 - 1600 =2500*4.5 = 2500*4.5 ESUM(U64:U65) =SUM(V64:V65) =(1/(1+0.1))^U63 =(1/(1+0.1))^V63 =U66*U67 =V66*V67 =SUM(B68:V68) =T70+U68 =U70+V68 =T71+U70 =U71+V70 =G70+H68 =G71+H70 =H70+168 =H71+170 =170+J68 =171+J70 =J70+K68 =J71+K70 =K70+L68 =K71+L70 =L70+M68 =L71+M70 =M70+N68 =M71+N70 =N70+068 =N71+070 =070+P68 =071+P70 =P70+Q68 =P71+Q70 =Q70+R68 =Q71+R70 =R70+S68 =R71+S70 =S70+T68 =S71+T70 =5+-G71/H68 Years 77 =F77+1 =G77+1 =H77+1 =177+1 =J77+1 =K77+1 =L77+1 =M77+1 =N77+1 =077+1 =P77+1 =Q77+1 =R77+1 =S77+1 =177+1 =U77+1 78 =G66 =H66 =166 =J66 =K66 =L66 =M66 =N66 =066 =P66 =Q66 =R66 =S66 =T66 =U66 =V66 79 =(1/(1+0.35))^G77 =(1/(1+0.35)) ^H77 =(1/(1+0.35))^177 =(1/(1+0.35))^277 = (1/(1+0.35)) ^K77 = (1/(1+0.35)) ^L77 =(1/(1+0.35))^M77 =(1/(1+0.35))^N77 =(1/(1+0.35))^077 = 1/(1+0.35))^P77 = (1/(1+0.35))^077 =(1/(1+0.35)) ^R77 =(1/(1+0.35))^S77 =(1/(1+0.35))^777 =(1/(1+0.35))^U77 =(1/(1+0.35))^V77 80 =G78*G79 =H78*H79 =178*179 =J78*179 =K78*K79 =L78*L79 =M78*M79 =N78*N79 =078*079 =P78*P79 =Q78*079 =R78*R79 =S78*879 =T78*179 =U78*U79 =V78*V79 =SUM(B80:V80)

M73 X for - А V т к р м м о р о в s т у 60 61 Cost of Furnace 62 63 Year 64 Maintenance Cost 65 Savings in Fuel Cost 66 Net Cashflow 67 Present Value Factor at 10% 68 Present Value 69 Net Present Value 70 Cumulative Cashflow 71 Remaining Amount =H63+1 -1600 =2500*4.5 =SUM(164:165) =(1/(1+0.1))^163 =166*167 =163+1 -1600 =2500*4.5 =SUM(J64:J65) =(1/(1+0.1))^J63 =J66*J67 =J63+1 - 1600 =2500*4.5 SUM(K64:K65) =(1/(1+0.1))^K63 =K66*K67 =K63+1 -1600 =2500*4.5 =SUM(L64:L65) =(1/(1+0.1))^L63 =L66*L67 =L63+1 -1600 =2500*4.5 =SUM(M64:M65) =(1/(1+0.1))^M63 =M66*M67 =M63+1 -1600 =2500*4.5 =SUM(N64:N65) =(1/(1+0.1))^N63 =N66*N67 =N63+1 - 1600 =2500*4.5 =SUM(064:065) =(1/(1+0.1))^063 =066*067 =063+1 - 1600 =2500*4.5 SUM(P64:P65) =(1/(1+0.1)) ^P63 =P66*P67 =P63+1 -1600 =2500*4.5 ESUM(Q64:Q65) =(1/(1+0.1)) ^Q63 =Q66*Q67 =Q63+1 - 1600 =2500*4.5 =SUM(R64:R65) =(1/(1+0.1)) AR63 =R66*R67 =R63+1 - 1600 =2500*4.5 =SUM(S64:S65) =(1/(1+0.1))^S63 =S66*567 =S63+1 -1600 =2500*4.5 =SUM(T64:T65) =(1/(1+0.1))^T63 =T66*T67 =T63+1 - 1600 =2500*4.5 SUM(U64:U65) =(1/(1+0.1))^U63 =U66*U67 =U63+1 - 1600 =2500*4.5 SUM(V64:V65) =(1/(1+0.1)) ^V63 =V66*V67 =SUM(B68:V68) =U70+V68 =U71+V70 =H70+168 =H71+170 =170+J68 =171+J70 =J70+K68 =J71+K70 =K70+L68 =K71+L70 =L70+M68 =L71+M70 =M70+N68 =M71+N70 =N70+068 =N71+070 =070+P68 =071+P70 =P70+Q68 =P71+Q70 =Q70+R68 =Q71+R70 =R70+S68 =R71+S 70 =S70+T68 =S71+T70 =T70+U68 =T71+U70 72 Payback Period = Completed Years + Remaining 73 Amount/Available Amount 74 75 Calculation of IRR =5+-G71/H68 Years Let's assume discount rate @ 35% and calculate NPV 76 77 Year =H77+1 =177+1 =J77+1 =K77+1 =L77+1 =M77+1 =N77+1 =077+1 =P77+1 =Q77+1 =R77+1 =S77+1 =177+1 =U77+1 78 Net Cashflow =166 =J66 =K66 =L66 =M66 =N66 =066 =P66 =Q66 =R66 =S66 =T66 =U66 =V66 79 Present Value Factor at 35% =(1/(1+0.35))^177 =(1/(1+0.35))^177 =(1/(1+0.35))K77 =(1/(1+0.35))^L77 =(1/(1+0.35))^M77 = (1/(1+0.35)) ^N77 =(1/(1+0.35))^077 =(1/(1+0.35))^P77 =(1/(1+0.35))^077 =(1/(1+0.35))^R77 =(1/(1+0.35))^577 = (1/(1+0.35))^T77 =(1/(1+0.35))^U77 =(1/(1+0.35))^V77 80 Present Value =178*179 =J78*179 =K78*K79 =L78*L79 =M78*M79 =N78*N79 =078*079 =P78*P79 =Q78*079 =R78*R79 =S78*579 =T78*179 =U78*U79 =V78*179 81 Net Present Value =SUM(B80:V80) IRR = Lower Rate + (Lower Rate NPV*Difference in rates)/(Lower Rate NPV-Higher 82 Rate NPV) 83 84 IRR (%) 85 Note : Present Value Factor has be 87 88 Equivalent Annual Cost = (Asset 89 90 91 Equivalent Annual Cost = 02
For any query or clarification, please leave a comment.