Curious about how much money you'll accumulate in your Roth retirement account?

If you’ve got Microsoft Excel (or just about any other popular spreadsheet program) running on your computer, you can use its FV function to forecast the future value of your Roth IRA or Roth 401(k).

The FV function calculates the future value of an investment given its interest rate, the number of payments, the payment, the present value of the investment, and, optionally, the type-of-annuity switch. (More about the type-of-annuity switch a little later.)

The function uses the following syntax:

=FV(rate,nper,pmt,pv,type)

This little pretty complicated, I grant you. But suppose you want to calculate the future value of an individual retirement account that’s already got $20,000 in it and to which you are contributing $400-a-month. Further suppose that you want to know the account balance—its future value—in 25 years and that you expect to earn 10% annual interest.

To calculate the future value of the individual retirement account in this case using the FV function, you enter the following into a worksheet cell:

=FV(10%/12,25*12,-400,-20000,0)

The function returns the value 771872.26—roughly $772,000 dollars.

A handful of things to note: To convert the 10% annual interest to a monthly interest rate, the formula divides the annual interest rate by 12. Similarly, to convert the 25-year term to a term in months, the formula multiplies 25 by 12.

Also, notice that the monthly payment and initial present values show as negative amounts because they represent cash outflows. And the function returns the future value amount as a positive value because it reflects a cash inflow you ultimately receive.

That 0 at the end of the function is the type-of-annuity switch. If you set the type-of-annuity switch to 1, Excel assumes payments occur at the beginning of the period (month in this case), following the annuity due convention. If you set the annuity switch to 0 or you omit the argument, Excel assumes payments occur at the end of the period following the ordinary annuity convention.

This is useful but it's also important to keep revisiting your assumptions at least annually. Not many of us are achieving 10% growth in our retirement accounts, for example, so a lower rate might be more realistic even over the long term as the economy recovers. Plus the amount you can contribute into your retirement account will change over time as you change jobs or experience other life changes.

Forecasting anything 20 or 30 years out is a highly uncertain exercise, and your estimate can easily be off by 30 percent or more. It's a useful tool for determining very roughly whether you're on track to achieve your retirement income goals, but when I look at these forecasts I assume a very wide margin of error.

Thanks for sharing. I know it was probably just a randomly picked number, but I agree that 10% is too high. Forward projections of returns based on the famous 9-11% return of the markets is unreasonable right now. Returns are composed of inflation, dividend yield, and economic growth (along with swings in valuation, or P/E, which should cancel out over the very long run). Dividend yield is 2% right now, economic growth will likely average just 2-3%, and inflation is hopefully going to stick around 2-3%. Adding it all up you get plus or minus 7% as a reasonable expectation going forward.

But, for calculating the future values of my investments and my retirement date, I remove inflation. Currently I project a 4% real return on my retirement accounts.

For non-retirement accounts, don't forget to take out taxes. 15% capital gains tax on a 7% return will eat up a full percentage point. So you're left with a 6% return that then gets eroded by inflation... real return = 3%.

Depressing, but perhaps more realistic

Comments from a (reformed?) data addict.
You might want to use 3% or 4% if you include dividends for a long-term 'real' (over inflation) US equity return.

Post-WWII, US equities median long-term real (over inflation) price performance (no dividends) has been about 1.3% to 1.5% using the Dow and depending on your time horizon (20yrs=1.4% 30yrs=1.6% 40yrs=1.3% etc). So add in something for dividends (yield since 1985 for the Dow has ranged from about 1.5% to 4.5%, with a quick visual guess of 2.7% or so for a median) for a more complete picture.

A piece of good news - once you reach a 32 year time horizon, it becomes highly likely that you will come out ahead of inflation (over 94% of the time, of course that is "only" 35 examples of 32-year time horizons eg 1945-1977, 1946-1978, etc).

some methodology notes...
I used CPI-U for inflation and the Dow Jones Industrial Average (30 stocks) since the data goes way back. In fact, 1896-present the long-term real price performance is more like 0.2% to 0.3%, not as exciting. If you start in 1931 instead (avoid the 1929 crash), you get very similar results to starting in 1945.

S&P 500 is my default current index, but price data on Yahoo for the S&P 500 only goes back to 1950. Since then, long-term real price performance (no dividends) is 2.1% to 2.5%. Yield for S&P since 1970 has ranged from just above zero to 3.1% or so, quick visual guess of 1% median). That's better price return than the Dow, mostly thanks to the S&P 500 including mid-cap stocks not just large cap. Total S&P real return of roughly 3% to 3.5% in line with Dow real return of roughly 4%...that mixes & matches some periods though.

Before CPI-U data starts, I used inflation estimates from westegg dot com/inflation/ . I inflation-adjusted index prices, and then looked at all x-year returns from 1 to 40 years and then the median of all data for any one length of returns (all 7 year periods, for example) to try to avoid a particular start date skewing the result. That's why I started with the Dow, as the S&P has just over 20 years of 40-year results (Dow goes way back, I started with 1896 annual data so 78 yrs of 40-year results). Got similar results whether looked at monthly or annual data (for a 10-year range that would be either 120 monthly data points or 10 annual data points). Yields I did NOT download, I'm trying to recover from my data addition I looked at yields online in graph format at wsj dot com (I think the graphing requires subscription - yahoo dot com doesn't seem to have yield data for indices? or look for a total return index - and wsj yield data seems to reach back different number of years depending whether you've chosen annual or quarterly data or something)

Teehee! This is like how the weatherman only gets the weather right sometimes... even with all the fancy equipment, predictions a week out are generally useless.

The formula is nice, but don't bet on it being the truth forever.

Ooh...now you've gotten me excited!

The trouble with just looking at the past data is that you are ignoring cause. Stock market performance is driven by corporate profits. Corporate profits in the aggregate track GDP reasonably well. Government spending directly produces about 1/3 of GDP if I recall correctly. And it indirectly produces considerably more since consumer spending accounts for about 2/3 and somewhere around 1/3 of Americans work for the government.

During much of your lookback period the USG has been spending in deficit and hence has been arbitrarily pushing the economy. That push has been financed by increasing national debt and willingness of foreign forces (notably Japan, now China) to buy debt and invest in this country. I think we have reached the point where these forces will wane. That means growth will probably slow and could even reverse. US demographics are also very troubling.

The government has gotten out of debt walls before using "tricks." After the deficit spending of the New Deal that got us out of the depression, the dollar was devalued by increasing the price of gold 75%. The in the 1970s when we were drowning in debt from paying for the Vietnam war and from being Europe's post WWII police force, we went off the gold standard. The result was an inflation spike and, effectively, another devaluation. Economists still argue about these things but that seems to be the practical outcome.

The problem is, we can't devalue now. The market might do it for us but that will be a gradual process - meaning that it will manifest as a slow decline in the dollar over a long period. It has already been happening over the last decade or so.

Now, I don't mean to sound all "doom and gloom." I don't think the world as a whole is in trouble. And US companies will still supply to much of the world's demand for many years (decades) to come. My point of all this is that the conditions that lead to the corporate earnings growth in the past are fairly well understood and are unlikely to continue. So simply looking at data misses that the underlying forces have changed.

For example, you can get your 3-4% real growth simply by adding population growth and productivity growth in the past. But population growth in the US has gone negative and productivity growth, while still positive, is probably slowing and, even if it is not, it is being achieved at the expense of jobs.

I really think that the last 10 years is far more indicative of future US performance than the previous 100. I also think that, while the US will be a safe and stable place to keep money, it will significantly underperform going forward. I think we will be lucky to get 0 real return! I do not expect inflation to pick up anytime soon so I think that real return will be a ~2.5% inflation environment so that it appears as a ~2.5% return comprised of ~1.5% dividends and ~1% price appreciation.

I also think it will rain sometime in the next decade.

It will definitely rain sometime!

Maybe we should ask some sheep about the market I think sometime in the 1980s a tv station in Oregon (Salem maybe??) compared the accuracy of the official weather forecast and whether one particular flock of sheep was up on the hill of their pasture or down in the valley near the sheep shed. Sheep were slightly more accurate than the forecast (but still nowhere near 100%).

In terms of forecasting, that is definitely tricky. Looking at the whole bell-curve of past performance gives a much better picture than picking one number for long term returns. But if you're going to pick a single number, I personally wouldn't use anything above 4% (including dividends).

Over any 20-year period historical period (first data 1950), S&P price performance (no dividends) has ranged from an annual real return of -3.3% to +10.1% (median 2.7%). Over any 30-year period the range has been +0.1% to +5.5% (median 2.0%).

Time for me to drop the data quest and go outdoors to enjoy our lovely weather today!

(note: if you look at long-term periods ending in the last 10 years, the results are actually pretty good thanks to the unusually strong market of the 1990s...even with the weak 2000's at the end.) If anyone's curious I could look back to find a similarly moribund period to the 2000's and then look at what happened after...

I joined this message board just to say thank you to the OP. His intentions were to show you how to figure out the numbers, and that is why I found this post on my google search. And he did exactly that. He didn't say or imply that you were going to get a 10% return. That is the example that he used.

The rest of the input in this tread is from a bunch of baboons.

Yes, but sometimes it's worth listening to baboons. In this case the baboons are warning that forecasts of the future value of your investment are likely to be inaccurate and you should revisit them every few years as key factors change. The baboons are not disputing the methodology of the calculation, they are adding value to the discussion by pointing out that if you perform this calculation just once at the inception of your Roth IRA without revising it periodically down the road, your final results are likely to be very different (even by 50 percent or more) from the forecast given by the calculations.

And what, pray tell, did you Google to find this particular post?

I Googled "future value of money in Excel" and this post wasn't in the top 20 hits, although there were plenty of links that would have provided the information the OP provided. I suspect that you are not what you seem. We baboons are bit smarter than you think.