Quarterly Growth Compounding equating to the annual rate?

[RSidhu]

Hi I am stuck with this. I want to show a 5% growth for income for the year but i want to show the quarterly income as well. See my example. If I calculate the growth rate quarterly, the calculated annual growth rate is 5.1% and not 5%. Why is my formula wrong?

See attached excel. Thanks!

 

[vletm]

@RSidhu
100 + 5%/12 + 5%/12 + 5%/12 + 5%/12 is more than 100 +5%
You should use ~4,9088937716% per quarterly to get annual 5% growth rate.
Check file ...

 

[RSidhu]

That is not correct vletm. My annual rate is 5%. I cannot use 4.908%. I think it has got to do with the formula. Thanks for trying.

@RSidhu
100 + 5%/12 + 5%/12 + 5%/12 + 5%/12 is more than 100 +5%
You should use ~4,9088937716% per quarterly to get annual 5% growth rate.
Check file ...

 

[Chihiro]

The thing is you are compounding the growth.

It's quarterly compounded growth with Year End value of 5% increase over original value.

You will need to adopt Compound Annual Growth Rate (CAGR) calculation and adjust for Quarterly growth.

CAGR = (Ending Value/Beginning Value)^(1/# of Years)-1

In your case think of each quarter as single year... so
CAGR = (105/100)^(1/4)-1 = 0.01227...

So quarterly growth rate of approx. 1.227% will result in Year End growth of 5% over original value.

See attached.

 

[RSidhu]

Thanks a lot Chihiro! That works! however, i have to use this formula over several quarters in the future. the beginning value will change every year. the new beginning value in Q5 will be based on Q4 (105) and not 100. How do I adjust the formula based on that?

 

[Chihiro]

Just change B8 to 105. And it will automatically adjust the calculation.

If B8 = 105, then Year End = 110.25, 5% growth over 105.

Edit: Alternately, you can just copy F8 and paste across for following quarters.

 

[RSidhu]

thank you Chihiro! you are the best!

 

[vletm]

@RSidhu
How much is 4,9088937716%/4?
You could use it.
It's good that You got formula too.