ICAEW.com works better with JavaScript enabled.
Exclusive

Excel Tip of the Week

Excel Tip of the Week #376 - Discounting functions redux

Author: David Lyford-Smith

Published: 12 Jan 2021

Exclusive content
Access to our exclusive resources is for specific groups of students, users, subscribers and members.

Hello all and welcome back to the Excel Tip of the Week! This week we have a Creator post in which we are making a definitive re-examination of the four key functions in Excel for handling discounting calculations: NPV, XNPV, IRR, and XIRR. These were last covered back in TOTW #169.

Familiarity with the basic idea of discounted cash flows is presumed for this blog.

Net present values

Discount rates aren’t too complicated to calculate directly in Excel, but to save time, there are a couple of simple functions available to do the calculations in a single step. These let you take a series of cash flows, a discount rate, and simply output the final net present value figure.

Let’s start with the simplest function, NPV:

Image of net present value

=NPV(discount rate, values)

Discount rate is the applicable discount rate, expressed as a percentage
Values is a list of the cash flows to be discounted – these can be in a row or in a column

This function just applies the stated discount rate to each value – once to the first item, twice to the second, and so on – and then adds the total. In other words, the discount rate is presumed to be per each period, and each successive value is one period after the next. Note that all the values are discounted i.e. it is presumed that the first cash flow occurs at the end of the first period. If you have any undiscounted cash flows, i.e. cash flows at the beginning of the first period, then you need to add those to the result of the NPV function.

XNPV is much the same, except that it can handle uneven payment schedules. You need to provide dates for each cash flow:

=XNPV(discount rate, values, dates)

Dates is the new argument here – this must be a range of dates the same size as the values range

Note that XNPV will discount to the first date in the given date series – i.e. it will not discount the first cash amount at all. If you instead want to discount to today’s date or some other earlier date, add a column at the beginning with that date and £0 associated cash flow. 

Naturally, XNPV is more precise than just NPV. Using the same data as above, XNPV gives us £5,568 – £13 lower than the straight NPV calculation.

There is a demonstration of both methods, along with a direct calculation showing how each works, in this file

Internal rates of return

You can find internal rates of return – that is, the discount needed for a £0 NPV – for many discounting calculations using Goal Seek (see TOTW #250 for more on that). Goal Seek works through iterative trial-and-error to solve these kinds of problems, but you can also use the IRR and XIRR functions to do the same. Note however that these functions also calculate iteratively – that is, they’re not precisely mathematical, and may sometimes fail or give slightly inaccurate answers.

Once again, we’ll start at the simpler function, IRR:

Image of IRR function

The syntax is very simple:

=IRR(values)

Just like NPV, this presumes that discounting starts straight away, so that the first amount is discounted once, the second twice, and so on.

There’s actually an optional second input – guess. You can use this to “seed” the starting value for the iterative calculation, which can improve accuracy and stability. Sometimes there are multiple values that lead to a 0 NPV. Inputting a guess value can help if the function doesn’t behave as you expect. If you don’t include one, Excel uses 10% by default.

XIRR is the date-specific version:

=XIRR(values, dates)

The optional guess input is also available. XIRR calculates the same way XNPV does – i.e. assuming that the first amount is not discounted, and discounting all other cash flows with respect to that date.

Because IRR’s calculation method matches NPV, and XIRR’s matches XNPV, you can use the corresponding functions to check that the results are indeed £0:

Image of the corresponding functions

Because these aren’t precise calculations, if you expand out the decimals, eventually you’ll see a few nonzero amounts, but this is nothing to worry about.

It’s very important to remember the difference between these two functions and how they calculate – NPV and IRR start discounting immediately, whereas XNPV and XIRR discount later cash flows back to the first one listed. If you forget this you can easily end up a year out on your discounting!

Check out the accompanying example file and experiment with the functions for yourself.

You may also like

Archive and Knowledge Base

This archive of Excel Community content from the ION platform will allow you to read the content of the articles but the functionality on the pages is limited. The ION search box, tags and navigation buttons on the archived pages will not work. Pages will load more slowly than a live website. You may be able to follow links to other articles but if this does not work, please return to the archive search. You can also search our Knowledge Base for access to all articles, new and archived, organised by topic.

Open AddCPD icon

Add Verified CPD Activity

Introducing AddCPD, a new way to record your CPD activities!

Log in to start using the AddCPD tool. Available only to ICAEW members.

Add this page to your CPD activity

Step 1 of 3
Download recorded
Download not recorded

Please download the related document if you wish to add this activity to your record

What time are you claiming for this activity?
Mandatory fields

Add this page to your CPD activity

Step 2 of 3
Mandatory field

Add activity to my record

Step 3 of 3
Mandatory field

Activity added

An error has occurred
Please try again

If the problem persists please contact our helpline on +44 (0)1908 248 250