Mandelbrot set image very small MohammedAmin.com
Serious writing for
serious readers
Follow @Mohammed_Amin
Join my
email list

Search this site

Custom Search
Tap here for MENU

A hazard with Excel's Internal Rate of Return function

Finance students are taught about the conceptual differences between net present values and internal rates of return. Excel has functions for both. I have recently found Excel's IRR function gave wrong answers. The explanation is that it treats blank cells quite differently from the way I expected. Instead one needs to insert zero into cells that have no cash flow.

Summary

Posted 12 February 2017

Very early in one's training for any financial profession (for example training as a chartered accountant) one encounters the time value of money, discounting cash flows, and the concept of the internal rate of return.

Very briefly, if you specify:

  1. A series of future cash flows which are received at future points in time, whether you count them all in future days, future weeks, future months or future years, where "Ct" is the cash flow arising at time t. The cash flow Ct may be negative (by convention used to represent investments that you make which require spending cash such as buying a share or building a factory) or positive (when the investment pays you a return such as a dividend, income from the factory, or sale proceeds.)
  2. A rate "r" for discounting future cash flows. This discount rate can differ from period to period, but for convenience in practice most calculations are done with a discount rate which is the same for all periods.
  3. With "n" as the number of equally spaced time periods under consideration. (A cash flow does not need to arise at each time period.)

Then the present value of the series of future cash flows, PV, is computed as follows:

PV = C0/(1+r)0 + C1/(1+r)1 + C2/(1+r)2 +... (and so on) ... Cn/(1+r)n

or in more concise mathematical notation:

 

Numerical illustration of discounting

Consider the following cash flows:

Time Cash flow
0 -400
1 20
2 30
3 40
4 50
5 60
6 70
7 150
8 200
9 150
10 100

Here there is one single investment of 400 at time 0, followed by 10 positive cash receipts. If one discounts this series of cash flows at 5% per period, the arithmetic is as follows:

Time Cash flow PV of each cash flow
0 -400 -400.00
1 20 19.05
2 30 27.21
3 40 34.55
4 50 41.14
5 60 47.01
6 70 52.24
7 150 106.60
8 200 135.37
9 150 96.69
10 100 61.39
NPV = total 221.25

The table below shows each mathematical formula, copied from the Excel spreadsheet which is used for all of these workings.

Simple NPV calculation
0.05 Discount rate
Time Cash flow PV of each cash flow Calculated values
0 -400 =B6/(1+$A$3)^A6 -400
1 20 =B7/(1+$A$3)^A7 19.047619047619
2 30 =B8/(1+$A$3)^A8 27.2108843537415
3 40 =B9/(1+$A$3)^A9 34.553503941259
4 50 =B10/(1+$A$3)^A10 41.1351237395941
5 60 =B11/(1+$A$3)^A11 47.0115699881075
6 70 =B12/(1+$A$3)^A12 52.2350777645639
7 150 =B13/(1+$A$3)^A13 106.602199519518
8 200 =B14/(1+$A$3)^A14 135.367872405737
9 150 =B15/(1+$A$3)^A15 96.6913374326696
10 100 =B16/(1+$A$3)^A16 61.3913253540759
NPV = total =SUM(C6:C17) 221.246513546886

Here $A$3 references the Excel spreadsheet cell A3 which holds the discount rate "r"of 0.05 which is of course 5%.

A6, A7 etc are the successive values of time "t" in the formula given earlier on the page.

Internal rate of return

Discounting needs you to specify a rate (or rates if you have different discount rates for different periods) that you will use to discount the cash flows. In the above example, 5% was your choice. You could equally well have discounted at 4%, 9% or any other rate you chose.

Obviously the higher the discount rate, the lower the aggregate NPV since higher discount rates reduce the positive cash flows in periods 1 - 10 to smaller NPV numbers.

An obvious question to ask is, is there a discount rate which results in calculating an aggregate NPV of zero?

That depends on the cash flow pattern. If all of the cash flows were positive (i.e. there was no initial -400 at time 0, only the positive receipts from times 1-10), then with any finite discount rate, no matter how large, the aggregate NPV is always greater than zero. Only an infinite discount rate will produce a zero NPV, if all cash flows are positive, and even then only if there is no positive cash flow at time 0.

With the cash flows in the example, one can calculate the discount rate that produces an aggregate NPV of zero. In the Excel spreadsheet you can repeatedly change cell A3 until the total NPV becomes zero. To four decimal places, a discount rate of 12.2816% makes the total NPV equal to zero. Instead of trial and error, one can use the Excel goal seeking tool, which is how I calculated 12.2816%.

This discount rate, which makes the NPV = 0 is known as the internal rate of return, or "IRR".

In the above equation, the IRR is the value of "r" for which NPV=0, if such a value exists. In other words, it is the value of "r" that solves the equation:

0 = C0/(1+r)0 + C1/(1+r)1 + C2/(1+r)2 +... (and so on) ... Cn/(1+r)n

For values of t up to 4, there are general solutions to the above equation, since the equation can be rewritten in the form

0 = A + Br + Dr2 + Er3 + Fr4 where A, B, D, E, F are computed by multiplying out the divisors in the previous equation and then rearranging the terms.

However that does not guarantee that there is a value of r that solves the equation. Even with simple quadratic equations, of the form 0 = A + Bx + Cx2 we know from school algebra that there may be no real solutions. As mentioned before, if all cash flows are positive, there will be no finite IRR.

For values of t greater than 4, there is no general solution, since in the nineteenth century Abel proved there was no algebraic solution to the general quintic polynomial equation.

Instead of having to use goal seeking, Excel contains a function which I have in the past found very useful, which computes the IRR for you. Its format is as follows. In the cell where you want the IRR to be shown, type:

=IRR ([range of cash flow values in a horizontal or vertical line, with the periods equally spaced],[a guess to get Excel's calculation started])

Its usage is shown in the spreadsheet extract below:

Simple NPV calculation
0.05 Discount rate
Time Cash flow PV of each cash flow Calculated values
0 -400 =B6/(1+$A$3)^A6 -400
1 20 =B7/(1+$A$3)^A7 19.047619047619
2 30 =B8/(1+$A$3)^A8 27.2108843537415
3 40 =B9/(1+$A$3)^A9 34.553503941259
4 50 =B10/(1+$A$3)^A10 41.1351237395941
5 60 =B11/(1+$A$3)^A11 47.0115699881075
6 70 =B12/(1+$A$3)^A12 52.2350777645639
7 150 =B13/(1+$A$3)^A13 106.602199519518
8 200 =B14/(1+$A$3)^A14 135.367872405737
9 150 =B15/(1+$A$3)^A15 96.6913374326696
10 100 =B16/(1+$A$3)^A16 61.3913253540759
NPV = total =SUM(C6:C17) 221.246513546886
Excel IRR of above cash flows =IRR(B6:B16,0.1) 0.122816316332845

Financial concerns with the internal rate of return

Those receiving a financial education are taught to consider the pros and cons of using IRR or NPV to evaluate projects, and in general to prefer the use of NPV for a number of reasons which are outside the scope of this article.

One point which is often validly made is that the mathematics will generate multiple values for the IRR. Very briefly, in general, for every sign reversal in the cash flows (where a negative cash flow is followed by a positive cash flow, or a positive cash flow is followed by a negative cash flow) one potentially generates another solution value.

In the above numerical example, with only one sign reversal, (-400 followed by solely positive cash flows) there was a unique value of "r" as the solution, with r = 0.1228 (i.e. 12.28%). However more complicated cash flow sequences with more sign reversals may generate several IRR values; all of them are as real as each other. Being conservative, one normally uses the lowest computed IRR value in such cases.

A more serious problem with Excel

The spreadsheets I build for myself normally compute NPV rather than IRR. However for decades I have regularly used the Excel IRR function, and regarded its outputs as reliable. Accordingly I had a shock last month.

I was performing a simple calculation. A few years ago, I bought an investment for £7,962.64, received a dividend of £157.32, bought some more of the same investment for £2,310.24, received some more dividends, and still own the investment. (The identity of the investment is not disclosed, because I do not wish to be exposed to the risk of giving investment advice.)

I wanted to see how the investment was performing, by computing the monthly IRR. (The cash flows occur more frequently than annually.) The monthly IRRm can easily be converted to an annual IRRa using the following formula:

1 + IRRa = (1 + IRRm)12

The cash flows are set out on the "Investment" tab of the spreadsheet and reproduced below:

Month ending Net cash (out) in
31 May 2014 -     7,962.64
30 June 2014
30 July 2014          157.32
30 August 2014
29 September 2014
30 October 2014
29 November 2014
30 December 2014 -     2,310.24
29 January 2015
28 February 2015
31 March 2015
30 April 2015
31 May 2015
30 June 2015
31 July 2015 285.12
30 August 2015
30 September 2015
30 October 2015
29 November 2015
30 December 2015 144.54
29 January 2016
29 February 2016
30 March 2016
30 April 2016
30 May 2016
29 June 2016
30 July 2016          291.06
29 August 2016
29 September 2016
29 October 2016
29 November 2016
29 December 2016      12,443.24

The investment is still owned, so the 29 December 2016 cash flow is the dividend received in December 2016 plus the market value of the investment at 29 December 2016.

I then used the Excel IRR function to calculate the monthly IRR, and was delighted to see a monthly IRR of 4.9% which equates to an annual IRR of 78.4%. However a moment's look at the cash flows, which only show a total undiscounted gain of £3,048.40 shows that the annual IRR could not possibly be 78.4%.

When in doubt with a calculation, it helps to perform it more simply, without relying upon complicated formulas. Accordingly, I set up a calculation in the form of a bank account simulation, as shown by the table below:

Monthly return 1.00%
Date Opening value Add cash net if negative, withdraw if positive Investment return on opening amount at specified monthly return rate Closing value
31/05/2014             -       7,962.64               -      7,962.64
30/06/2014    7,962.64              -            79.63    8,042.27
30/07/2014    8,042.27 -     157.32          80.42    7,965.37
30/08/2014    7,965.37              -            79.65    8,045.02
29/09/2014    8,045.02              -            80.45    8,125.47
30/10/2014    8,125.47              -            81.25    8,206.73
29/11/2014    8,206.73              -            82.07    8,288.79
30/12/2014    8,288.79     2,310.24          82.89  10,681.92
29/01/2015  10,681.92              -           106.82  10,788.74
28/02/2015  10,788.74              -           107.89  10,896.63
31/03/2015  10,896.63              -           108.97  11,005.60
30/04/2015  11,005.60              -           110.06  11,115.65
31/05/2015  11,115.65              -           111.16  11,226.81
30/06/2015  11,226.81              -           112.27  11,339.08
31/07/2015  11,339.08 -     285.12         113.39  11,167.35
30/08/2015  11,167.35              -           111.67  11,279.02
30/09/2015  11,279.02              -           112.79  11,391.81
30/10/2015  11,391.81              -           113.92  11,505.73
29/11/2015  11,505.73              -           115.06  11,620.79
30/12/2015  11,620.79 -     144.54         116.21  11,592.45
29/01/2016  11,592.45              -           115.92  11,708.38
29/02/2016  11,708.38              -           117.08  11,825.46
30/03/2016  11,825.46              -           118.25  11,943.72
30/04/2016  11,943.72              -           119.44  12,063.15
30/05/2016  12,063.15              -           120.63  12,183.79
29/06/2016  12,183.79              -           121.84  12,305.62
30/07/2016  12,305.62 -     291.06         123.06  12,137.62
29/08/2016  12,137.62              -           121.38  12,259.00
29/09/2016  12,259.00              -           122.59  12,381.59
29/10/2016  12,381.59              -           123.82  12,505.40
29/11/2016  12,505.40              -           125.05  12,630.46
29/12/2016  12,630.46 - 12,443.24         126.30       313.52

Here one starts by making the investment of £7,962.64, with all positive cash flows being shown as withdrawals from the bank account, and extra investments shown as additions to the bank account. The specified rate of return is then the interest that the "bank" is deemed to pay on the net cash invested into it, i.e. your "net investment in the account."

With a specified monthly rate of return of 1%, the closing value after deducting the 29 December 2016 cash flow from the dividend and notional market value sale, is £313.52. That shows that 1% is a good guess at the monthly rate of return, but is slightly too high since there is allegedly something left over after you have taken everything out of the investment.

Again, using the Excel goal seeking tool, one can easily compute that a monthly return of 0.9181% leaves you with a terminal value of exactly zero. This corresponds to an annual rate of return of 11.6% which is by no means spectacular, but is much better than doing nothing or losing money.

In the linked spreadsheet, on the "Investment" tab, I have set out the calculation giving rise to the spurious Excel calculated monthly IRR of 4.9%. Using that to discount each of the individual cash flows shows that one does not get an NPV of zero. Conversely when the true internal rate of return of 0.9181% is used to discount the cash flows, the NPV is indeed zero as expected.

I could not understand why Excel's IRR function was giving such ridiculous answers. I have used IRR often in the past, and found it to generate reliable results.

Explanation

A chance discussion with one of my sons, who is a computer programmer, found the explanation. It illustrates how frustrating computers and their logic can be.

In my past uses of the IRR function, the cash flows I have been working with have usually been calculated from other numbers on the spreadsheet. Accordingly, every cell in the range of cash flows is either non-zero (an actual cash flow) or zero (representing the total of some cells that add to zero.)

However in the case of this investment, since the cash flows only occurred on certain dates, I simply pasted the cash flows on those dates into column B on the tab "Investment". The other cells in column B were left blank.

However, Excel does not treat those blank cells as if they contained a zero. Instead, the cell, and the row the cell is on, are ignored in the IRR calculation.

This is explained in more detail on the spreadsheet's "Explanation" tab. That tab has three sections.

Base case calculations

This simply repeats the calculations on the "Investment" tab leading to the spurious IRR of 4.9% per month.

Excel has treated the blank cells as if they did not exist. Accordingly it has calculated actually IRR for the set of cash flows below.

In this section, I have deleted the rows that had blank cells. That is how the Excel IRR function is seeing the data. After deleting the rows with blank cells, I understood why Excel was calculating a 4.9% per month IRR. With a very short period of time for the investment to generate the dividends and final capital value, a very high IRR is necessary.

If in the original set of cash flows, zero is inserted in the cells where no cash flow arises, Excel sees the correct spacing of the periods and then computes a correct IRR

This section reproduces the cash flows on the investment.

However here an explicit "0" is typed into each cell when there is no cash flow. Excel's IRR function now sees the cash flows with their true spacing over time, and now computes the correct IRR of 0.9181%.

Concluding comments

Having had this experience, I will ensure that in future there are never any blank cells in a range if I am computing the IRR of that range of cash flows.

This is problem I have never encountered in several decades of using Excel, which is why I have written this page to share the experience.

 

The Disqus comments facility below allows you to comment on this page. Please respect others when commenting.
You can login using any of your Twitter, Facebook, Google+ or Disqus identities.
Even if you are not registered on any of these, you can still post a comment.
comments powered by Disqus

 

Custom Search

Follow @Mohammed_Amin

Tap for top of page