What is an NPV?
Simply put an NPV or net present value is a way of determining an investment or projects value. It is probably the most often used formula for financial decision making. The below tutorial takes you through:
Before we delve into the depths of NPV, we should first understand what a present value or PV is. Imagine that you were to receive $100 from the bank in five years time (unlikely I know). You know that the interest rate from the bank during that period of time is going to be constant at 5%. What is that $100 value today?
Think of investing an unknown amount y, for 5 years at 5%. We know that the first year we would get y x 5% in interest plus still have our principal y. i.e. we would have y x (1+5%). If we re-invest our interest and principal of y x (1+5%) for another year we would get y x (1+5%) x (1+5%) or y x (1+5%)2.
As you can imagine the pattern repeats itself until we get to year 5, where we have y x (1+5%)5. Hence if we want $100 in five years time by investing y then the following relationship would have to hold.
Type the equation in the calculator and you get y = $78.3
NPV is simply an extension of the PV, taking into account your initial investment. Say for example we had an initial outlay of $50 and in return we got $100 in five years time. The NPV would be the PV of future cash flows (i.e. the PV of $100 or $78.3) less the initial investment at time zero ($50), or $28.3.
So what does that value mean?
The decision to accept or reject a project
Basically put if a project has a positive NPV then we should accept the project. The below outlines the accept reject decision.
NOTE: Some of you sharp tacks may be thinking. What happens if you have multiple projects with positive NPVs but only have a restricted budget? Well that is a topic for another week and relates to capital budgeting restrictions.
The Excel XNPV function
As pointed out by Chandoo.org and Navigator Project Finance there are some limitations to the NPV function in Excel. Some of the main limitations include:
XNPV (rate, values, dates)
Let’s look at some examples for the Excel XNPV function.
Putting it all together – Some Examples
You can follow the examples by downloading the Excel spreadsheet and watching the following YouTube video.
Example 1
Let’s firstly look at the example we introduced in the Present Value Introduction. Imagine that the current date is 31 December 2011 and we want to find the present value of $100 given to us in 5 years time if we use a discount rate of 5%.
See Figure 1 for the XNPV calculation.
Figure 1: XNPV formula for Example 1
If you've done this correctly you should get a value of $78.3.
Example 2
You are thinking of investing in a machine. On 31 December 2011 the machine costs $150, and you require a return of 10%. If you have the following cash flows, would you invest in the machine?
Figure 2: XNPV formula for Example 2
Using Excel we come to the following conclusion, the net present value of the cash flows is -$12. If we use the decision rules we established in the last section we would reject the project as it has a negative NPV.
If you’re still struggling with the NPV concept check out some of these great links.
Simply put an NPV or net present value is a way of determining an investment or projects value. It is probably the most often used formula for financial decision making. The below tutorial takes you through:
- An introduction to present value
- The decision to accept or reject a project based on NPV
- The Excel XNPV function
- Putting it all together – some examples
Before we delve into the depths of NPV, we should first understand what a present value or PV is. Imagine that you were to receive $100 from the bank in five years time (unlikely I know). You know that the interest rate from the bank during that period of time is going to be constant at 5%. What is that $100 value today?
Think of investing an unknown amount y, for 5 years at 5%. We know that the first year we would get y x 5% in interest plus still have our principal y. i.e. we would have y x (1+5%). If we re-invest our interest and principal of y x (1+5%) for another year we would get y x (1+5%) x (1+5%) or y x (1+5%)2.
As you can imagine the pattern repeats itself until we get to year 5, where we have y x (1+5%)5. Hence if we want $100 in five years time by investing y then the following relationship would have to hold.
100 = y x (1+5%)5
Rearranging this formula would bring about y = 100/(1+5%)5. Wow, look what just happened, we derived the PV formula.Type the equation in the calculator and you get y = $78.3
NPV is simply an extension of the PV, taking into account your initial investment. Say for example we had an initial outlay of $50 and in return we got $100 in five years time. The NPV would be the PV of future cash flows (i.e. the PV of $100 or $78.3) less the initial investment at time zero ($50), or $28.3.
So what does that value mean?
The decision to accept or reject a project
Basically put if a project has a positive NPV then we should accept the project. The below outlines the accept reject decision.
- NPV >0 – accept the project
- NPV = 0 – indifferent between accepting and rejecting the project
- NPV <0 – reject the project
NOTE: Some of you sharp tacks may be thinking. What happens if you have multiple projects with positive NPVs but only have a restricted budget? Well that is a topic for another week and relates to capital budgeting restrictions.
The Excel XNPV function
As pointed out by Chandoo.org and Navigator Project Finance there are some limitations to the NPV function in Excel. Some of the main limitations include:
1) The cash flows are at the end of the year (even your initial investment which is meant to occur at t=0); and
2) cash flows must be equally spaced apart.
As a result of these limitations Video Financial Modelling prefers to use Excel’s XNPV function. Don’t be too worried, the Excel XNPV function is similar NPV function, but with one extra input, dates. The XNPV function looks like:XNPV (rate, values, dates)
Let’s look at some examples for the Excel XNPV function.
Putting it all together – Some Examples
You can follow the examples by downloading the Excel spreadsheet and watching the following YouTube video.
Example 1
Let’s firstly look at the example we introduced in the Present Value Introduction. Imagine that the current date is 31 December 2011 and we want to find the present value of $100 given to us in 5 years time if we use a discount rate of 5%.
See Figure 1 for the XNPV calculation.
If you've done this correctly you should get a value of $78.3.
Example 2
You are thinking of investing in a machine. On 31 December 2011 the machine costs $150, and you require a return of 10%. If you have the following cash flows, would you invest in the machine?
- 31 December 2012: $50
- 31 December 2013: $40
- 31 December 2014: $70
- 31 December 2015: $10
Figure 2 shows the calculation for the above example.
Figure 2: XNPV formula for Example 2
Using Excel we come to the following conclusion, the net present value of the cash flows is -$12. If we use the decision rules we established in the last section we would reject the project as it has a negative NPV.
If you’re still struggling with the NPV concept check out some of these great links.
- Wikipedia
- Chandoo.org
- Navigator Project Finance
- (Note: don’t get bogged down with the nominal to real rate calculation in this video).
No comments:
Post a Comment