A to Z of Excel Functions: The IRR Function

Welcome back to our regular A to Z of Excel Functions blog. Today we look at the IRR function.

The IRR function

There are many techniques employed to value an asset, a project, a business, a shareholding, and so on. However, one is arguably more common than the rest these days – Net Present Value (NPV) using discounted cash flows.

A discounted cash flow (DCF) is a straightforward computation:

• Let’s assume inflation is running at 10% (and we will assume this is after tax as we all earn our wages after tax and increases in spending affect this after-tax wage)
• Something that costs $100 this year will cost 10% more next year, i.e. $110
• Something that costs $110 next year will cost 10% more the year after, i.e. $121
• Something that costs $121 in that year will cost 10% more the following year, i.e. $133.10
• However, they are all worth the equivalent of $100 now (as we “discount” these future values back to their present values).

Note that all of these valuations are for a point of time not a period. This is a common mistake in modelling. We have to understand when we assume the cash flows will occur. The three most common assumptions are at the start, the middle or the end of the period in question. This assumption will obviously vary the overall valuation as a consequence.

Valuations include both cash inflows and cash outflows. Adding up all these positive and negative present values, provides a netted off total: the Net Present Value (NPV). The aim is to generate a positive return (a positive NPV) for a given rate of discounting, known as the discount rate.

The Internal Rate of Return (IRR) is the name given to the discount rate that makes the Net Present Value (NPV) of a range of cashflows zero. For example, if I invest $100 now and receive $121 back in two years’ time this would give me an annual IRR of 10% since:

($100) + PV($121) = ($100) + $121 / (1 + 10%)²

= ($100) + $100

NPV = 0

It is often used to calculate cost of capital hurdle rates, bond rates, discount rates implicit in leases and Compound Annual Growth Rates, amongst other tasks.

For cashflows with equal periodicity, Excel has a function that will estimate the IRR:

• IRR(values, [guess]) returns the periodic IRR for a set of sequential cashflows occurring on a regular periodic basis. There must be at least one positive and one negative value in the range. IRR will then cycle through an iterative technique (up to 20 times) to try and find an answer which is accurate to within 0.00001%
• If the guess is not specified, Excel will assume that it is 10% (0.1).

Both are fraught with problems in practice and the associated Excel file provides several documented examples. However, before we discuss some of the problems with the functions, let me compare and contrast with a simple example:

In the illustration above, we consider three slightly different cashflows.  The first one has the old faithful “hockey stick” projections of an outflow followed by five successive cash inflows.  In our example, this would give us a periodic IRR of 1.64%.  If the periods were months and we wished to convert this to annual rate then, using the compounding formula, this would be:

                                                (1 + 1.64%)¹² -1 = 21.54%.

The second example for IRR gives the same rate – which is correct.  Start dates for IRR are irrelevant: the only thing that matters is how long after the initial cashflow subsequent flows occur.

Unfortunately, the third scenario is not right and demonstrates a weakness in the IRR function.  Here, the outflow still occurs in the first period, but then there is a two-month delay before the inflows are received.  Clearly, this will impact both the NPV and the IRR.  The IRR is unaffected however – because blanks are ignored by the IRR function.  To get Excel to calculate the IRR correctly here, zeroes should be entered into the two blank cells.

More than One Solution

There can be more than one IRR.  Every time a cashflow series changes sign (i.e. goes from positive to negative or vice versa) there is potentially another solution.  Consider the following:

Here, prompted by a guess in the sister XIRR function (albeit of the other solution 21.43%), XIRR and IRR return two different IRRs associated with this cashflow scenario.

It is important to not only check that an IRR gives an NPV of zero but that it is also the correct one in the circumstances.

So What Would You Use Instead?

There are times when accuracy is paramount, e.g. bond issues involving large sums of money. Excel will only calculate IRR on a maximum cycle of 20 times. Goal Seek, on the other hand, can calculate on a cycle of up to 32,767 iterations (this may be changed in Excel Options, ALT + T + O) with greater accuracy.

Therefore, when it is business critical, I always use Goal Seek to calculate IRRs. It is a very simple approach and uses the longhand checking approach described above. For example:

Here, an NPV calculation is constructed long hand with the discount rate an input in cell E46 (this is similar to an example in the attached Excel file).

Next, call up Goal Seek (ALT + T + G):

The NPV (cell F57 here) can be set to zero by Goal Seek, by changing the rate in cell E46. Once you have clicked ‘OK’ in the dialog box, the algorithm will cycle through to a solution where possible with the NPV displayed at the same time to confirm that the value obtained is indeed the IRR.

It may be a cumbersome method, but give me reliability over elegance any day.

We’ll continue our A to Z of Excel Functions soon. Keep checking back – there’s a new blog post every other business day.