Thread: Need formula help to calculate annualized compounding percentage

1. 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"

2. 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. 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
•