401K compound interest calculation

Discussion in 'Financial Planning' started by Steve.S.Walker@gmail.com, Jan 6, 2007.

  1. Guest

    Hi...

    Can someone recommend a site, or an excel download that will help me
    estimate my 401K growth over time, including an employer match? I
    found quite a few, but the problem is they only allow a 100% employee
    match, and my employer matches 8% for my 2% of my salary (they're very
    generous).. I'd like a calculator that figures in my annual raises,
    plus my annual rate of return, etc. Bloomberg has a good one, but it
    limits the employer match to 100%... Also, if I have to do a formula
    myself, do I perform the compound interest calculation monthly?
    quarterly? Any ideas?

    Thanks.
     
    , Jan 6, 2007
    #1
    1. Advertisements

  2. joetaxpayer Guest

    wrote:

    > Hi...
    >
    > Can someone recommend a site, or an excel download that will help me
    > estimate my 401K growth over time, including an employer match? I
    > found quite a few, but the problem is they only allow a 100% employee
    > match, and my employer matches 8% for my 2% of my salary (they're very
    > generous).. I'd like a calculator that figures in my annual raises,
    > plus my annual rate of return, etc. Bloomberg has a good one, but it
    > limits the employer match to 100%... Also, if I have to do a formula
    > myself, do I perform the compound interest calculation monthly?
    > quarterly? Any ideas?
    >
    > Thanks.


    I sent Steve a copy of the spreadsheet I have on my personal site. FWIW,
    it allows entry for total percent going in, i.e. the sum of the employee
    deposit and employer match expressed as one number, for Steve, 10%.
    I don't compound monthly, we always talk here in terms of annual rate of
    return, and a 40 or so line sheet is easier to glance at in terms of
    changes to salary or impact due to a raise of the percent saved.
    Calculators that skip the 40 years' numbers and only show an end result
    are useless to my way of thinking, it's better to see the amount saved
    each year to tell if you're on track. For example - Given the numbers
    15% total saving, 3% annual raise, 8% return, one has 20X after about 42
    years of work, but only 5X after 21. The full sheet shows the power of
    compounding in the last decade. Happy to forward or post a downloadable
    copy.

    JOE
     
    joetaxpayer, Jan 6, 2007
    #2
    1. Advertisements

  3. Mike Guest

    Mike, Jan 8, 2007
    #3
  4. samsai Guest

    Joe, could you send me the spreadsheet... I was able to locate your
    site but the online version doesnt open in my browser...

    Thanks. I have a side question on 401K calculation:

    Sample year end report from fidelity 401k:
    Beginning Balance $22,448.69
    Your Contributions $14,093.64
    Employer Contributions $3,077.81
    Change In Market Value $4,219.20
    Ending Balance $43,839.34
    Additional Information
    Dividends & Interest $3,099.13
    Your Personal Rate of Return (for the year) 13.5%

    What I dont understand here is how did they arrive at this rate of
    return? Also it seems that "Change In Market Value" include the
    "Dividends & Interest" value?

    My confusion arises from the fact that my personal rate of return comes
    as 22% when I include last years figures. And I cant believe that rate
    of return on my investments!!

    Please help me understand fidelity's "Rate of Return" or let me know
    good way to calculate that on yearly basis.

    Thanks.
    Samsai

    On Jan 6, 9:43 am, joetaxpayer <> wrote:
    > wrote:
    > > Thanks.I sent Steve a copy of the spreadsheet I have on my personal site. FWIW,

    > it allows entry for total percent going in, i.e. the sum of the employee
    > deposit and employer match expressed as one number, for Steve, 10%.
    > I don't compound monthly, we always talk here in terms of annual rate of
    > return, and a 40 or so line sheet is easier to glance at in terms of
    > changes to salary or impact due to a raise of the percent saved.
    > Calculators that skip the 40 years' numbers and only show an end result
    > are useless to my way of thinking, it's better to see the amount saved
    > each year to tell if you're on track. For example - Given the numbers
    > 15% total saving, 3% annual raise, 8% return, one has 20X after about 42
    > years of work, but only 5X after 21. The full sheet shows the power of
    > compounding in the last decade. Happy to forward or post a downloadable
    > copy.
    >
    > JOE
     
    samsai, Jan 27, 2007
    #4
  5. "samsai" <> writes:

    > Sample year end report from fidelity 401k:
    > Beginning Balance $22,448.69
    > Your Contributions $14,093.64
    > Employer Contributions $3,077.81
    > Change In Market Value $4,219.20
    > Ending Balance $43,839.34
    > Additional Information
    > Dividends & Interest $3,099.13
    > Your Personal Rate of Return (for the year) 13.5%


    Making the simplifying assumption that your and your
    employer's contributions were evenly spaced through
    the year, your average invested amount for the year
    was:
    $31,034.42 = $22,448.69 + 0.5*($14,093.64 + $3,077.81).
    Your investment gain for the year was:
    $4,219.20 = $43,839.34 - $22,448.69 - ($14,093.64 + $3,077.81)

    The rate of return on the average amount invested was
    13.6% = $4,219.20/$31,034.42

    This is just a simplified approximation of the real
    calculation to sanity-check Fidelity's calculation.

    To do the real calculation one would need to know the
    exact dates and amounts of your contributions and your
    employer's contributions. With those in hand, one would
    then run the Internal Rate of Return algorithm on those
    cash flows (if you have Excel, you can load in its
    analysis toolpack and then you'll have access to the
    IRR() and XIRR() functions and can do the calculation
    yourself).

    --
    Rich Carreiro
     
    Rich Carreiro, Jan 27, 2007
    #5
  6. samsai Guest

    Thanks Rich and Joe... This was perfect explanation...

    I will try to remember that this is continuous contributions type
    investment and hence the calculation is little different/
    complicated...

    I have heard that people try to achieve on a yearly basis 8% or more
    returns on 401K... they say if you maintain this rate of return over
    time, then you are on right path... since I started my 401K few years
    back, the market has been UP in general...

    is 8% target achievable in bad years? I know it depends on the market,
    but experienced people can shed light on historic returns or share
    their experiences.

    Thanks in advance.
    - Samsai

    On Jan 27, 7:44 am, Rich Carreiro <>
    wrote:
    > "samsai" <> writes:
    > > Sample year end report from fidelity 401k:
    > > Beginning Balance $22,448.69
    > > Your Contributions $14,093.64
    > > Employer Contributions $3,077.81
    > > Change In Market Value $4,219.20
    > > Ending Balance $43,839.34
    > > Additional Information
    > > Dividends & Interest $3,099.13
    > > Your Personal Rate of Return (for the year) 13.5%Making the simplifying assumption that your and your

    > employer's contributions were evenly spaced through
    > the year, your average invested amount for the year
    > was:
    > $31,034.42 = $22,448.69 + 0.5*($14,093.64 + $3,077.81).
    > Your investment gain for the year was:
    > $4,219.20 = $43,839.34 - $22,448.69 - ($14,093.64 + $3,077.81)
    >
    > The rate of return on the average amount invested was
    > 13.6% = $4,219.20/$31,034.42
    >
    > This is just a simplified approximation of the real
    > calculation to sanity-check Fidelity's calculation.
    >
    > To do the real calculation one would need to know the
    > exact dates and amounts of your contributions and your
    > employer's contributions. With those in hand, one would
    > then run the Internal Rate of Return algorithm on those
    > cash flows (if you have Excel, you can load in its
    > analysis toolpack and then you'll have access to the
    > IRR() and XIRR() functions and can do the calculation
    > yourself).
    >
    > --
    > Rich Carreiro



    ======================================= MODERATOR'S COMMENT:
    Please trim the post to which you are responding. "Trim" means that except for a FEW lines to add context, the previous post is deleted.
     
    samsai, Jan 28, 2007
    #6
  7. joetaxpayer Guest

    samsai wrote:
    > I have heard that people try to achieve on a yearly basis 8% or more
    > returns on 401K... they say if you maintain this rate of return over
    > time, then you are on right path... since I started my 401K few years
    > back, the market has been UP in general...
    >
    > is 8% target achievable in bad years? I know it depends on the market,
    > but experienced people can shed light on historic returns or share
    > their experiences.
    >
    > Thanks in advance.
    > - Samsai


    The simple answer is 'no'. This is a variant on the Risk/Return
    question. If you got 8% in a bad year, well, it wouldn't be bad, right?
    The 8% goal is achievable over time, I would think. The average return
    for the S&P has been over 11% for the past 40 years, and there's enough
    argument in favor of lower returns for the next 10-20. The 11.4% to be
    precise brought with it a 16.3% standard deviation. This simply means
    that about 2/3 the time the return was 11.4% +/- 16.3% and that 1/6 of
    the time, worse than -4.9% and 1/6 the time it was greater than 27.7%.

    By diversifying, adding small cap, foreign funds, and bonds (this is not
    a complete list) you improve the return a bit, but more importantly,
    reduce the standard deviation. Modern Portfolio theory and specifically,
    the Efficient Frontier address this better than I can in one paragraph.
    http://en.wikipedia.org/wiki/Efficient_frontier is a decent overview of
    this.
    I can also refer you to
    http://www.moneychimp.com/articles/randomness/time_horizon.htm
    which I recommend for two reasons. You can see how the market performed
    in any year you choose, and how your risk is lessened as your time
    horizon is lengthened. If you enter a decade at a time, e.g.. 1980-89,
    you'll see how the 90's (and 80's for that matter) were an anomaly, as
    one would expect to have about one down year in three, or 3 per decade.
    Don't plan your future based on the 90's (I've give my right pinkie to
    have a decade like that again), but hopefully, the 70's are too
    pessimistic. I can suggest that you keep reading, I have a reading list
    on my web site, and continue to add to it as good books come to my
    attention. All books on the list are books I've read. Elle has good
    links to sites about diversifying, also worth a look:
    http://home.earthlink.net/~elle_navorski/index.html

    JOE
    JoeTaxpayer.com
     
    joetaxpayer, Jan 28, 2007
    #7
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. hierba de gatos

    Compound Interest charge

    hierba de gatos, Apr 29, 2008, in forum: Accounting
    Replies:
    0
    Views:
    353
    hierba de gatos
    Apr 29, 2008
  2. nish
    Replies:
    4
    Views:
    473
  3. nish
    Replies:
    8
    Views:
    670
  4. Isaac

    Compound Interest Investments in Money

    Isaac, Oct 29, 2003, in forum: Microsoft Money
    Replies:
    0
    Views:
    235
    Isaac
    Oct 29, 2003
  5. P Hargreaves

    Compound interest

    P Hargreaves, Apr 5, 2006, in forum: Microsoft Money
    Replies:
    4
    Views:
    294
    P Hargreaves
    Apr 8, 2006
Loading...

Share This Page