Calculate Average Annual Compounding Rate of Increase

  • Thread starter Thread starter dapel
  • Start date Start date
D

dapel

I've been playing around with various functions, but can't seem to get
what I need. I'm working with data that shows the annual change in
average total health benefit cost over a series of years. I've been
asked to equate that to an average annual compounding rate of
increase. My source data is from a survey that shows the following:
Column A is the year, Column B is the increase to health cost
1989 16.7%
1990 17.1%
1991 12.1%
1992 10.1%
1993 8.0%
1994 -1.1%
1995 2.1%
1996 2.5%
1997 0.2%

So, health care costs in 1989 were up 16.7% from the previous year,
and so on.
I'm in need of a formula or function that will take this data and calc
that it is equivalent to a compounding 9.5% (or whatever it happens to
be) per year.

I then do the same thing based on the actual results of the client,
for comparison's sake.

Any and all help would be greatly appreciated!
 
dapel

If you set the first year value to 1988 and give it an Index value of 100
and multiply this by the percenage increase to 1997 you get an index value of
188.92.

You can use goal seek to find the average percentage needed to increase 100
to 188.92 in 10 periods.

In F3 I enter =B3*(1+G3)^10 where B3 = 100 in G3 try a value of .05

In Tools, choose Goal Seek. Cell to change = F3, To Value is 188.92, By
Changing cell G3

This returns 0.0656823946616974

You might want to choose 9 payments rather than 10, I'm not sure about that.

To check the formula use =100*(1+G3)^10 which will return 188.92

Hope that this helps

Peter Atherton
 
dapel

If you set the first year value to 1988 and give it an Index value of 100
and multiply this by the percenage increase to 1997 you get an index value of
 188.92.

You can use goal seek to find the average percentage needed to increase 100
to 188.92 in 10 periods.

In F3 I enter =B3*(1+G3)^10 where B3 = 100 in G3 try a value of .05

In Tools, choose Goal Seek. Cell to change = F3, To Value is 188.92, By
Changing cell G3

This returns 0.0656823946616974

You might want to choose 9 payments rather than 10, I'm not sure about that.

To check the formula use =100*(1+G3)^10 which will return 188.92

Hope that this helps

Peter Atherton
Click to expand...

Thanks for the input. It will do the trick for now, though I have some
situations coming up with partial years. I was trying to use XIRR,
but it just wouldn't catch.
Thanks again.
 
Of course I only had the interest rates. Dividing the last value by the first
will give you the ratio for the period.

With say six months the situation is the same, final value divided by the
first to give the ration and double the number of periods.

Peter

:
 
Here's a direct calculation that doesn't use goal seeking:
Say the values begin in row2.
in C2, =1+B2
in C3, =C2*(1+c3) and copy down
in col Dx, where x is last row of data, =Log10(Cx)/n
where n is number of years.
Then avg. rate is =(10^Dx)-1
You can combine the last two formulas:
avg. rate =(10^(Log10(Cx)/n))-1

n can be fractional, but for the fractional years the increase must be FOR
THE PERIOD, not an annual rate. I.e. if a period is .25 years, then the
increase must be for the three months since the last period.
 
Back
Top