Locked lesson.
About this lesson
The Net Present Value is a Return on Investment analysis that determines a value in monetary terms for the accumulated cost and benefits of a project over a set time period.
Exercise files
Download this lesson’s related exercise files.
Net Present Value (NPV).xlsx18.9 KB Net Present Value (NPV) - Solution.xlsx
18.2 KB
Quick reference
Net Present Value
The Net Present Value is a Return on Investment analysis that determines a value in monetary terms for the accumulated cost and benefits of a project over a set time period.
When to Use Net Present Value
Like all project ROI techniques, this analysis is done as part of the preparation of the business case used to justify a project. Net Present Value (NPV) analysis is focused on value creation. This technique can be used with all types of projects from small to large and those focused both on sales benefits and productivity benefits. The only down side with this technique is that it is hard to calculate by hand, but spreadsheet software has the NPV formula and can do the calculations.
Instructions
This ROI technique requires the use of a spreadsheet. The columns for spreading the costs are always a year in duration. Also, most companies set these columns based upon their fiscal year. Therefore, unless the project starts on the first day of the fiscal year, the project costs in the first column will represent less than a year’s worth of project effort. The NPV is always done for a predetermined number of years and the project costs and the benefits must be spread appropriately throughout those years.
The NPV technique provides a long term impact for the project because it looks beyond the point of Breakeven or Payback. The number of years to use in the analysis is determine by your business and the project type. For instance, I often use three years for IT projects because the technology changes so fast. I used seven years for new product development projects for a company that made products for use in the construction industry, and I have used 15 to 20 years for large utility projects.
This leads to one of the characteristics of NPV. It does its calculation using a Discounted Dash Flow (DCF) analysis. Discounting is a technique for reducing the value of money in the future and transforming it back to the value of money today. By doing this effects like inflation and the cost of money can be accounted for in the analysis. This discounting is done in the NPV calculation by applying a discount rate, sometimes called a hurdle rate. This number is provided by Finance and it changes based upon market and business conditions. The DCF effect is small in the first year or two (there is no DCF effect for money spent in the current year) but it soon grow large. I recommend using a DCF analysis whenever you have project that will require more than two years to payback.
So let’s talk about the NPV formula. This formula will require three types of information. First is the number of years in the analysis. Second is the annual or period total from the spreadsheet for each of those years (do not use the cumulative). Finally, it will use the discount rate from Finance. Each year annual cash flow total is discounted and then all of the discounted cash flows are summed up to determine the total Net Present Value.
This analysis can be done using the ROI spreadsheet that we illustrated in the module on ROI. However, be careful when using the Excel NPV formula. Excel starts discounting the first annual cash flow value that is in the formula. If you are preparing an NPV analysis for a project that will start next year, that is appropriate. However, if you are preparing an NPV analysis for a project that will be starting in the current year, the annual cash flow for the current year should be excluded from the NPV calculation. Just start the NPV formula with the value from “Next” year and then add in the value for the current year to the NPV calculation. See the example below using the Excel formula.
Theoretically, any NPV that is greater than zero makes money for the business. However, my rule of thumb is that I like to see an NPV that is at least 50% of the value of the project so that if the project estimates or the benefit estimates are off – either amount or timing, the project will still have a positive impact.
Hints and Tips
- This technique provides a long range view of the project. The best project from an NPV perspective may not be the best one from a Payback Period or Breakeven Point perspective. Find out from your stakeholders which is more important to them, near term profit or long term profit.
- If the discounting will go on for a long period (more than 8 years) even a minor change in the discount rate can have a very large impact on NPV. Make sure that you are using the correct rate. Finance often will have different rates for different types of projects.
- Do not use the cumulative totals, use the annual totals. This is the most common mistake made when using this technique.
- Some business will want to spread the investment portion of the project costs based upon the annual depreciation values instead of the actual cash flow. This will inflate the NPV some, but it will provide a value that is more likely to align with the impact of the project on the Net Income. Follow whichever method your company directs you to use.
- 00:03 Hi, this is Ray Sheen.
- 00:05 Let's look at the ROI technique known as net present value.
- 00:08 This is the most popular technique for calculating ROI on large projects.
- 00:15 Net present value is focused on the growth and business value over some time period.
- 00:20 The net present value, or NPV,
- 00:21 is determined by summing the annual period totals of project cash flows,
- 00:26 both the cost and the benefits, over several years, and then discounting them.
- 00:30 It's similar to the cumulative cash flow calculation that we
- 00:33 have on the spreadsheet.
- 00:34 But by adding the discounting affect,
- 00:36 we have a better sense of the true value created.
- 00:39 If the NPV is negative, don't do the project,
- 00:41 because it drains value from the business.
- 00:43 If the NPV is positive, do the project, because it adds value.
- 00:47 If comparing between projects, do the project with the highest NPV,
- 00:50 it will add the most value.
- 00:52 Unlike payback period and break even point, NPV looks to long term benefits.
- 00:57 It includes what happens after the point where cost and benefits are equal,
- 01:01 to determine what is happening to the business value over the long term.
- 01:05 I mentioned in a previous module that different ROI techniques could prioritize
- 01:09 projects differently.
- 01:10 A project may have a very fast payback period, but
- 01:13 provide very little long term benefit.
- 01:15 Another project may have a slower payback period, but
- 01:18 the long term benefit could keep growing exponentially.
- 01:22 NPV will highlight this long-term business value growth.
- 01:25 It is not about time, it's about money.
- 01:29 I mentioned that NPV discounts the annual period totals.
- 01:32 Let's talk about that for a minute.
- 01:34 Discounted cash flow considers the opportunity costs of the money invested in
- 01:37 the project.
- 01:38 The opportunity cost is the income or savings that the money you invested in
- 01:42 the project could have earned, and the best alternative that could be considered.
- 01:46 Discounted cash flows will convert the money in future years
- 01:50 into what the money is worth today.
- 01:52 It recognizes that there may be inflation or deflation effects.
- 01:55 It considers how the money could have been invested in other financial instruments,
- 01:59 and what interest or dividend rate would be available for that investment.
- 02:02 And it considers the interest rate that is being charged to borrow money.
- 02:06 All of these are combined to create a discount rate,
- 02:09 sometimes called a hurdle rate.
- 02:10 The discount rate for every company is different based upon the company's
- 02:14 financial situation and the industry conditions.
- 02:16 Your finance department will set a rate each year and sometimes twice a year.
- 02:21 And that is what you should use for discounting.
- 02:23 Discounted cash flow is used for projects with long term cost and benefits.
- 02:27 My rule of thumb is, that if I'm using more than two years of my
- 02:30 ROI calculations, I use an ROI technique that uses discounted cash flow.
- 02:35 Less than two years, and the discounting effect is so
- 02:38 small, it usually does not significantly impact the ROI results.
- 02:42 The two ROI techniques that use discounted cash flow are net present value and
- 02:47 the internal rate of return.
- 02:49 So let's look at the NPV calculation.
- 02:52 It starts with the ROI spreadsheet in the annual totals.
- 02:55 Be sure that each column represents a year.
- 02:57 Many companies will actually list the calendar or
- 02:59 fiscal year at the top of the column.
- 03:02 When this is the case, the first column will not have a full year's worth of
- 03:05 project costs, unless the project starts on the first day of the year.
- 03:09 That's okay for this analysis.
- 03:11 The NPV calculation is not jeopardized by a partial year in the first column.
- 03:15 The spreadsheet should sum every column for you.
- 03:18 The NPV will use this period total, not the cumulative total.
- 03:22 Using the cumulative is a common mistake that will likely inflate your NPV
- 03:26 to unrealistic levels.
- 03:28 This is the formula.
- 03:29 NPV is the sum of each of the year's period totals
- 03:32 after they have been discounted using the discount rate.
- 03:35 Note that the NPV calculation is for some set period of years.
- 03:39 Again, finance will normally dictate how many years to use
- 03:42 based upon the project time.
- 03:44 However many years you are directed to use,
- 03:46 that is the number of columns you will need in the spreadsheet.
- 03:50 NPV calculations are not normally done by hand.
- 03:52 The spreadsheet will do them for you.
- 03:55 One caution when using the NPV function in Excel,
- 03:58 it starts discounting with the first column that is in the function.
- 04:02 Now, if you are doing an NPV calculation for a business case or
- 04:05 a project that will start next year, that's okay.
- 04:07 But if you're doing an NPV calculation for a project that's about to start in
- 04:11 this year, the first year's money should not be discounted.
- 04:15 Discounting always starts with next year's money.
- 04:18 So in that case, remove the current column from the Excel NPV formula, and
- 04:22 add that to year's total to the discounted total from the NPV function.
- 04:26 The Excel equation for this case is shown.
- 04:30 Let's look at an example.
- 04:31 Here are the spreadsheet totals for a ten month IT project that costs $450,000 and
- 04:36 introduces a new sales order management system.
- 04:39 During year one, the operations suffer from loss of productivity
- 04:43 as they come up to speed on the new system.
- 04:45 After that, they get continued productivity improvement each year, and
- 04:48 with the new system, they're even able to reach some new customers.
- 04:51 You can see the period totals for each year.
- 04:54 If we just added the values for each year out through year six,
- 04:57 we would have a cumulative value of $1,650,000.
- 05:01 But because this is a project with a long-term impact,
- 05:04 we use discounted cash flow.
- 05:06 For this example, finance gave us a discount rate of 15%.
- 05:09 We plug the numbers in to the equation, and get a net present value of $893,674,
- 05:15 just barely over half of what the cumulative value would have been, but
- 05:20 a better indication of the value of the project, which is still very good.
- 05:24 For project investment of 450,000, the business not only earns back
- 05:28 the investment, it makes another $893,000 within six years.
- 05:32 Net present value is an excellent technique for
- 05:36 determining the long-term value of a project.
- 05:39 If you've completed the ROI spreadsheet, it's an easy calculation.
Lesson notes are only available for subscribers.
PMI, PMP, CAPM and PMBOK are registered marks of the Project Management Institute, Inc.