Financial modelling with the XIRR formula in Excel
Where cash flows are more complicated or more irregular, Excel does have a formula to help us: XIRR. On many computers it is not part of a standard build and is available as an “add in”.
Is XIRR available to you in Excel?
You will very quickly find whether XIRR is available to you by going “insert” “function” and seeing whether you have XIRR towards the bottom of the list of financial functions.
If not, in Excel 2003 go “tools” “add ins”, tick all the options and then tick “OK”.
If you are working in Excel 2007, you should be able to see whether you have the XIRR function by trying to insert it from the Formulas tab. If you don’t have the XIRR function, click on the Office button at the top LHS, click “Excel Options”, and then click “Add-Ins”. If the Analysis ToolPak is inactive, click on it and then click “Go” down the bottom of the dialog box. Tick all the boxes and click on “OK”.
If after that you still don’t have XIRR available at that stage (e.g. because Add Ins are only available through your company’s network) you may have to resort to the help function in Excel or your IT manager to make any further progess.
How does Excel’s XIRR function work?
XIRR assigns dates to cash flows. If you have XIRR installed on your computer, 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 at exercise 8 in the spreadsheet with semi-annual cash flow.
Now for the toughest question we can possibly throw at you. If you get stuck, you can always get some help from the answers on the hidden tab in your spreadsheet – that’s what they’re there for!
XIRR challenge
Below you have a copy of information publicly released by XYZ Private Equity firm relating to its track record. XYZ got a high IRR on Construction Co (the bottom of the list) but is this because it sold out early? Using XIRR, answer this question: for how many years was XYZ invested in Construction Co?
| Cost (m) | Proceeds (m) | Money multiple | IRR (%) | |
| Home Building Co | 3.0 | 7.8 | 2.6x | 25% |
| Cake Baking Co | 2.0 | 5.0 | 2.5x | 56% |
| Lift Making Co | 1.6 | 4.8 | 2.9x | 34% |
| Construction Co | 1.0 | 5.0 | 5.0x | 81% |
- A = less than 2 years
- B = between 2 and 3 years
- C = 3-4 years
- D = 4-5 years
- E = 5 plus years
Click here if you would like a few hints. You can check your answer by sitting the last question of the course quiz.
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, you need to set up a table or use XIRR. Click here to continue with the advanced financial modelling course.
Back to top