Solved growth rate formulas, with a twist

June 30, 2015 at 21:14:00
Specs: Windows 7
Hi,

So here's a tricky one. I tried using CAGR but it doesnt seem to work for me... maybe my logic is wrong. Here is what I want to do:

1 have 37 columns, months 0 to 36. month 0 has a number = 220

Totals of values from month 1 to 12 needs to = 41528

Totals of values from month 13 to 24 needs to = 98087

Totals of values from month 25 to 36 needs to = 176970

need to populate all cells with appropriate values

the catch is that the value of month 1 cannot be less than month 0, the value of month 13 cannot be less than month 12, the value of month 25 cannot be less than month 24.

is there any simple way to do this?

thanks guys, it will be greatly appreciated.

bijal


See More: growth rate formulas, with a twist

Report •

#1
July 1, 2015 at 06:09:24
✔ Best Answer
Unless I'm missing something in your request, it seems to me that you need to use a different growth rate for each period. I can't see how there could be a single growth rate that gets you to your required values at the end of each 12 month period.

I started with this table:

     A      B           C           D             E             F           G ....
1    0      1           2           3             4             5           6 ....
2   220
3 
4                          =((fv/pv)^(1/n))-1
5         0 - 12        Rate       13 - 24      Rate         25 - 36        Rate
6   fv    41528     0.547604063     98087    0.074251255      176970     0.050406378
7   pv      220                     41528                      98087	
8   n        12                        12                         12	

C6, E6 and G6 contain the standard CAGR formula shown in D4, referencing the values in columns B, D and F, respectively.

I then used this formula in B2 and dragged it to M2 to get 41528.

=A2*(1+$C$6)

I then used this formula in N2 and dragged it to Y2 to get 98087

=M2*(1+$E$6)

I'll leave it up to you to figure out what formula I used in Z2. ;-)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#2
July 1, 2015 at 19:11:14
Awesome... I figured they would need different CAGR rates. my problem was and still is that the amount that populates the 13 column (month 12) is the minimum starting amount for the 14th (month 13).

thanks for your help, it cleared up a lot of confusion

I did get this one... Z2 =Y2*(1+$G$6)

cheers


Report •
Related Solutions


Ask Question