Forecasting the Future Value of Your Roth-IRA or Roth-401(k)

[samozain]

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.

[dczech09]

I have a Roth IRA with currently approximately $7,500 in it.

I will turn 25 on May 5th. So let's say I have 40 years until retirement.

I am invested in some good growth mutual funds. Let say I make 9% per year (I have been averaging more than that lately).

I max out my contributions at $416.66 per month...

PV= $7,500
PMT= $416.66
N= 480 (40 years X 12 months)
I= 0.75 (9/12)

FV= $2,221,343

I think I am on track To quote Dave Ramsey- "what if I am half wrong?!"

[kv968]

I have a Roth IRA with currently approximately $7,500 in it.

I will turn 25 on May 5th. So let's say I have 40 years until retirement.

I am invested in some good growth mutual funds. Let say I make 9% per year (I have been averaging more than that lately).

I max out my contributions at $416.66 per month...

PV= $7,500
PMT= $416.66
N= 480 (40 years X 12 months)
I= 0.75 (9/12)

FV= $2,221,343

I think I am on track To quote Dave Ramsey- "what if I am half wrong?!"

And just to keep you in check if you're feeling TOO good about it...

Discount that $2.2 Mil by a modest 2% inflation rate and in today's dollars that $2.2 would be $1,006,024

Or if you knocked 2% off the annual return for inflation then you'd be looking at $1,215,990 in 40 years.

Still not a bad chunk of change either way but it's always scary when you discount stuff back to today's dollars and add inflation into the mix.

[jpg7n16]

I will turn 25 on May 5th. So let's say I have 40 years until retirement.

FV= $2,221,343

I think I am on track To quote Dave Ramsey- "what if I am half wrong?!"

Agreed with kv, don't wanna burst your excitement too much, but still. Inflation has averaged long term 3.4%. So did some calcs at 3.4% inflation and here's what I found.

At a 4% withdrawal rate, that sum will give you $88,854/year in income. Sounds good.

At 3.4% inflation to give you an idea of what that will feel like to live on, that's like $23,327year in today's dollars. Will that give you the type of retirement you want?

And Dave's tongue in cheek question is a bit more real if you seriously think, what if you are half wrong? That's income of $11,663/year in today's dollars. What would retirement be like then?

I know -- we're just killjoys huh?


But realistically, you'll end up putting more away, cause you'll get raises over time. And you'll likely be using a 401k too, so you'll probably have more than $2.2m.

[kv968]

Agreed with kv, don't wanna burst your excitement too much, but still. Inflation has averaged long term 3.4%. So did some calcs at 3.4% inflation and here's what I found.

At a 4% withdrawal rate, that sum will give you $88,854/year in income. Sounds good.

At 3.4% inflation to give you an idea of what that will feel like to live on, that's like $23,327year in today's dollars. Will that give you the type of retirement you want?

And Dave's tongue in cheek question is a bit more real if you seriously think, what if you are half wrong? That's income of $11,663/year in today's dollars. What would retirement be like then?

I know -- we're just killjoys huh?

Now why did you have to go and use such things as REAL numbers? My measly 2% did enough damage even if it wasn't the true number

[jpg7n16]

Now why did you have to go and use such things as REAL numbers? My measly 2% did enough damage even if it wasn't the true number

Haha - umm... Cause a million dollars still sounds cool?

Sorry! haha

[KTP]

This whole thread points out the huge problem we have right now with bonds paying below the inflation rate...even worse if you are in a taxable account, then the bonds pay well below inflation after taxes are counted.

Something has to give soon...either we get deflation or bond prices need to go WAY up. Or most of us need to develop a taste for cat food.

[dczech09]

This whole thread points out the huge problem we have right now with bonds paying below the inflation rate...even worse if you are in a taxable account, then the bonds pay well below inflation after taxes are counted.

Something has to give soon...either we get deflation or bond prices need to go WAY up. Or most of us need to develop a taste for cat food.

Yes, good point. With bond rates so low, prices are at highs. This is definitely a seller's market. And it cannot hold forever. I think deflation is a real concern.

And just think... a few years ago, I thought hyperinflation would be a concern

[woodie96]

Man, thats what I get for starting soo late.....14 years away from being 62.....so if I assume 8%, put in 850 a month, along with the 6oK that is there now....will have whopping 400K plus.....
That might do it since I already get a 2500 month retirement check from the military/VA for disability.
Have to do some thinking and pondering.

[kv968]

Man, thats what I get for starting soo late.....14 years away from being 62.....so if I assume 8%, put in 850 a month, along with the 6oK that is there now....will have whopping 400K plus.....
That might do it since I already get a 2500 month retirement check from the military/VA for disability.
Have to do some thinking and pondering.

We could make you feel really good and discount that $400k+ to what it would be equivalent to in today's dollars using jpg's 3.4% annual inflation rate

[jpg7n16]

Man, thats what I get for starting soo late.....14 years away from being 62.....so if I assume 8%, put in 850 a month, along with the 6oK that is there now....will have whopping 400K plus.....
That might do it since I already get a 2500 month retirement check from the military/VA for disability.
Have to do some thinking and pondering.

Yup $445k. Which you could withdraw about $18k/year to be at the 4% rule ($1483/month). Are you investing aggressively enough to earn 8%?

$850/month is $10,200/year. What percent of your income is that?

You guys ever wonder why I always post "strive for 15-20% over time"?? Threads like this make me seem less crazy. I'm gonna convince people to invest 15-20% one way or another

[dczech09]

You guys ever wonder why I always post "strive for 15-20% over time"?? Threads like this make me seem less crazy. I'm gonna convince people to invest 15-20% one way or another

Well you are a financial advisor. Its your job to persuade people to invest more

[kv968]

Yup $445k. Which you could withdraw about $18k/year to be at the 4% rule ($1483/month). Are you investing aggressively enough to earn 8%?

$850/month is $10,200/year. What percent of your income is that?

You guys ever wonder why I always post "strive for 15-20% over time"?? Threads like this make me seem less crazy. I'm gonna convince people to invest 15-20% one way or another

If you really want to hit home on the saving more topic, let me just do the same numbers with my conservative 2% inflation rate factored in. That would be:

$361,650 total
$14,466/year
$1206/month (4% withdrawl rate)

Not bad but that's also considering the ability to make 8%/year and inflation staying at 2%.

Actually, the other thing to remember is that these numbers are using a constant MONTHLY compounding of the 8% expected return (which is highly unlikely). If you use an ANNUAL compounding of the 8% (more realistic) you get:

$196,814 total
$7873/year
$656/month (4% rate)

I'm not even going to do those numbers figuring in inflation

[woodie96]

LOL....I'm such a dummy......it sure makes a difference when you put the % sign in the formula.....I was happy that I was going to be a multi-gazillionaire.....
Til I figured out my error.....OOPSIE

[woodie96]

JPG....that is 10% of my total, including the military retirement stipend....I will be bumping it up again when I get a raise..hopefully later this month...
If I don't see it I won't miss it right?

As for being aggressive...JimOhio posted a link a while back (months if not years ago) where you could type in your profile of mutual funds and it would give you a % of what you were actually investing in as far as stocks/bonds/cash etc. When I ran that formula back then, I was in about 98% stocks. Probably 20+ percent of that international.

I tried to look in the archives but couldnt find the link...bummer

KV....It already bad enough....I think I'll pass on the refiguring..