Nástroje, automatizace a praxe
How to Calculate Your True Portfolio Return Using XIRR
Key takeaways
- A simple return ignores the timing of contributions and withdrawals — XIRR does not.
- XIRR works with dates and cash flows: contributions as negative numbers, withdrawals and current value as positive.
- The XIRR result is an annualised return (p.a.) — directly comparable with a benchmark.
- For accurate results, enter each contribution and withdrawal separately.
- XIRR below the benchmark signals you bought at unfavourable times or paid unnecessary fees.
XIRR (Extended Internal Rate of Return) is a function that calculates your true annualised return, precisely accounting for the timing of every contribution and withdrawal. Without it you are comparing apples with oranges.
Why a simple return is not enough
Imagine you invested CZK 10,000 in January and another CZK 100,000 in December. The portfolio grew 5%. But 90% of the money was invested for only one month — the market's actual contribution to your outcome was minimal. A simple return misses this; XIRR does not.
How to set up XIRR in Excel step by step
- Column A: dates of all contributions and withdrawals + today's date at the end
- Column B: contributions as negative numbers (money leaves your pocket), withdrawals as positive
- Last row of column B: current portfolio value as a positive number (as if you sold everything today)
- Formula: =XIRR(B1:B100,A1:A100)
- Format the result as a percentage — you get the annualised return p.a.
What to do with the result
Compare XIRR against your benchmark — for example the S&P 500 or MSCI World return for the same period. If your XIRR lags, it may be down to timing (you bought at expensive moments), fees, or unsuitable allocation. XIRR below the benchmark is a signal for reflection, not panic.
Mistakes that distort the result
The most common mistake: a forgotten contribution or withdrawal. Every cash flow must be in the table. The second mistake: using the portfolio's current value in the broker's currency without converting to CZK — always use one currency throughout. If you maintain your portfolio tracker continuously, you always have the data for XIRR at hand.
FAQ
What exactly does XIRR measure?
XIRR measures the average annualised return on an investment, precisely accounting for the timing of contributions and withdrawals. The result is equivalent to the annual interest rate that would produce the same outcome for the given cash flows.
What is the difference between XIRR and a simple percentage return?
A simple return compares the starting and ending value. XIRR takes into account exactly when you invested or withdrew money. If you invest regularly, the difference can easily be 5 percentage points.
Does XIRR work in Google Sheets?
Yes, the syntax is the same: =XIRR(values,dates). Both Google Sheets and Excel support it without any add-ins. Make sure your dates are in the correct format — otherwise the function returns an error.