Do you actually know the returns you're getting on your investments? Calculating the CAGR or XIRR using this free spreadsheet is simple!

Calculating Investment Returns (And Why You Should Track Historical and Projected Returns Together)

When it comes to investing, I’ve always just kept my investments as simple as possible, mainly in index funds that track the total market and/or in target date funds for my retirement accounts. I keep in mind the 5 simple truths of investing. I don’t try to time the market or pick individual stocks. Really, I’m a pretty boring person investor.

Do you actually know the returns you're getting on your investments? Calculating the CAGR or XIRR using this free spreadsheet is simple!

And that’s what you’re pretty much supposed to do, right? You always hear that the most important thing is to just keep saving consistently through bad times and good times and it’ll all work out, even if you don’t have the “perfect” asset allocation , the “perfect” choice of funds, or enter the market at the “perfect” time.

But how do you know how your investments are really doing? Relying on published rates of return won’t take into consideration management fees you’re paying, stock trading fees, timing of contributions or withdrawals, or other significant factors.

You definitely want to personally track the rate of return on each of your investment accounts. If you haven’t been doing it, start now!

What doesn’t get measured
doesn’t get managed.

And so, this is where I have to admit that for some of my investments, I had never tracked my returns. Never as in… until very recently I had never tracked my returns on a retirement account that I’d had from a previous employer since 2007. Yikes. And once I did look at it a little bit closer, I rolled that account over ASAP to a Vanguard traditional IRA account to avoid some fees that were reducing my returns.

My Rate of Return Epiphany

What I have done over the years is to project my investment balances into the future to formulate retirement and savings plan that will align with my goals. I suppose I’m more focused on the future than on the past (not a bad thing in theory).

I also had a separate excel spreadsheet to track my historical returns (once I became obsessed with figuring that out as well).

I’m always trying to simplify and organize things, especially my personal finances. I automate everything, especially transfers to my 24+ active financial accounts. Generally, I use as few spreadsheets as possible and keep them organized in digital folders.

I realized that it may be beneficial to combine my spreadsheets to include both historical and projected data. This way I could efficiently monitor my accounts both in past projected performance.

The Benefits of Combining Historical & Future Data

Since combining my spreadsheet functions, I have found the following benefits:

  • I can easily compare my projected performance to historical performance for my investments as well as comparing my returns to the total market returns
  • I can update both my historical data and my beginning balance at the same time

It’s definitely saved me some time, but the biggest benefit has been in keeping a long-term view of looking at my investments. It’s really encouraging to see how far I’ve already come and exactly how I can reach my long-term goals!

Why You Should Calculate Your Own Rates of Return

As a simple example that shows exactly why you should calculate your own rates of return, I found the following returns listed on one of my recent 529 college saving statements:

A 21.1% return since inception seems pretty good right? However, when I actually calculate my returns using the compound annual growth rate (discussed next) I get the following:

  • 2014 = 6.06%
  • 2015 = -.08%
  • 2016 = 7.55%
  • 2017 1st qtr = 4.41%

I doubt anyone is wildly jealous of these returns. But, knowledge is power and now I can compare these historical returns to those of the market overall and determine if I need to make any adjustments to my underlying investments.

I’m not trying to beat the market, but if my investments aren’t keeping up with the market based on my allocation of stock and bonds, it’s probably time to take a closer look!

How to Calculate Rates of Return

A simple way to calculate your investment returns is to use the compound annual growth rate, or CAGR.

CAGR = [(Ending Value/Beginning Value) ^ (1/n)] – 1, where n = number of years

The compound annual growth rate is a time-weighted return. In addition to the formula above, any contributions need to be subtracted from the ending value and distributions during the time period need to be added back, as they are not included in the formula.

An alternative to using CAGR is XIRR, or extended internal rate of return. This return factors in the presence and timing of contributions and distributions. It’s more complicated to calculate and understand, but also more accurate, especially when you have significant inflows and outflows. The Biglaw Investor has a great article about how to use XIRR if you’re interested in learning a little bit more.

To calculate your own personal rates of return, I definitely suggest using an excel spreadsheet (there are online calculators, but you want to be able to keep track of this on an ongoing basis!).

Yep, I’ve got you covered on this one (if you’ve been on my blog for more than 2 minutes you know I love my spreadsheets!).

Using the Spreadsheet to Calculate Historical & Projected Returns

The following spreadsheet template is designed to allow you to track historical and projected returns together. Because of variances in contribution frequency, differences in types of investment accounts and other factors, it may require some minor additional setup to make it work for you (totally worth a few minutes of your time, I promise!).

Download the spreadsheet for Excel | Google.

Here are a few quick notes about the spreadsheet:

  • Record all account transactions on a monthly basis (combine contributions made more frequently than monthly such as retirement accounts).
  • You can keep track of gain/loss on a monthly basis, quarterly basis (as shown), semiannual basis or annual basis by entering them in for historical returns or adjusting the formula for projected returns.
  • Start with your historical data and returns and be sure to separate them from projected data and returns by shading those cells in a separate color, as shown.

As a word of “warning”, this spreadsheet may require a little bit of knowledge about time value of money, Excel and calculating investment returns (3 of the most fun things ever, right?).

Final Thoughts

It’s so important to quantify the returns you’re getting on your portfolio to make sure you’re on track with reaching your important long-term financial goals.

It all comes back to those financial goals and stashing that money under your bed simply isn’t going to get you there!

How do you track your investment returns?

LIKED IT? PLEASE SHARE!

11 Responses

  1. From one math nerd to another – I love this! 🙂 Many – perhaps most – people don’t have any idea what type of returns they’re getting with their investments. Most also have no idea what level of fees they’re paying. It seems popular to remain clueless… and that is working against those investors.

    1. I’m going to take that as a compliment – I’m both a math nerd and a spreadsheet nerd :)! Your latest post was right along these same lines – know your fees!

    1. Thanks Amy! I doubt yours is that bad ;). If you’re tracking your returns, you’re well ahead of the vast majority!

  2. This is probably what drives me most crazy about Dave Ramsey’s investment advice. He uses average instead of CAGR to pump up his returns. There is a huge difference between average and CAGR and he calls us finance weenies for getting in the weeds. I want to yell it’s common sense to give someone an accurate number instead of deception. Anyway big fan of CAGR 🙂

  3. “biggest benefit has been in keeping a long-term view of looking at my investments”

    This is what I try to do too…key word try. I manually go in once per quarter to list the market value of all of my accounts to see how I’ve done since the past quarter. I have to account for inflows however to see what my contributions are VS how the investments themselves are doing.

    1. Sounds like you’ve got a great system to track how they’re doing! It is important to see how the underlying investments are doing for sure :).

welcome!

I’m Kathryn Hanna-wife, mother of 3 and a Certified Public Accountant. I love to budget (really, I do!) , build spreadsheets and spend money on travel, sewing supplies and good chocolate.

subscribe!

12 Month Financial Plan Sidebar

topics!

Easy-to-customize spreadsheets to improve your entire financial life from budgeting to tax and retirement planning.