# 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 = 41528Totals of values from month 13 to 24 needs to = 98087Totals of values from month 25 to 36 needs to = 176970need to populate all cells with appropriate valuesthe 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

#1
July 1, 2015 at 06:09:24
 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. ;-)message edited by DerbyDad03