MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Monthly / Annual growth


Posted by IML on January 15, 2002 10:53 AM

Lets say you have an annual growth rate in cell A1 of 5%
I want to end up with the value of $100 to be $105 in 12 months, but show it by month.
A4:L4 are names of the month
A5 is a value $100
B5:L5 are a formula to show the monthly growth
I tried
=A5*(1+$A$1/12) in B5 and copied it over but got a december value of $104.68, not $105.

dividing by 11 get 105.12

I think I needed to pay attention to exponents in math class for some reason, but can't remeber.

Here are the results with the the formula shown
{37257,37288,37316,37347,37377,37408,37438,37469,37500,37530,37561,37591;100,100.416666666667,100.835069444444,101.25521556713,101.677112298659,102.100766933237,102.526186795459,102.95337924044,103.382351653942,103.8131114525,104.245666083552,104.680023025567}


Thanks in advance.
Ian


Posted by Barrie Davidson on January 15, 2002 11:06 AM

Ian, change your formula to read:

=A5*(1+$A$1)^(1/12)

Regards,
BarrieBarrie Davidson

Posted by IML on January 15, 2002 11:14 AM

Perfect, almost..

ERR