Hi,

Myself and a colleague have been trying to work out how to calculate
the number of years it will take to achieve a target value (which
grows at a seperate slower rate) given a pre-defined start value and
fixed growth using a single formula and just did it...and really felt
the need to tell someone!!

Start Value B4 = 14216
Growth Rate B5 = 1.07
Target Value B6 = 17750
Growth Rate B7 = 1.02

Duration: =LOG(((B6*B7^B10)/B4),B5)

Am sure there are better ways and any high school student could have
done this but we're old so we feel dead chuffed!! Anyway, glad to get
that out of the system :-)

Best Regards,

CalumMurdo Kennedy

It is hard to check if your formula returns a correct value or not because
it uses the cell B10 which you didn't define for us. With that said, the
formula I derived is different than yours (and requires only the cells you
defined for us). I get this formula...

=LOG(B6/B4)/LOG(B5/B7)

Here is how I developed it (X is the unknown time period we are looking
for)...

B4 * (B5 ^ X) = B6 * (B7 ^ X)
Log(B4 * (B5 ^ X)) = Log(B6 * (B7 ^ X))
Log(B4) + Log(B5 ^ X) = Log(B6) + Log(B7 ^ X)
Log(B4) + (X * Log(B5)) = Log(B6) + (X * Log(B7))
(X * Log(B5)) - (X * Log(B7)) = Log(B6) - Log(B4)
X * (Log(B5) - Log(B7)) = Log(B6) - Log(B4)
X * Log(B5 / B7) = Log(B6 / B4)
X = Log(B6 / B4) / Log(B5 / B7)

--
Rick (MVP - Excel)


wrote in message
...
Hi,

Myself and a colleague have been trying to work out how to calculate
the number of years it will take to achieve a target value (which
grows at a seperate slower rate) given a pre-defined start value and
fixed growth using a single formula and just did it...and really felt
the need to tell someone!!

Start Value B4 = 14216
Growth Rate B5 = 1.07
Target Value B6 = 17750
Growth Rate B7 = 1.02

Duration: =LOG(((B6*B7^B10)/B4),B5)

Am sure there are better ways and any high school student could have
done this but we're old so we feel dead chuffed!! Anyway, glad to get
that out of the system :-)

Best Regards,

CalumMurdo Kennedy

Hi. Your description sounds like your equation is:

start*(1 + r1)^y = target*(1 + r2)^y

and you want to solve for y (ie years)

My guess would be:

Log(start/target)/(Log(r2) - Log(r1))

where r1 is your rate 1.07, ect.

Again, just a guess.
= = = = = =
Dana DeLouis



wrote:
Hi,

Myself and a colleague have been trying to work out how to calculate
the number of years it will take to achieve a target value (which
grows at a seperate slower rate) given a pre-defined start value and
fixed growth using a single formula and just did it...and really felt
the need to tell someone!!

Start Value B4 = 14216
Growth Rate B5 = 1.07
Target Value B6 = 17750
Growth Rate B7 = 1.02

Duration: =LOG(((B6*B7^B10)/B4),B5)

Am sure there are better ways and any high school student could have
done this but we're old so we feel dead chuffed!! Anyway, glad to get
that out of the system :-)

Best Regards,

CalumMurdo Kennedy