Select Page

# 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?

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.
•