By Dr. Jim Dahle, WCI FounderAs you invest, knowing how you are doing is important. Luckily, it is easy to see how your individual investments are performing each year. They report to you their time-weighted return. Of course, that isn't the return you generate. You get a dollar-weighted return. It turns out that the dollar-weighted return for most investors is far lower than the time-weighted return of their investments. This occurs mostly due to performance chasing and the buying high/selling low phenomenon that results from it, but some of it occurs because most investors are adding money to their accounts throughout the year via ongoing savings.
While I don't think you need to look at your investments every day—or even every month—you ought to check in and see how you are doing from time to time. If you don't know what returns you have been getting, it is hard to gauge how well you are progressing toward your goals. It is also easier to get sucked into investments that promise a high return but don't actually deliver. In short, knowing how to calculate your own return empowers you as an investor.
The best way to calculate your return is to use the Excel XIRR function (also available with other spreadsheets like Google Sheets and financial calculators). This gives you a dollar-weighted return because it takes into account the timing and amount of your cash flows into and out of your retirement funds. It is surprisingly easy to calculate. All you need to know is the amounts you have put in or taken out of the account and the dates on which you did that. Here's a quick tutorial:
Put the amounts of your cash flows into column A. The amounts you contributed to retirement and other investment accounts are positive. The amounts you took out are negative. The last entry should be the current amount you have, as a negative number. Put the dates of the cash flows into column B. You need to use the Excel DATE function to do this. It looks like this: =DATE(2004,8,16) where 2004 is the year, 8 is the month, and 16 is the day.
Now, in another cell, put in the XIRR function. It looks like this: =XIRR(A1:A10, B1:B10, 5%) where your cash flows are in cells A1 to A10, your date functions are in cells B1 to B10, and 5% is your estimated return. (If left blank, it defaults to 10%.)
Here's an example:
Remember that 6.94% is an annualized return, meaning that between 1/1/20 and 3/1/25, this investment returned 6.94% PER YEAR. If your period of time is less than one year, it will also annualize the return. For example, if your period is six months and your return is 5%, then XIRR would return something closer to 10%.
If you would like to calculate a year-to-date return and/or calculate out your return for each calendar year you have had the investment, it only gets a little more complicated. First, you'll need to add in the value of the investment at the end of each year. I use two entries, the first negative and the second positive, both with the date of 12/31 of the given year.Then, run the XIRR function from the positive entry on 12/31 of one year to the negative entry on 12/31 of the next. See the next spreadsheet for details of how to calculate returns for partial years, full years, and the year to date.
Play around with it for a few minutes, and you'll figure it out. If you're a spreadsheet junkie, you can also break it down for each retirement account or even each individual investment. All you need are your inflows and outflows and the corresponding dates. If you want to calculate the yearly returns or the year-to-date return, you'll also need the year-end values of the investments. But you don't have to take into account fees, commissions, or any dividends or capital gains that are reinvested.
If you don't reinvest dividends, those should be considered withdrawals from the account, just like how fees paid from a separate account should be considered contributions to the account. XIRR is a powerful function that will allow you to calculate your portfolio's overall returns, no matter how many different retirement accounts you have.
You can download the two spreadsheets in this post here and here.
What do you think? How do you calculate your returns? Any questions on how to use XIRR?
[This updated post was originally published in 2011.]
Happy New Year WCI!
Quick question: If one were to take an average of their yearly XIRR’s over the past X number of years, how close should that average come out to equaling the total XIRR? I ask because in my own spreadsheet my overall XIRR (as of 1/1/2019) is a couple percentage points lower than if I take the average of the individual yearly XIRR’s over that same time period.
What am I missing here?
Thanks!
Average is not annualized. It’s the classic “If you lose 50% one year, how much do you have to earn the next year to break even?” The answer isn’t 50%, it’s 100%. Your annualized return (what XIRR provides) is supposed to be lower than your average annual return. The more volatile the investment, the larger the difference.
Thanks for responding to both of my comments and clearing that up.
I don’t think I like XIRR anymore after seeing what it did to my overall return following 2018 :-p
Unfortunately, the annualized return is the only one that matters.
Happy New Year Lee and everyone else.
Sounds like you’re forgetting arithmetic mean versus geometric mean.
Thanks for the reply.
You’re correct that I was using arithmetic mean rather than geometric mean, but unfortunately that didn’t seem to do the trick.
My Total XIRR over the past 7 years still equates to more than 2% lower than the geometric mean on those years’ individual XIRRs does.
Is it just simply that because my overall investment balance began 2018 at or near it’s highest total then ended up negative for the year that it is dragging down my overall XIRR? i.e. Losing 7% on $1,000,000 effects the portfolio more than gaining 20% on $100,000 does?
In other words, as my nest egg grows can I just expect the total XIRR to be amplified more by up and down years moving forward?
Yes, XIRR is a dollar weighted average, so the returns in the years with higher portfolio balances contribute more to the overall return.
Vanguard explains their Personal Performance calculations as using the IRR method (not = Excel IRR function necessarily) and state that it is a dollar-weighted value. So, for my Vanguard accounts then, I would not need to go through the Excel XIRR exercise, right? (Assuming I didn’t presume to check Vanguard’s math.) That is, I would be looking at the realistic calculation of my returns coming from Vanguard (vs. the marketing calculations).
I don’t know that Vanguard is doing anything wrong with their return calculations. I think they’re probably trustworthy. But you can double check them if you want.
Related to the question above, does anyone know what would account for a significant discrepancy between my personal return in excel using XIRR versus the total annual return calculated by an app like Personal Capital? There is about a 4% difference in my 2018 return between these two methods. I’ve extensively checked over my XIRR worksheet to confirm there are no errors and can’t come up with anything.
Not sure how Personal Capital does it. But 4% is obviously a huge difference so I don’t think I’d trust it if you’re sure your XIRR calculation is right.
Ok, I’m going to do this. I’m so intimidated by this – not because of the Excel function – because I have to go back to add every-other week contribution amounts from the last 8 years, plus cash contributions.
Hm.. Maybe I’ll just start as of being new to practice – that’s only 2 years of contributions. I plan to simply use the balance of all the accounts as of the day I’m starting to do the transactions as my first “contribution.” Does that seem right?
Also, there was a long period where money that was contributed sat in cash and paid “interest” and some dividends that sat in cash within retirement accounts before finally being reinvested. I imagine those do not require separate entries as withdrawals because XIRR should adjust the internal rate of return appropriate for the time this $$ sat in cash, yes?
Thanks as always!
Yes, that would work fine. Yes, XIRR will account for that return when calculating the overall return of the account. Obviously if you wanted the return of the cash and the investments individually, you’d have to enter all the little transactions between the two.
Excellent. Actually the last 2 years weren’t too hard – a little Office Excel-Fu and I was able to extract contributions only out of Fidelity with relative ease.
Something else interesting to me – the function doesn’t actually seem to require the transactions be in chronological order in the spreadsheet. Right now I just have an amalgam of transactions from each account piled into two columns, not sorted.
On the bright side, while the S&P has returned annually 10.5% over my assessment period, my porfolio including international, bonds and real-estate has returned… 9.5%. Now I need to figure out if I’m actually getting less volatility for that 100 basis points…
Any tips on how to “clean up” the XIRR spreadsheet, or will it just go on forever?
Mine is getting incredibly long with a lot of rows…
Wish there were a cleaner way to visualize it.
Sorry. Mine is pretty long. I guess you can start over any time you like though.
Since reading your original blog post, I have been tracking my returns for several years now – thank you so much.
Do you incorporate your investments into Private Real Estate funds on this spreadsheet? If so, I was curious how you incorporate yearly growth on multi-year investment funds?
Yes.
Some tell me what the increased value is, so I put that in. Others do not, so I leave it as the original value until sold and then update it to the sold value.
WCI,
Great post as always. Any suggestions on tracking IRR throughout your portfolio? I find the statements from most brokerages pretty lack luster overall (currently at Fidelity, TD institutional, & Schwab). I am pondering starting to do my own self directed investing and wanted to have a system in place to monitor things.
What do you mean “any suggestions?” The post you commented on explains how to track IRR. Did you have a specific question?
I have an excel sheet that I use to keep track of various financial things:
1) Monthly savings to my taxable account – helps me keep track of year to year savings amount and resultant savings rate
2) Networth tracker – at the end of the year I track assets and liabilities
3) Investment performance log – my current advisor gives quarterly statements. I log the starting value, ending value, contributions, appreciation, and rate of return
To be specific, I wanted to know if you have any recommendations as to how to track the financial performance of all your investment assets. I ask because if I decide to do my own investing I want to be able to honestly assess how things are performing. At the end of the day I want to be able to have a number for my rate of return each quarter and each year. Things obviously get complex with multiple brokerage accounts.
Are you putting things into excel? A paid financial tracker program? Yahoo finance?
I have started playing around with https://themeasureofaplan.com/investment-portfolio-tracker/
I apologize if my ask isn’t clear. Essentially I want to have things set up for if I decide to do my own self directed investing that I am not sitting around at the end of the year with a mess of numbers and not being confident that I am tracking how well or poorly I am doing.
Yes, I use Excel as noted in the post above.
Hi:
This post was ver helpful. Thanks.
It seems that the spreadsheets in the post have disappeared. Is there a way to get it back.
Thanks again.
Yes they have, haven’t they. I’ll have my tech guy look at it.
Working now. Thanks for all you do.
Our tech guy is great isn’t he? I shot it over to him, recorded two podcasts, and he fixed it while I was doing podcasts. I feel Hannibal from the A Team sometimes. “I love it when a plan comes together.”
https://www.pinterest.de/pin/69665125470647544/?amp_client_id=CLIENT_ID(_)&mweb_unauth_id={{default.session}}&_url=https%3A%2F%2Fwww.pinterest.de%2Famp%2Fpin%2F69665125470647544%2F&_expand=true
I have 2 managed bond accounts with Fidelity (one pre-tax, one taxable). They buy and sell bonds for me with a very low account fee (seems to be 0.02% unless I’m missing something). The idea behind this was to avoid the loss of NAV that occurs with bond funds when interest rates are rising, since if they are held to maturity and there is no default, one gets one’s principle back. In both cases, this year, according to Fidelity’s numbers, both accounts have lagged their benchmarks and even Fidelity’s similar managed bond funds. For example, my taxable bond account (invested in my state of MA), had a 1 year return of 0.44%, vs 1.52% for Barclay’s muni index, vs 1.44% for fidelity’s Mass Muni fund. My pre-tax bond account actually lost money.
If I do an XIRR calculation with only the starting value, the fee’s deducted and the ending value, I get a 1 year return of 8.25%. I know I am imputing the values correctly, because Quicken automatically calculates a 1 year IRR of 8.23%. The discrepancy is of similar size but opposite direction for my pre-tax bond account with Fidelity reporting -2.22% and Quicken reporting -7.94% returns. Why such big discrepancies? I wonder if the XIRR function is not appropriate for bonds that have been bought at discount or premium on the secondary market, and where their value changes over time.
No, you should still be able to use XIRR for that. Not sure what’s going on in your case.
As I think about it more, Fidelity must be calculating/estimating the value of my bonds on the secondary market were they to be sold today and factoring that into the returns. The reason I think this is because on my account page they are comparing the returns to bond indexes, which are adjusted for NAV.
I am trying to track XIRR in my account for each of the last 10 years. In each year, where I had a negative return, I get a #NUM! error. No issue when I have a positive return. I am using the same formula every year. Any solution to this problem?
That happens to me sometimes too. Sometimes putting in a different “guess” for the rate of return has fixed it. Sometimes just using a longer period of time (more than a few days) seems to fix it. Sometimes just putting the formula in again fixes it. It would be interesting to hear from a real Excel expert on this topic.
Hi- the examples for this article arent posted anymore. Could you please re-post them or send me your XIRR worksheet?
Thank you
Wonder what happened. Maybe it was an old plug-in we used for this 2011 post. I’ll have our tech team look at it and maybe it’s time to run it again and update it.
your insets with a think spreasheet examples and the download no longer display. Can you bring them back?
Trying to figure out the tech solution. Probably going to have to redo the post and just take screenshots. Bummer.
Updated this post today with new screenshots to replace the old embedded files.
Great explanation of XIRR. I first used it to calculate the IRR of our solar panel installation and had to teach myself how to use it – wish I’d seen this post and had spreadsheet.
As an FYI….
I recently was musing on what my total return was for the last 5 years and 10 years across all the accounts we have investments in, including taxable, tax free and pre-tax. Since most of these were held at Fidelity for over 10 years, I searched around and took at their Full View page. I’d looked at this in the past but never bothered doing much with it.
It turns out they have a nice total return calculator function there that shows monthly, quarterly or annual returns across your selected accounts along with csv downloadable details on opening and closing balances, and ins/outs, adds/minuses.
While this was pretty handy, the challenge for me would be to reconcile the transactions and sort through the various transactions to separate out the ones that legitimately should be counted in total return versus ones that Fidelity counted that actually shouldn’t have been. Reason being some balance reductions were just transfers out of fidelity into another outside investment, rather than actual balance declines due to investment performance. Their tool doesn’t distinguish between those two possibilities.
More work needed. With lots of complexity this tool might at least give me a big jumpstart on doing the XIRR calculation by consolidating all the relevant transactions that should be included.
I’m trying this out for the first time. I get a very different number than what Vanguard reports out for my roth IRA – is there something I’m missing? In contrast, the number I got for my 457(b) is the same as what Empower is reporting.
Not sure if you’re doing it wrong or if Vanguard is or if they’re using different dates or cash flows or a different method entirely. But since you apparently did it right with your 457, it sounds like Vanguard is doing something different. But check to see if you’re missing a withdrawal or contribution or something first.
Thank you for the helpful instructions. I’d like to try using XIRR to calculate my personal rate of return, and have saved all my old statements from when I started investing decades ago. Unfortunately, I did not save individual transaction confirmations. Some brokerages didn’t include specific contribution dates on their statements and only included the beginning balance, the total amount deposited, the gain/loss, and the ending balance, for each quarter. Most of my data is no longer available on the brokerage websites. What’s the best way to handle this for the XIRR calculations?
I’m currently entering all the information from my old statements into a spreadsheet. If I find I have a single missing statement, how do you suggest handling this for the XIRR calculations?
Maybe treat every contribution and withdrawal for the year as taking place on July 1st? Should be close enough over the long run.