Net Present Value Calculations

Calculating the Net Present Value (NPV) of a stream of cash flows is a popular method in capital budgeting.  The NPV is a calculation which is commonly used to determine a projects worth.  NPV compares the present value of money today, to the present value of money in the future, taking initial investment (negative amount) inflation and returns into account.  This has become the standard for investment decisions in recent times with the formula for when cash flows are even being;

 

NPV=- PV(1 + r)-n  + FV(1 + r)-n  

PV=Present Value, $1000
hr=Rate, 6%
n=Periods, 1 Year
FV=Future Value, $105

Example 1

Let's say we invest $1000 over 1 year and at the end of the year we will be given $1,050 this represents a 5% return on our initial investment. 50 / 1000=5%. The question we will want to have answered is if we put the money in a bank and the bank is going to pay 6% then what is the NPV. I can plug this into our above formula;


NPV=-100*(1.06)^0+ 105*(1.06)^-1

NPV=-.094

Now the rule of thumb with NPV calculations is if the result is above zero then the project is better than the alternative and the project should go ahead. If the result is less than zero then you should not go ahead.


Take for example the following project proposal. We have an initial investment of $100K a hurdle rate of 10% and a stream of cash flows over 5 years. The NPV calculation data for an example project.

Need Help with Excel Reports ... Learn More

Example 2

Rate : 10%

 Investment

       Year 1

     Year 2

 Year 3

      Year 4     

    Year 5

 ($100,000)

       ($9,500)

     $40,000     

 $44,000

      $34,000

    $34,000

NPV= − Initial Investment + C1 X (1 + r)-1 + C2 X (1 + r)-2 + C3 X (1 + r)-3 + C4 X (1 + r)-4 + C45 X (1 + r)-5

Where;


r is the target rate of return per period;
C1 is the net cash inflow during the first period;
C2 is the net cash inflow during the second period; and so on to 5 in the example above.

In Excel the problem can be calculated with the NPV formula which reduces the complexity to the following.

=NPV(Rate,Stream of Investments)

The Stream of investments would be all of the cells containing your expected return for the 5 years so the following:

=NPV(10%,-100,000,-9,500,40,000,44,000,34,000,34,000)

or 

=NPV(A2,C2:C7)

Where A2 is the Rate and C2:C7 contain the initial investment (100K) and the stream of cash flows over 5 years. The example file shows the problem in Excel.