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

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 12C6, 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

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

Ask Your Question

Weekly Poll

Do you think Microsoft can save the Surface Book lineup?

Discuss in The Lounge

Poll History