About this lesson
IRR and XIRR: the return on an investment, and why real cash flows need dates
IRR gives the return that makes a project's NPV zero, including today's cash flow in the range: =IRR(B4:B9). It assumes the cash flows are exactly a year apart. When they aren't, give Excel the dates: =XIRR(B4:B9,A4:A9) for the return and =XNPV(B1,B4:B9,A4:A9) for the NPV.
Using Google Sheets? Do this lesson in Sheets.
=IRR(B4:B9)What you'll practise
Work out the return on a minority stake in a cold-storage business: IRR on the cash flows, then XIRR and XNPV on their real dates, and see how much the dates move the answer.
4 steps · 40 points · the cells outlined in gold below
- IRR
- XIRR
- XNPV
- dated cash flows
- hurdle rates
The data
| A | B | |
|---|---|---|
| 1 | Hurdle rate | 12.00% |
| 2 | ||
| 3 | Date | Cash flow ($k) |
| 4 | 01/07/2025 | -4,800 |
| 5 | 27/03/2026 | 380 |
| 6 | 26/03/2027 | 425 |
| 7 | 24/03/2028 | 455 |
| 8 | 23/03/2029 | 490 |
| 9 | 14/02/2031 | 6,400 |
| 10 | ||
| 11 | IRR | |
| 12 | XIRR | |
| 13 | XNPV ($k) | |
| 14 | XIRR less IRR |