Question

Question 1: Chapter 8 Investment Criteria. A new furnace for your small factory will cost $27,000 a year to install and will require ongoing maintenance expenditures of $1,50 more fuel-efficient than your old furnace and will reduce your consumption of heating oil by 2,400 gallons per year. Heating oil this year will cost $3 a gallon; the price per gallon is expected to increase by $.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 8%. What is the net present value of the investment in the furnace? Cost of Furnance 27.000 a. What is the Net Present Value MODA venoot b. What is the IRR? c. What is the Profitability Index? d. What is the payback period?

I need solution to be in excel format so I can see how it was done

Answer 1

A) NPV:
Installation cost = 27000
Maintenance=1500/year
Oil saving=2400 gallons/year
oil rate=3/g first year,3.5 2nd year and 4 (3-20) year
life=20 years
So NPV
=2400*3/(1.08)^1+2400*3.5/(1.08)^2+2400*4/(1.08)^3......2400*4/(1.08)^4 - 27000/(1.08)^1-1500/(1.08)^1...1500/(1.08)^20
=91003.19-14727.22-25000
=91003.19-39727.22
=51275.97

D) Payback period=individual annual cash inflow from the initial cash outflow, until the payback period has been achieved
ist year:7200-1500=5700
2nd year:8400-1500=6900 =12600
3rd year:9600-1500=8100 =20700
4th year:9600-1500=8100 =28800>27000
so its between 3rd and 4th year
=27000-20700/8100
=.77
So payback period is 3+0.82
=3.77 years

B) To calculate IRR NPV must be zero:
NPV=0=2400*3/(1+r)^1+2400*3.5/(1+r)^2+2400*4/(1+r)^3......2400*4/(1+r)^4 - 27000/(1+r)^1-1500/(1+r)^1...1500/(1+r)^20
So by hit and trial :
r=27.1%

C)Profitability Index=PV of future cashflows/Initial Investment
So as Calculated in part a)
PV of future cashflows=91003.19-14727.22
=76275.97
Initial investment=27000/1/08
=25000
so profitability index=76275.97/25000
=3.05 or approx. 3