Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    May 2013
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Need formula help to calculate annualized compounding percentage

    Ugh, I am sure this is Excel 101 for all of you ~ but I am stumped. I can do this manually with a lot more work, but I know there is a fancy formula out there that will slice through this like butter!!

    I have several years to calculate, each reflects the % earned during each month... I need to compute the effective overall annualized percentage for the investment.

    ie:

    YEAR Jan Feb Mar Apr May June July Aug Sept Oct Nov Dec
    2012 2.93% 2.31% -0.33% -0.15% -1.61% 0.74% 1.43% 1.27% 1.38% 0.46% -0.34% 1.58%


    Now I know the answer for this one is 10.02% but I cannot make the formula come up with that answer!!!! Honestly I usually just get an error or "trouble with this formula"

    Please help ~ I am NOT the pro!!!!

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    I presume you start with some value x and after january will have x*(1.0293) and after Feb x*(1.0293)*(1.0231) etc. Then the Overal percent increase is the final /x -1.

    So you want the product of (1+each month) - 1. If teh Percentages are in B2:M2, you can do with the array formula (confirm with ctrl-shift-enter):
    =PRODUCT(1+B2:M2)-1
    Note I get 10.014% not 10.02%, so I presume that the values you list may have some rounding...

    Steve

  3. #3
    New Lounger
    Join Date
    May 2013
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    You are AMAZING!!!!!! Thanks SO much!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •