Formulas discussed: NPV, XNPV
The workbook used throughout this article is available here.
In a previous post we looked at how Excel calculates NPVs and IRRs and some of their weaknesses. In this post we’ll show you how you can manually calculate an NPV so that you have greater flexibility and control, and know exactly what kind of answer you’re getting.
The first part of the workbook is a refresher on how NPV and XNPV produce different results, based on their different assumptions of the start date. XNPV is useful if you are not dealing with constant, annual periods, and means you don’t have to separately calculate a discount rate for the particular period length if you start with an annual rate.
You’ll notice that XNPV produces a slightly different result to the end of period NPV calculations. This is because NPV assumes constant period length, whereas XNPV uses actual dates. The final period is actually 366 days long, so if you change the date to 30/12/2016, the two methods will have the same result.
Time series NPV
Why might you want to display an NPV at each point in time? Mainly because it can show you how the value of your business can vary depending on income, expense and capital requirements, and help you optimise the timing any capital raisings or choose when to sell the business.
If you wanted to display an NPV at each point in time, then XNPV and NPV can be suitable, but can slow your model down, as it will be repeatedly making the same calculation. For example, using the annual table in section 1a of the workbook, the final cashflow of 11,000 would be discounted back in every single NPV calculation. While in this example that is only for four periods, in a large model it can chew up a lot of processing power. Using a manual calculation can:
1. Speed up calculation through removing repetition
2. Allow you to use different discount rates in different periods
3. Fully control whether cashflows and valuations are at the start, end or during a period.
These can be done using the NPV (or XNPV) functions if done carefully (see the discussion on variable discount rates below), but I find a manual calculation to be more straightforward.
Section 2a of the workbook contains manual calculations of the NPV on an annual basis. You’ll notice that the results in the first period are the same as the results using the NPV formulas in the earlier example.
These formulas take the NPV from the next period, add the cashflow from their period, and discount them back accordingly. And just by choosing where to place brackets in the formula, you have controlled whether the value is at the start or end of the period.
This also works for quarterly, or any other model period you want to use.
Here, because of the way the period discount rate has been calculated, the result is the same as the quarterly XNPV. If you wanted to produce the NPV result then you just need to change the period discount rate formula so that it uses a constant 0.25 as the power, rather than the actual number of days in the period.
It’s also important to notice another difference between the two manual calculation methods. When calculating a value at the end of the period, you need to use the discount rate in the next period, not the current one. For example, for the NPV at 31/12/13 you want to discount back the value at 31/3/14 by the 90 days between those dates, not the 92 days between 30/9/13 and 31/12/13.
Variable discount rates
Using the same logic in the manual calculation method, you can now easily use variable discount rates. Why would you want to do this? It could be because there are different risk profiles for different cashflows (although this would probably be better done by calculating their NPVs separately with constant discount rates and then adding the results together), or more likely because interest rates change over time, whether because you have a variable rate, you have a fixed rate that expires at some point or you need to refinance your debt in the future and want to see how this varies your NPV. If you are using the manual calculation method, then your formulas do not need to change from how they were done for the quarterly calculations in section 2b of the workbook.
If you want to use the inbuilt NPV (or XNPV) formula with varying discount rates, then you need to modify your standard approach. You do this by applying the same logic in the NPV formula to how the manual method works. Essentially you take the NPV that is calculated in the next period, add it to the current period cashflow, and discount back by one period. You are essentially repeatedly calculating a single period NPV.
What can go wrong
Avoid these simple mistakes and you’ll be well along the path to valuing like a pro.
1. Use the compounding formula to calculate a period interest rate, don’t just divide it by the number of periods.
If you’re dealing with a quarterly model, the formula should not be
It should be
2. Make sure you use the correct period’s discount rate. For value at the start of a period, use the same period’s rate, for value at the end, use the next period’s rate.
3. If you want to use the built in NPV formula over multiple periods, then use single period NPV calculations rather than a forward looking construction so as to speed up model calculation. For example, in cell I87, note that my formula is
Not that this method would work given the variable discount rates anyway. This also means that you are less likely to make errors if you extend the end date of your model (a general benefit of the manual method).
Have any questions? Post them in the comments or email me at email@example.com. We’ll also take suggestions for any future topics you’d like this blog to cover.