0

Novice to retirement funds and investing here. I'm wanting to build a spreadsheet in Excel that can help calculate my annual rate of return for retirement accounts.

I have some elementary questions about how to calculate whether my [American] retirement fund [401K and other similar funds like 401A and 403B] is performing optimally with the rate of return or interest gained in the 8-10% range as is often recommended.

I have got my brain in a pretzel by overthinking this I'm sure, but when looking at a monthly or quarterly percentage I'm unsure of the math to ensure I am getting at least 8% per year.

As an unrealistically simple scenario, let's say I see exactly a 2% gain each quarter. Do I add the four quarters up (2% +2% +2% +2%) and arrive at the healthy 8% return, or do I need 8% each quarter to have 8% return annually? Basically I need to know how to look at a quarterly statement and know whether to say "Wow, only 2%? I'm under-performing by 6%. Time to invest in some different funds" or else (say in Q1) say "Okay, 2%. As long as this holds each of the remaining quarters then I'm on track for my 8% goal".

Realistically though you might have 2% one quarter -1.5% the next and 3% the next, I realize. As I said I'm likely over thinking this, but if I can build a spreadsheet to punch in my returns monthly even and then have a YTD return field calculating on all the months concluded thus far that would help me visualize my gains better. Can someone clarify how to do that math so I can create such a calculation in a spreadsheet?

I envision the format being something like this:

enter image description here

7
  • Usually the plan administrators can provide this for you, look for "performance" in their reports or on their website.
    – littleadv
    Apr 2 at 17:45
  • I'm aware of reporting tools on such websites, but that doesn't get me closer to having the power to calculate this myself. Plus obviously I need someone to sort out my confusion on whether the gains are calculated by adding the percentages (2% * 4) each quarter or not. Without knowing that even a reporting tool will not tell me if I am on track (YTD) for my annual goal. Apr 2 at 17:49
  • No, it's not adding percentages, it's calculating gain vs basis and tracking that every day.
    – littleadv
    Apr 2 at 17:51
  • 1
    8% over a year translates, roughly, to 2% per quarter. It's not really hard to achieve with broad index funds as long as the stock market is moving in the right direction. VOO is almost 10% YTD for this year, for example.
    – littleadv
    Apr 2 at 18:22
  • 1
    Compound interest formula works well for ... compound interest. Investment returns are compound, but they're not straight line, so it doesn't fit exactly.
    – littleadv
    Apr 2 at 18:24

2 Answers 2

1

If you look at gains more frequently then annually, then you compound those gains to come up with an equivalent annual rate. The simplest way to do that is to add 1 to each periodic rate and multiply them together, so if you made 2% each quarter, then your equivalent annual gain would be

(1.02 * 1.02 * 1.02 * 1.02) - 1

or

(1 + 0.02)^4 - 1 = 8.24%

or, if your goal is 8% annual gain, then you can reverse the formula to find the quarterly gain you need:

1.08^(1/4) - 1 = 1.94%

Note that you need slightly less than 2% each quarter to end up with 8% for the year.

However, gains are not always the same, and are not even always positive. Negative gains tend to have a bigger drag than the equivalent positive gain; for example, the result of a 10% gain followed by a 10% loss is

(1+ 0.1) * (1 + -0.1) = 1.1 * 0.9 = 0.99

So your gains must, on average, be larger than your losses just to break even. Luckily the market in the US tends to gain more than it loses, resulting in positive returns over long periods of time.

If you're tracking this in a spreadsheet, then the math is as simple as mentioned above - add 1 to each periodic return, multiply them all together, and subtract 1 from the result. If you have monthly returns then you have 12 months to multiply together to get an annual result instead of 4 quarterly returns.

1
  • Thank you for the clear and straight-forward answer here! This is what I was looking to visualize and confirm. Apr 2 at 18:36
0

As an unrealistically simple scenario, let's say each quarter I see exactly a 2% gain each quarter. Do I add the four quarters up (2% x 4) and arrive at the healthy 8% return, or do I need 8% each quarter to have 8% return annually?

Keeping with this simple scenario, if there were no other investments into the account; and the value rose by 2% each quarter your annual growth would be

(1.02)^4 or  1.0824

That is because that 2% growth is being compared with the starting balance each quarter.

Of course if you only checked once a year then you would see it as 8.24% growth.

Once you factor in your contributions 3 to 7 times during the quarter, and any company match 3 to 7 times each quarter you can't just look at the starting and ending balance, because some shares were only in your portfolio for part of the quarter.

Remember the prices changes every day, so the path will never be smooth.

1
  • For clarification, that 8.24% is within the expected range most brokers and retirement account managers refer to (the 8%-10% range, maybe 7% some years) because they are speaking in terms of an ANNUAL percentage, correct? They are never referring to the gradual month by month or quarter by quarter percentage gains, right? Essentially my question matches this one on CNN Money: "I'd like to earn a steady 8% a year on my retirement portfolio. How can I get an 8% return on my investments?" money.cnn.com/2015/05/13/retirement/… Apr 2 at 18:33

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .