Financial modelling with the XIRR formula in Excel

The standard IRR formula assumes cash flows occur at regular annual intervals. Where cash flows are more complicated or irregular you need to use a different Excel formula: XIRR.

How does Excel’s XIRR function work?

XIRR assigns dates to cash flows.  To practise with XIRR, in your own spreadsheet build a table with dates across the top and cash flows underneath.  Use XIRR to calculate the IRR for these cash flows.  Because it is looking at dates, XIRR “knows” when cash flows are generated and we don’t have the problems we were having with the standard IRR formula.

The XIRR challenge

Now for the toughest question we can possibly throw at you. Below you have a copy of information publicly released by XYZ Private Equity firm relating to its track record.  XYZ got a 81% IRR on Construction Co (the bottom of the list) but is this a little misleading – is the high IRR explained by a very early opportunistic exit?

Modelling XIRR

Using XIRR, answer this question: for how many years was XYZ invested in Construction Co? Is the answer:

  • A = less than 2 years
  • B = between 2 and 3 years
  • C = 3-4 years
  • D = 4-5 years
  • E = 5 plus years?

XIRR challenge hints

Here are a few hints that might help you with the XIRR challenge:

  • Hint 1: set up a little table with cash flows for the Construction Co investment (-1.0, 5.0), together with some experimental dates
  • Hint 2: use the XIRR formula to determine the IRR
  • Hint 3: iterate with the dates until the gap between them leads to an 81% IRR
  • Hint 4: if you wish, use goalseek to help with iteration (“set cell” = the cell that contains the XIRR formula, “to value” = 0.81, “changing cell” = one of the cells that contains a date)
  • Hint 4: work out how many years this gap equates to.
  •  

    Excel modelling with the XIRR formula

If you want to check your answers, you can download the XIRR formula Excel spreadsheet.

By the end of all that work you should be well aware of the pitfalls involved in using the IRR formula.  As we saw earlier, you need to enter zeros where you have no cash flows, and you need to enter the correct number of columns (= 1+ project duration).  We’ve also discovered the final potential trap in using Excel though.

Financial modelling trap no. 4

The IRR formula only works for annual cash flows.  Where cash flows are semi-annual or follow a different pattern use XIRR.

Extra for experts: Excel’s XNPV function

Did you know that Excel has an XNPV function (kind of ‘matching’ the XIRR function)? Is there a little potential trap in that one do you think? You bet your life there is. Find out more here: Excel’s XNPV formula.