Excel NPV functions

Free Preview

Excel has 2 ways to calculate the NPV and there is a third we will show below. Note the pros and cons of these methods

NPV Function in Excel

The actual NPV function is fairly simple.

To follow along go to 17.11

As shown below you need to point at the discount rate and then tell it where the values are. BUT, note that we only highlighted the values from period 1 onwards. We excluded the period 0 payment.

NPV

As shown above in the description of what Value 1 is (we have underlined the important parts)

  • the cash flows HAVE to be equally spaced in time ( so they can be either monthly, annual etc BUT it can’t change), AND
  • they must occur at the END of each period (so we have to leave out Period 0 as it occurs at the beginning of Period 1).

This is extremely important for 2 reasons:

  • If you include Period 0 it will give you the wrong answer
  • The number you get from the ‘NPV’ is not actually a net present value. It is the present value of all the future cash flows but it ignores the initial cash flow.

So to get the NPV you need to take this number and add/ subtract the initial cash flow. So the actual NPV is in cell B47.

The XNPV function in Excel

A better function (in our opinion) is the XNPV function as it eliminates the problems above.

To follow along go to 17.11

So as shown below you need to point to the discount rate, but also to the cash flows AND the related dates. Note that this time you INCLUDE the initial cash flow. This is because you have provided the date and Excel will discount back to the earliest date it finds.

NPV

This is important as it means:

  • The cash flows can be beginning or end as long as you provide the correct date
  • The timing of the cash flows can be irregular (so you can have some monthly cash flows, some quarterly etc)
  • You don’t need to add/ subtract the initial cash flow as it is already included

You will note that using NPV the net present value was 9 805 and XNPV gives 9 819 (look at the answer in the function wizard). XNPV is taking into account leap years and actual days in the year whereas NPV is just assuming that they are exactly the same time periods.

Manually discounting each cash flow

A third way is to manually calculate it. If you think what NPV is doing, it is taking each year’s cash flow and bringing it back to a common period. So we can create a factor that tells us what to multiply the cash flow by. This is easy enough using the PV function. Excel needs to calculate what $1 in say 2 years time is worth today.

To follow along go to 17.11

As shown below, in the PV function we have put a 1 in the FV option and then linked the relevant criteria to the discount rate and the header showing what year it is. This generates a value (0.591… in the case below) which tells us that $1 in 2 years time is the same as 0.59 today. Now we can multiply the cash flow in year 2 (10 913)  by the factor (0.591…) to get the worth of the cash flow (6 458). If you add up all the cash flows (including cell D41) it will give you 9 805 which is the same number as calculated with the NPV function.

NPV

The benefit of doing it manually is that it allows you to see the impact of each year on the NPV. At a discount rate of 30% year 5’s cash flow of 18 677 is only contributing 5 030 to the NPV whereas year 2’s cash flow of 10 913 contributes a similar amount. It is useful to understand where in a project the real value is generated and what part of the cash flows should you really make sure are as correct as possible.

Mid Year Discounting

This method also allows you to change the time periods to allow for mid year discounting.

Mid year discounting assumes that you control these cash flows and so in reality you won’t wait till the end of the year to get your hands on the cash flow. You will probably take it out throughout the year.

To imitate this behavior we can assume that on average we take the cash out in the middle of the year.

If we have manually calculated the discount factor, all we need to do is change the time periods. As shown below in row 33, the time periods are now 0, 0.5, 1.5 etc. The PV calculation will use these new time periods (1.5 as shown in the red circle) and create a new discount factor (which will be bigger as the cash is flowing earlier which is better for net present values).

NPV

Note the difference in value between this mid year discounting (13 983) and the normal end of year NPV (9 805). Just moving the cash flows 6 months earlier can make a massive difference to the end result!

The NPV function in Excel won’t allow for Mid Year Discounting but you can trick XNPV into doing it by creating another header with new dates that correspond to the mid year dates and not the end of year dates.




Back to: Financial Calculations and compound interest in Excel > Time Value of Money- Debt, NPV, IRR