Net Present Value Expert Needed

[webster]

Hi,

I need some help with calculating my 3 co projects' NPV. I did a research
on the net and found out how to calc using the formula and an excel
sheet, not sure if its right, can someone spare few mins to glance
through for me to see what i did was correct below?

Interest rate: 6%

Project One: Will cost $150,000 and will take six months to complete. Savings from this project are projected to be $55,000 per year for 6 years. Ongoing costs for this project will be $15,000 per year for 4 years.

T=0 -$150,000 / 1.06^0 = -$150,000
T=1 ($55,000 - $15,000)/ 1.06^1 = $ 37736
T=2 ($55,000 - $15,000)/ 1.06^2 = $ 35600
T=3 ($55,000 - $15,000)/ 1.06^3 = $ 33584
T=4 ($55,000 - $15,000)/ 1.06^4 = $ 31684
T=5 $55,000 / 1.06^5 = $ 41099
T=6 $55,000 / 1.06^6 = $ 38773

NPV = $68476

Project Two: Will cost $250,000 and take twelve months to complete. Revenues from this project will be $62,000 per year for 7 years.

T=0 -$250,000 / 1.06^0 = -$250,000
T=1 $62.000 / 1.06^1 = $58491
T=2 $62.000 / 1.06^2 = $55180
T=3 $62.000 / 1.06^3 = $52056
T=4 $62.000 / 1.06^4 = $49110
T=5 $62.000 / 1.06^5 = $46330
T=6 $62.000 / 1.06^6 = $43708
T=7 $62.000 / 1.06^7 = $41234

NPV = $96109

Project Three: Will cost $420,000 and take twenty four months to complete. Savings from this project will be $35,000 per year for 8 years. Revenues from this project will be $55,000 per year for six years. Ongoing costs for this project will be $25,000 for the first 3 years and then $15,000 for 3 years after that.

T=0 -$420,000 / 1.06^0 = -$420,000
T=1 $65.000 / 1.06^1 = $61321
T=2 $65.000 / 1.06^2 = $57850
T=3 $65.000 / 1.06^3 = $54575
T=4 $75.000 / 1.06^4 = $59407
T=5 $75.000 / 1.06^5 = $56044
T=6 $75.000 / 1.06^6 = $52872
T=7 $35.000 / 1.06^7 = $23277
T=8 $35.000 / 1.06^8 = $21959

NPV = -$32695

ty, much appreciated.

 

[cjd]

I haven't checked you maths but you've made a boo boo with the initial investment and timing of savings.

In 1, the assumption that you should use year 0 for the investment is probably ok as it only takes 6 months.

In 2, the investment takes 12 months so you should call it year 1 so that it is discounted. Savings then start in year 2.

In 3, the investment takes 2 years so it should be spread over 2 years and savings can't usually be made until the investment is complete, so they would only start in year 3.

 

[webster]

Do you have an example for 2.

Not sure what the T=0 and T=1 calc will be?

Thanks!

PS: if i know 2, i should be able to do 3.

 

[cjd]

It just all slips a year

T=0 (as the upfront investment takes a year you have nothing in year 0)
T=1 -$250,000 / 1.06^1 = (so the £250K is discounted in year 1)
T=2 $62.000 / 1.06^2 = (savings only start to arrive in year 2)
T=3 $62.000 / 1.06^3 =
etc


for 3, you'll need to assume £210,00 is spent in year 1 and £210,000 in year 2. Savings start year 3

 

[webster]

Something like this?

Project Two: Will cost $250,000 and take twelve months to complete. Revenues from this project will be $62,000 per year for 7 years.

T=1 -$250,000 / 1.06^1 = -$235849
T=2 $62,000 / 1.06^2 = $55180
T=3 $62,000 / 1.06^3 = $52056
T=4 $62,000 / 1.06^4 = $49110
T=5 $62,000 / 1.06^5 = $46330
T=6 $62,000 / 1.06^6 = $43708
T=7 $62,000 / 1.06^7 = $41234
T=8 $62,000 / 1.06^8 = $38900

NPV = $90669

Project Three: Will cost $420,000 and take twenty four months to complete. Savings from this project will be $35,000 per year for 8 years. Revenues from this project will be $55,000 per year for six years. Ongoing costs for this project will be $25,000 for the first 3 years and then $15,000 for 3 years after that.

T=1 -$210,000 / 1.06^1 = -$198113
T=2 -$210,000 / 1.06^2 = -$186899
T=3 $65,000 / 1.06^3 = $54575
T=4 $65,000 / 1.06^4 = $51486
T=5 $65,000 / 1.06^5 = $48572
T=6 $75,000 / 1.06^6 = $52872
T=7 $75,000 / 1.06^7 = $49879
T=8 $75,000 / 1.06^8 = $47056
T=9 $35,000 / 1.06^9 = $20717
T=10 $35,000 / 1.06^10 = $19544

NPV = -$40311

 

[cjd]

Looks about it.

You'll notice that in 2 the NPV has improved as you're discounting the upfront payment and this has a bigger effect than delaying savings

In 3 tho' delaying income and savings 2 years worsens the NPV even taking into account the discounted payment.

 

[webster]

thanks, its been very helpful
learnt something new ;-)