Percent increase spread out over 12 months

[erenkey]

I need help with a formula that will help spread a percent increase over a 12 month period. Basically I am working on a budget template where I want to increase a number by an annual percentage but spread that increase over the entire 12 month period. Example:

I have a value of 100 and want to increase it by 30% over the next 12 months. I tried using =100*(1+(30%/12)) to get the number for the first month and then replace the 100 in the formula with the previous month number and drag it out 12 months but when I do that I get an increase of 34.5%. I want my total increase to only be 30%. Is there a way to do that with a formula?

 

[gaz_chops]

Maybe

=(100*1.3)/12

 

[joeu2004]

It sounds to me like you want to compound the monthly increase so that you have an annual increase of 30%. With 100 in A1, enter the following in A2 and copy down through A13:

=A1*(1+30%)^(1/12)

Note that as you copy down, A1 changes to A2, A3, etc. The result in A13 will be 130, 30% more than 100 (A1).

It is important not to explicitly round the intermediate results. But of course, you might format the cell to display only 2 decimal places.