# Adjusting a value for inflation each month using rolling 12-monthly inflation figures

I have a figure that I want to adjust for inflation each month. I have monthly values for the UK Consumer Price Index but these are each for the change over the preceding 12 months.

If my base value is £100 in December 2012, how would I adjust this (in Excel/Numbers), both forward and backward each month? Is it possible with these 12-monthly figures?

``````         MONTH    CPI    VALUE
--------------------------------
August 2012   2.5%
September 2012   2.2%
October 2012   2.7%
November 2012   2.7%
December 2012   2.7%    £100.00
January 2013   2.7%
February 2013   2.8%
March 2013   2.8%
April 2013   2.4%
``````

In the style of the Bank of England's Inflation Calculator, you can do the calculation like so.

The third column is an index made from the inflation figures and the forth column shows the inflation-adjusted values.

Using the index to calculate the difference in costs, for example:

``````Cost in April = Cost in December x ( April price index / December price index )

111.136478 = 100 x ( 1.2610802 / 1.1347132 )
``````

The formulas used to produce the table above are shown below.

• Aren't the BOE figures annualized percentages? So, for example, your `D3` element should read `=D2*(1+C3/12)` to use the monthly rate? Sep 29, 2015 at 15:18
• Well spotted: "CPI All Items: Percentage change over 12 months". However I would use `=D2*(1+C3)^(1/12)` to obtain the monthly change. Sep 29, 2015 at 16:04
• Fair enough! :-) Sep 29, 2015 at 16:31
• Thanks both! Really helpful. There's a slight difference in result between your methods of calculating the monthly rate... My first thought would be Peter's, but my maths aren't good enough to work out why Chris's might be better. Sep 29, 2015 at 16:34
• You can obtain the actual monthly figures : ons.gov.uk/ons/datasets-and-tables/… Sep 30, 2015 at 2:48

The actual increase in the cost of living for one month over the previous month cannot be calculated from the annualized increase in cost over the entire previous year.

Consider the hypothetical case of a very stable economy, where prices stay constant for decades. Nevertheless, the authorities issue monthly statements, reporting that the change in the cost of living, for the last month, year over year, is 0.00%. Then they go back to sleep for another month.

Then, something happens, say in August, 2001. It causes a permanent large increase in the cost of many parts of the cost of living components. So, in September, the authorities announce that the cost of living for the end of August, 2001, compared to August a year ago, was up 10%.

Great consternation results. Politicians pontificate, unions agitate on behalf of their members, etc...

The economy returns to its customary behavior, except for that one-time permanent increase from August, 2001. So for the next eleven months, each month, the authorities compare the previous months prices to the prices from exactly a year ago, and announce that inflation, year over year, is still 10%.

Finally, we reach September, 2002. The authorities look at prices for the end of August, 2002, and compare them to the prices from the end of August, 2001 (post "event"). Wonder of wonders, the inflation rate is back to 0.00%!!

Absolutely nothing happened in August 2002, yet the rate of inflation dropped from 10% to 0%.