An XIRR calculator finds your true yearly return when your money goes in and out at irregular times. Enter the date and amount of each cash flow, and this XIRR calculator solves for the annual rate, using the same method as the XIRR function in Excel. It's useful when you've bought shares on different dates, added to a fund now and again, or taken money out along the way. A simple percentage gain ignores timing, whereas XIRR gives proper weight to how long each amount was invested.
What is XIRR?
XIRR stands for extended internal rate of return. Microsoft describes the function as returning the internal rate of return for a schedule of cash flows that isn't necessarily periodic. The ordinary IRR assumes that the cash flows arrive at equal intervals, while XIRR uses the actual dates. Payments are discounted based on a 365-day year.
XIRR formula
XIRR is the rate r that makes the following sum equal to zero:
Sum of Pᵢ ÷ (1 + r) ^ ((dᵢ − d₀) ÷ 365) = 0
Here, Pᵢ is each cash flow, dᵢ is its date and d₀ is the first date. There's no direct way to solve this, so the calculator narrows in on the rate step by step until the sum is effectively zero.
How to use the XIRR calculator
- Enter each date as MM/DD/YYYY or YYYY-MM-DD.
- Enter each amount. Money you pay in is negative, and money you receive is positive.
- Add the current value of your investment as a final positive amount, dated today.
- Press Calculate XIRR.
XIRR example
Microsoft's example uses a payment of −10,000 on January 1, 2008, followed by receipts of 2,750 on March 1, 2008, 4,250 on October 30, 2008, 3,250 on February 15, 2009, and 2,750 on April 1, 2009. In total, 13,000 comes back on 10,000 paid in, and the money is returned over about 15 months. The XIRR is about 37.34%. That's much higher than a plain 30% gain would suggest for a period of over a year, as most of the money came back early.
XIRR and IRR compared
- IRR assumes that cash flows occur at regular intervals, such as every year or every month.
- XIRR uses the real date of every cash flow.
- Simple return looks only at the gain and ignores time altogether.
- CAGR suits a single investment that's held from start to finish.
For a regular monthly investment plan, IRR and XIRR will be close. If the timing is uneven, XIRR is the better measure.
When is XIRR useful?
It's useful for tracking a portfolio with several purchases over time, comparing investments that started on different dates, measuring a monthly investment plan, assessing a rental property with irregular income and costs, and working out the return on a loan that you have made. Once every holding is expressed as an annual rate, you can compare them fairly with one another, or with a savings account.
How to read your result
A positive XIRR means that you've earned a return, and a negative one means a loss. Be careful with very short periods, as a good week can turn into an enormous annualized number that could never realistically continue. An XIRR measures what has already happened. It also assumes that any money returned along the way could earn the same rate, which might not be possible in real life. For that reason, it's best to treat it as one useful figure among several, along with the total gain and the risk that you took.
Fees, dividends and taxes
To get an accurate result, use the amounts that actually moved. If fees were taken from what you paid in, include them in your payment. Dividends that you received in cash should be entered as separate positive amounts on the date they were paid, while reinvested dividends are already included in your final value. Taxes can be included if you'd like an after-tax return, but be sure to treat all of your investments in the same way when you compare them.
An example with a final value
Imagine that you invested 5,000 on January 1, then another 5,000 six months later, and that your holding is worth 11,000 one year after the start. Enter the two payments as negative amounts and the final value as a positive amount on the last date. The gain is 1,000 on 10,000 paid in, which is 10% overall. But half of the money was invested for only six months, so the XIRR is noticeably higher than 10%. This is exactly the kind of timing effect that a simple percentage misses, and it's why XIRR is favored for tracking investments that are added to over time.
Common mistakes
- Getting the signs wrong. Money paid in must be negative.
- Forgetting the current value. Without it, the return looks like a loss.
- Mistyping a date. Check the month and day order.
- Comparing with a total return. XIRR is yearly, and total return isn't.
Related investing tools
For regular intervals, try the IRR calculator, get a basic figure from the ROI calculator, and project growth with the compound interest calculator.
What next
Use this often? Add XIRR to your Home screen.
Frequently asked questions
How does the XIRR calculator work?
It searches for the yearly rate that brings the value of all your dated cash flows to zero, on a 365-day year.
What is the difference between XIRR and IRR?
IRR assumes regular intervals. XIRR uses the actual date of each cash flow.
Should investments be negative?
Yes. Money you pay in is negative, and money you get back is positive.
Why is my XIRR so high?
Over short periods, a small gain becomes a large annual rate.
Does it match Excel?
It uses the same formula and gives 37.34% on Microsoft's example.
Sources
Was this tool helpful?
What went wrong?
We only receive the reason you pick, never what you typed into the tool.
Thanks for your feedback.