So total 2011 = 1785
I also know that 2012 grew by 71% so there fore 2012 = 3052.35
Now, I'd like to extrapolate 2012 monthly data to get a steady growth for 2012 which will add up to 3052.35
So for Jan'12 it would be Dec'11*(1+X%) and then Feb'12 would be Jan'12*(1+X%)
My question is what is this X%? I've interpolated the 71% growth to monthly as 4.57% but if I use that as my X I get 2012 = 3101.563
You are misinterpreting the 2012 growth rate. Your mistake is highlighted in red above.
The 2012
total is 71% more than the 2011
total.
That does
not mean Jan'12 is 4.5722% more than Dec'11. For example, all of the 2011 value (1785) might have occurred in Jan'11; so Dec'11 would be zero.
If the 2012 total is 71% more than the 2011 total, that means the monthly amounts
in 2012 grew by about 4.5722% on average each month so that the total is 3052.35 = 1785*(1+71%).
Thus, Feb11 = Jan11*(1+4.5722%), and Mar11 = Feb11*(1+4.5722%). But the question remains: what is Jan11?
Think of each monthly amount as payments into an account. Starting with a zero balance, the balance at the end of Jan11 is the payment in Jan11 (TBD). The balance at the end of Feb11 is Jan11 + Jan11*(1+4.5722%). The balance at the end of Mar11 is Jan11 + Jan11*(1+4.5722%) + Jan11*(1+4.5722%)^2. And so on.
Mathematically, that summation series is the same as an account that we make fixed payments into (i.e. the Jan11 amount), and it earns 4.5722% interest.
So the Jan11 payment can be calculated by:
PMT((1+71%)^(1/12)-1,12,0,-1785*(1+71%))
where (1+71%)^(1/12)-1 is the average monthly growth rate, as you know.
That is about 196.56. To demonstrate, construct the following table. The formulas in column B are shown in column C.
[TABLE="class: grid, width: 365"]
<tbody>[TR]
[TD]
[/TD]
[TD="align: center"]
A
[/TD]
[TD="align: center"]
B
[/TD]
[TD="align: center"]
C
[/TD]
[/TR]
[TR]
[TD="align: right"]
1
[/TD]
[TD]
Annl_growth
[/TD]
[TD="align: right"]
71.0000%[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD="align: right"]
2
[/TD]
[TD]
Mnth_growth[/TD]
[TD="align: right"]
4.5722%[/TD]
[TD]
=(1+B1)^(1/12)-1[/TD]
[/TR]
[TR]
[TD="align: right"]
3
[/TD]
[TD]
Total'11[/TD]
[TD="align: right"]
1785.00[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD="align: right"]
4
[/TD]
[TD]
Jan'12[/TD]
[TD="align: right"]
196.56[/TD]
[TD]
=PMT(B2,12,0,-B3*(1+B1))[/TD]
[/TR]
[TR]
[TD="align: right"]
5
[/TD]
[TD]
Feb'12[/TD]
[TD="align: right"]
205.55[/TD]
[TD]
=B4*(1+$B$2)[/TD]
[/TR]
[TR]
[TD="align: right"]
6
[/TD]
[TD]
Mar'12[/TD]
[TD="align: right"]
214.95[/TD]
[TD]
copy B4 down through B15
[/TD]
[/TR]
[TR]
[TD="align: right"]
7
[/TD]
[TD]
Apr'12[/TD]
[TD="align: right"]
224.78[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD="align: right"]
8
[/TD]
[TD]
May'12[/TD]
[TD="align: right"]
235.05[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD="align: right"]
9
[/TD]
[TD]
Jun'12[/TD]
[TD="align: right"]
245.80[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD="align: right"]
10
[/TD]
[TD]
Jul'12[/TD]
[TD="align: right"]
257.04[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD="align: right"]
11
[/TD]
[TD]
Aug'12[/TD]
[TD="align: right"]
268.79[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD="align: right"]
12
[/TD]
[TD]
Sep'12[/TD]
[TD="align: right"]
281.08[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD="align: right"]
13
[/TD]
[TD]
Oct'12[/TD]
[TD="align: right"]
293.93[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD="align: right"]
14
[/TD]
[TD]
Nov'12[/TD]
[TD="align: right"]
307.37
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD="align: right"]
15
[/TD]
[TD]
Dec'12[/TD]
[TD="align: right"]
321.43[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD="align: right"]
16
[/TD]
[TD]
Total'12[/TD]
[TD="align: right"]
3052.35[/TD]
[TD]
=SUM(B4:B15)[/TD]
[/TR]
[TR]
[TD="align: right"]
17
[/TD]
[TD]
[/TD]
[TD="align: right"]
3052.33[/TD]
[TD]
=SUMPRODUCT(ROUND(B4:B15,2))[/TD]
[/TR]
</tbody>[/TABLE]
The
actual values in B5:B15 are not rounded. However, the
displayed values are rounded to 2 decimal places. B16 is the sum of the
actual values. B17 is the sum of the
displayed values.
