01 / 09The Catch-Up Was Sized on Summed Rates

PercentagesPercent ChangeFinancial Functions

Adjust for Inflation in Excel: Real Terms With a CPI Index

The Union Was Offered a £300 Catch-Up Because the Pay Sheet Added Five Years of Inflation Rates Together, and Every Member Was £1,087 Behind

9 Nov 20268 min read

UsesPOWERXLOOKUPSUMROUND

Fenwick Housing Trust promised its staff that pay would catch up with five years of inflation. The pay sheet sized the payment at £300 a head. Prices had compounded, and the real shortfall was £1,087.01, more than three times the offer.

MeasurePay sheetCorrect
Prices, 2020 to 2025+26.0%+28.62%
2020 salary in 2025 £37,80038,587.01
Catch-up per head3001,087.01
Budget for 412 staff123,600447,847

Scope. Every formula here works in any version of Excel since 2007, on Windows, Mac and the web, except XLOOKUP in section 4, which needs Excel 2021 or Microsoft 365. An INDEX and MATCH version sits beside it.

The rates are annual, December to December. Mixing them with annual-average figures from another table shifts every result, so take every rate from the same series.

01The Catch-Up Was Sized on Summed Rates

The reference grade, a support officer, earned £30,000 in 2020 and £37,500 in 2025. The sheet added the five inflation rates and carried the 2020 salary forward by the total:

=D2*(1+SUM(B3:B7))-D7

Five Years of Pay Against Five Years of Prices

Fenwick Housing Trust's reference grade in A1:F10. Column B is each year's inflation rate and C the price index built from it, 2020 = 100. D is the salary actually paid, E the 2020 salary carried forward with prices, and F the gap. Rows 9 and 10 compare the sheet's summed rates with the compounded index.

ABCDEF
1
Year
Inflation
CPI index
Salary (£)
Keep-pace pay (£)
Gap (£)
2
2020
100
30000
30000
0
3
2021
5.4%
105.4
30900
31620
720
4
2022
10.5%
116.47
32100
34940
2840
5
2023
4.0%
121.13
34200
36338
2138
6
2024
2.5%
124.15
36000
37246
1246
7
2025
3.6%
128.62
37500
38587
1087
8
9
Summed rates
26.0%
126
37500
37800
300
10
Compounded
28.6%
128.62
37500
38587
1087

fxCells with formulas are highlighted in green

Hover over formula cells to see the formula and highlight referenced cells

SUM(B3:B7) is 26.0%, so the sheet said £30,000 needed to become £37,800, and £37,500 was £300 short. The board approved £300 a head for 412 staff, £123,600 in all.

Column F follows the gap year by year. In 2022 the grade was £2,840 behind, and by 2025 the gap was still £1,087. The summed figure was never close.

Scenario: Enter the years in A2:A7, the rates in B3:B7 and the salaries in D2:D7. Enter =D2*(1+SUM(B3:B7))-D7 in H2 and check that it returns 300.

Try it in the grid

02Inflation Compounds, So Rates Don't Add

Each year's inflation is measured on prices that have already risen. So the rates multiply. A basket costing £100 in 2020 cost £105.40 in 2021, and 2022's 10.5% applies to the £105.40, not to the original £100.

YearRateBasket at startBasket at end
20215.4%100.00105.40
202210.5%105.40116.47
20234.0%116.47121.13
20242.5%121.13124.15
20253.6%124.15128.62

Adding the rates drops every rise on a rise. Here that is 2.62 points over five years, and the gap grows with higher rates and longer periods. In one cell, the compounded total is:

=PRODUCT(1+B3:B7)-1

It returns 28.62%. In Excel 2019 and earlier, confirm it with Ctrl+Shift+Enter, or use the index in section 3.

Scenario: Enter =PRODUCT(1+B3:B7)-1 in H3 and format it as a percentage with two decimals. Check that it reads 28.62%, then compare it with =SUM(B3:B7).

Try it in the grid

03Build a Price Index From Annual Rates

A price index puts every year on one scale. Pick a base year, give it 100, and grow it by each year's rate. Enter 100 in C2, then in C3:

=C2*(1+B3)

Fill it to C7. The index reads 105.40, 116.47, 121.13, 124.15 and 128.62. Inflation between any two years is now one division: =C7/C2-1 is the 28.62% rise from 2020 to 2025.

Published statistics give you the index directly. If you have it, paste the index and skip the rates, because rounded rates drift from the official series by a few hundredths each year.

The base year does not matter. An index with 2025 = 100 gives exactly the same ratios, so rebase a published series only when a report needs it.

Scenario: Enter 100 in C2 and =C2*(1+B3) in C3, filled to C7. Check that C7 reads 128.62, then enter =C7/C2-1 and check that it matches the PRODUCT result.

Try it in the grid

04Rebase Any Year With XLOOKUP

To move money from one year to another, multiply by the ratio of the two years' index values. The 2020 salary in 2025 pounds is:

=D2*C7/C2

It returns £38,587.01, and the real catch-up is that minus the £37,500 paid. Wrap it in ROUND for the board paper: =ROUND(D2*C7/C2-D7,2) gives 1,087.01.

For a report that asks "in which year's money?", put the target year in H1 and look its index up:

=D2*XLOOKUP($H$1,$A$2:$A$7,$C$2:$C$7)/C2

Fill it down column G and every salary is in H1's money. In older versions, INDEX($C$2:$C$7,MATCH($H$1,$A$2:$A$7,0)) returns the same index.

YearSalaryIn 2020 £
202030,00030,000
202130,90029,317
202232,10027,561
202334,20028,235
202436,00028,996
202537,50029,155

In real terms the grade never got back to 2020. The nominal column rose every year; the real column fell £2,439 in two years and was still £845 short in 2025.

Scenario: Enter 2020 in H1 and the XLOOKUP formula in G2, filled to G7. Check that G4 reads 27,561, then change H1 to 2025 and check that G2 reads 38,587.01.

Try it in the grid

05Divide, Don't Subtract, for Real Change

Pay rose 25% and prices 28.62%. The quick answer is a real fall of 3.62%, but that subtraction is the same mistake as summing the rates. Real change is the ratio of the two growth factors:

=(D7/D2)/(C7/C2)-1

It returns -2.82%. A £37,500 salary buys what £29,155 bought in 2020, and 29,155 ÷ 30,000 is 0.9718.

MethodFormulaReal change
Summed rates, subtracted=D7/D2-1-SUM(B3:B7)-1.00%
Compounded, subtracted=D7/D2-1-(C7/C2-1)-3.62%
Compounded, divided=(D7/D2)/(C7/C2)-1-2.82%

The subtraction is close when both changes are small. Over five years, or in a year like 2022, it is out by most of a point, and a negotiating table will notice.

Scenario: Enter =(D7/D2)/(C7/C2)-1 and =D7/D2-1-(C7/C2-1) side by side. Check that they read -2.82% and -3.62%, then set D7 to 38587.01 and check that the first returns 0%.

Try it in the grid

06Annualise the Real Change With POWER

A five-year figure is hard to compare with a one-year pay offer. Take the fifth root of the real growth factor to get the equivalent steady yearly rate:

=POWER((D7/D2)/(C7/C2),1/5)-1

It returns -0.57%: the grade lost just over half a percent of buying power a year. ^(1/5) works the same as POWER. Count the gaps, not the years listed: 2020 to 2025 is five.

Dividing -2.82% by five gives -0.56%. That looks harmless here, but it is the same additive shortcut, and it drifts further as the total grows. Use POWER whenever you quote a yearly rate.

Scenario: Enter =POWER((D7/D2)/(C7/C2),1/5)-1 and format it to two decimals. Check that it reads -0.57%, then change the exponent to 1/6 and see how counting the years instead of the gaps shrinks it.

Try it in the grid

07Five Traps

  1. Adding annual rates. SUM drops every rise on a rise. Multiply growth factors with PRODUCT, or build an index.
  2. Averaging the rates and multiplying by five. =AVERAGE(B3:B7)*5 is the same 26.0% as the sum, with a formula that looks more careful.
  3. Subtracting inflation from pay growth. Divide the growth factors instead. Subtraction overstated Fenwick's real fall by 0.8 points.
  4. Mixing index series. December-to-December rates and annual averages give different totals. Take every year from one published series.
  5. Rebasing the wrong way round. =D2*C2/C7 shrinks the 2020 salary instead of growing it. Money moves towards the later year by multiplying by the later index.

08Four Exercises

  1. Reproduce it. Enter the rates and salaries and confirm that the summed-rate formula returns a £300 catch-up.
  2. Fix it. Build the index in column C and confirm that =D2*C7/C2-D7 returns 1,087.01.
  3. Rebase it. Add the XLOOKUP column and confirm that the grade's real pay in 2022 was £27,561 in 2020 money.
  4. Annualise it. Confirm that the real change is -2.82% over five years and -0.57% a year with POWER.

What to Take Away

Every rate on Fenwick's sheet was correct. The error was the plus sign, which treated five years of rising prices as five separate rises on the 2020 basket.

Inflation is a chain, so carry it as one. Build an index, move money between years by the ratio of two index values, divide to get real change, and use POWER for a yearly rate.

Share this article:
Back to Blog
Daily challenge · Day 107

Total a Mileage Claim for One Pay Period with SUMIFS

A new exercise every day, solved in a real grid.

Solve today’s challenge