Returns · 1 of 6
How to calculate your portfolio return in Excel
Many investors keep their portfolio in an Excel sheet. It is a good habit, but the formula almost everyone uses to work out their return breaks as soon as money goes in or out. We show you how to do it properly with an example you can copy: the simple return, the money-weighted return with the XIRR function, and the time-weighted return (TWR).
6 min read
In short
- If you never add or withdraw money, the simple return, =(current value - contributed)/contributed, is correct and you need nothing else.
- With contributions or withdrawals, the simple return stops being reliable, because it treats a euro invested all year the same as one that went in last week.
- To find out what your money earned, use Excel's XIRR function: it takes the date of each movement into account and returns an annual rate.
- To compare yourself with a fund or an index, calculate the TWR: the return of each sub-period between movements, chained together.
The usual formula: simple return
The simple return compares what your portfolio is worth with what you put into it. If cell B1 holds what you contributed and B2 what it is worth today, the formula is =(B2-B1)/B1, formatted as a percentage.
If you put in €10,000 and today you have €11,000, you have made 10%. As long as you do not move money, that figure is right and you need nothing else.
Why deposits break it
The problem starts with the second deposit. The formula treats a euro that has been invested all year the same as one that arrived last week, and the figure stops meaning anything clear.
Look at the example in the table. On 1 January 2025 you put in €10,000. On 1 July the portfolio is worth €10,800 and you add another €5,000. On 31 December it is worth €16,500. You have put in €15,000 and you have €16,500, so the simple return says 10%. But the €5,000 was only invested for half the year, so that 10% falls short. With a withdrawal, the error can go the other way.
| Row | A: Date | B: Cash flow (€) | C: Value before the movement (€) |
|---|---|---|---|
| 2 | 2025-01-01 | -10,000 | 0 |
| 3 | 2025-07-01 | -5,000 | 10,800 |
| 4 | 2025-12-31 | 16,500 | 16,500 |
Money-weighted return in Excel: the XIRR function
The money-weighted return (also called the internal rate of return) takes into account when each euro went in and came out. Excel calculates it with XIRR, which accepts movements on irregular dates. The plain IRR function assumes all periods are the same length, and a real portfolio does not work like that.
Set up two columns: dates in column A and, in column B, the movements seen from your pocket. What you put in is negative, what you take out is positive and, in the last row, the current value of the portfolio goes in as positive, as if you sold everything that day.
With the data in the table, type =XIRR(B2:B4,A2:A4). The result is 12.1%, and it is annual: the function always returns a rate per year. If your Excel separates arguments with semicolons instead of commas, use semicolons.
The TWR: chain the sub-periods
The time-weighted return answers a different question: how did your investments do, regardless of when you added money? To calculate it you need one more figure, the value of the portfolio just before each movement (column C).
Each movement closes a sub-period. First sub-period: from €10,000 to €10,800, an 8% gain. Second sub-period: it starts with €10,800 plus the new €5,000, €15,800, and ends at €16,500, a 4.43% gain. The TWR chains the two: =(1+8%)*(1+4.43%)-1, which gives 12.8%.
Why three different figures for the same portfolio? The simple return (10%) ignores time. The money-weighted return (12.1%) measures what your money earned, with its dates. The TWR (12.8%) measures your strategy, and it is the one to use when comparing yourself with a fund or an index. Here the TWR beats the money-weighted return because you added money just before the weaker sub-period.
Your sheet, step by step
Record every inflow and outflow of money
One row per deposit or withdrawal, with its date. If you do not track the portfolio's cash separately, each purchase counts as money coming in and each sale you do not reinvest as money going out.
Note the value before each movement
That is the figure the TWR needs. You will find it on your broker's statement for that day; if you do not write it down at the time, it is hard to recover later.
Calculate the money-weighted return with XIRR
Put the current value as a positive number in the last row and type =XIRR(cash flows,dates). The result is already annual.
Chain the sub-periods for the TWR
Work out the return of each sub-period, multiply all the (1 + sub-period return) values together and subtract 1 at the end.
Where Excel falls short
With a small portfolio and a single broker, the sheet works. The hard part is keeping it up: updating prices by hand, recording every dividend and every fee, converting what is in dollars at each day's exchange rate and combining the statements of several brokers, each with its own format.
The TWR suffers most, because it needs the exact value of the portfolio on the day of each movement, a figure almost nobody has to hand unless they wrote it down at the time.
The idea to take away
If you never move money, the simple return is enough. If you make deposits or withdrawals, use XIRR to find out what your money earned and chain sub-periods to get the TWR if you want to compare yourself with an index.
If you would rather not maintain the sheet, in MyPortfolio you can import your broker's statement or your own Excel, as well as CSV, PDF or screenshots: we calculate the money-weighted return and the TWR for you, with up-to-date prices.
Frequently asked questions
How do you calculate portfolio return in Excel?
If you have not moved money, use =(B2-B1)/B1, with what you contributed in B1 and the current value in B2. If you have made contributions or withdrawals, use XIRR for the money-weighted return and chain sub-periods for the TWR.
How do I calculate my return if I add money?
Enter each contribution with its date as a negative number, put the current portfolio value as a positive number in the last row and type =XIRR(movements,dates). That way each euro only counts for the time it was invested.
What is the difference between IRR and XIRR?
The IRR function assumes all periods are the same length. XIRR accepts movements on irregular dates, which is what a real portfolio has, so it is the one to use.
Does XIRR return an annual rate?
Yes. XIRR always returns a rate per year, whether your movements span a few months or several years. In the article's example it gives 12.1% a year.
Can you calculate the TWR in Excel?
Yes, but you need the portfolio value just before each movement. Calculate the return of each sub-period, multiply all the (1 + sub-period return) together and subtract 1 at the end.