Bank Rates Center
Insurance Rates Center
Mortgage Rates Center

Last visit was:
and exchange ideas
It is currently Fri May 24, 2013 8:35 am

 Page 1 of 1 [ 10 posts ]
 Print view Previous topic | Next topic
Author Message
 Post subject: Forecasting the Future Value of Your Roth-IRA or Roth-401(k)Posted: Wed Apr 11, 2012 4:34 am

Joined: Tue Apr 10, 2012 3:29 pm
Posts: 1
 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.
Top

 Post subject: Re: Forecasting the Future Value of Your Roth-IRA or Roth-40Posted: Wed Apr 11, 2012 6:39 am

Joined: Thu Apr 05, 2007 3:05 pm
Posts: 1184
 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.
Top

 Post subject: Re: Forecasting the Future Value of Your Roth-IRA or Roth-40Posted: Fri Apr 13, 2012 6:28 am

Joined: Mon Jan 02, 2012 3:21 pm
Posts: 81
 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
Top

 Post subject: Re: Forecasting the Future Value of Your Roth-IRA or Roth-40Posted: Fri Apr 13, 2012 12:46 pm

Joined: Thu Jun 23, 2011 3:24 pm
Posts: 88
Top

 Post subject: Re: Forecasting the Future Value of Your Roth-IRA or Roth-40Posted: Fri Apr 13, 2012 3:28 pm

Joined: Tue Sep 20, 2011 2:20 am
Posts: 196
 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.
Top

 Post subject: Re: Forecasting the Future Value of Your Roth-IRA or Roth-40Posted: Sat Apr 14, 2012 10:02 am
 Moderator

Joined: Wed Sep 23, 2009 9:01 am
Posts: 4505
Top

 Post subject: Re: Forecasting the Future Value of Your Roth-IRA or Roth-40Posted: Mon Apr 16, 2012 5:50 am

Joined: Thu Jun 23, 2011 3:24 pm
Posts: 88
 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...
Top

 Post subject: Re: Forecasting the Future Value of Your Roth-IRA or Roth-40Posted: Sun May 06, 2012 8:25 pm

Joined: Sun May 06, 2012 8:21 pm
Posts: 1
 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.
Top

 Post subject: Re: Forecasting the Future Value of Your Roth-IRA or Roth-40Posted: Mon May 07, 2012 5:29 am

Joined: Thu Apr 05, 2007 3:05 pm
Posts: 1184
 dig_scott wrote: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.
Top

 Post subject: Re: Forecasting the Future Value of Your Roth-IRA or Roth-40Posted: Mon May 07, 2012 6:31 pm

Joined: Fri May 04, 2007 8:14 pm
Posts: 983
 dig_scott wrote: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.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.
Top

 Display posts from previous: All posts1 day7 days2 weeks1 month3 months6 months1 year Sort by AuthorPost timeSubject AscendingDescending
 Page 1 of 1 [ 10 posts ] Moderators: bpgui, JerichoHill