As you invest, it is important to know about how you are doing. It is easy to see how your individual investments are doing 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. –
While I don't think you need to look at your investments every day, or even every month, from time to time you ought to check in and see how you are doing. 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, being able 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 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. Amounts you contributed to retirement and other investment accounts are positive. 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 date.
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%.)
Now for an example:
If you want, click on the button in the lower right and follow the directions if you want to download this file to play with it.
Remember that 9.75% is an annualized return, so it means that between 7/11/07 and 5/19/11 this investment returned 9.75% PER YEAR. If your period of time is less than one year, it will also annualize the return. For example, if your period is 6 months, and your return is 5%, then XIRR would return 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. Of course, if you don't reinvest dividends, those should be considered withdrawals from the account, just like 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.
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.