MegaSack DRAW - This year's winner is user - rgwb
We will be in touch
Morning, I've got a whole load of prices I need to compare one for 2012 pricing and one for 2018 pricing. I have calculated the overall rise in price for instance 25% over the 6 years.
What I really want to do is understand what this 25% rise would equate to per year, assuming that the yearly rises are compounded. I can do it by trial and error but this isn't practical for over 200 lines! Is there an excel formula I could use to work this out for me?
Thanks
Try
=(2018/2012)^(1/6)-1
For 25% using example amounts this gives
(125/100)^(1/6)-1 = 3.79%
And to check it works
100 x 1.0379 x 1.0379 x 1.0379 x 1.0379 x 1.0379 x 1.0379 = 125
Just replace 2018 and 2012 in the first formula with references to the relevant prices in each row.
MattP has it - showing the working where r is the annual interest rate
P(2018) = P(2012)*(1+r)*(1+r)*(1+r)*(1+r)*(1+r)*(1+r)
= P(2102) * (1+r)^6
P(2018)/P(2012)=(1+r)^6
(P(2018)/P(2012))^(1/6)=1+r
r = (P(2018)/P(2012))^(1/6) - 1
Cheers all, much appreciated