XIRR Function in Microsoft Excel
From ForcaWiki, the simple encyclopedia
| In one sentence | A Microsoft Excel formula that finds the yearly rate of return for money that arrives on real, irregular calendar dates instead of a neat once a year schedule. |
|---|---|
| Category | Software |
| Related | Internal Rate of Return (IRR), XNPV Function in Microsoft Excel, Microsoft Excel |
The internal rate of return answers "what single interest rate would make this deal break even," but like plain NPV, it assumes payments land on a tidy, evenly spaced schedule. XIRR asks that exact same question for money that arrives on real, messy calendar dates, the way it actually does in most businesses.
The same idea, with real dates
XIRR works hand in hand with XNPV. Instead of picking a rate yourself, XIRR searches for the one specific yearly rate that would make XNPV come out to exactly zero for your exact cash flows and exact dates.
A worked example
Take the same lemonade stand payments from the XNPV example:
- January 1, 2024: spend 50 dollars.
- July 15, 2024: earn 30 dollars.
- March 3, 2025: earn 20 dollars.
- August 20, 2025: earn 25 dollars.
Earlier, using a 10 percent rate, XNPV came out positive, which is a hint that the true break even rate must be higher than 10 percent. Run XIRR on these exact same dates and amounts, and it finds that rate: about 48.3 percent per year. That is the lemonade stand's real, exact date return, a strong one, and a more accurate number than plain IRR could give, since it accounts for precisely when each dollar actually showed up.
How to use it
- Open Microsoft Excel and enter your dates and cash flows in two columns, just like for XNPV: dates in A2:A5, and matching amounts in B2:B5, with the very first amount entered as a negative number.
- Click on an empty cell where you want the answer to appear.
- Type
=XIRR(, then select your cash flow numbers first, B2:B5. Unlike XNPV, XIRR does not start with a rate, since finding the rate is the whole point. - Type a comma, then select your dates, A2:A5, and close the parenthesis, so the formula reads
=XIRR(B2:B5, A2:A5). - Press Enter. Excel searches for the answer and displays it, about 0.483 for this example, which you can format as a percentage to read it as 48.3 percent.
Fun facts
- XIRR does not solve for its answer in one direct step the way a simple formula would. Excel tries out many different rates and keeps adjusting, homing in closer and closer until it finds the one where XNPV becomes zero.
- XIRR has an optional third ingredient, a starting guess for the search, which defaults to 10 percent if you leave it out. Almost nobody ever needs to change it, but it is there for unusually tricky cash flow patterns.
- Because real businesses almost never get paid on a perfectly even once a year schedule, XIRR is often the more realistic choice for judging an actual investment than plain IRR.