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.
| Measure | Pay sheet | Correct |
|---|---|---|
| Prices, 2020 to 2025 | +26.0% | +28.62% |
| 2020 salary in 2025 £ | 37,800 | 38,587.01 |
| Catch-up per head | 300 | 1,087.01 |
| Budget for 412 staff | 123,600 | 447,847 |
Scope. Every formula here works in any version of Excel since 2007, on Windows, Mac and the web, except
XLOOKUPin section 4, which needs Excel 2021 or Microsoft 365. AnINDEXandMATCHversion 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.
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.
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.
| Year | Rate | Basket at start | Basket at end |
|---|---|---|---|
| 2021 | 5.4% | 100.00 | 105.40 |
| 2022 | 10.5% | 105.40 | 116.47 |
| 2023 | 4.0% | 116.47 | 121.13 |
| 2024 | 2.5% | 121.13 | 124.15 |
| 2025 | 3.6% | 124.15 | 128.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).
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.
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.
| Year | Salary | In 2020 £ |
|---|---|---|
| 2020 | 30,000 | 30,000 |
| 2021 | 30,900 | 29,317 |
| 2022 | 32,100 | 27,561 |
| 2023 | 34,200 | 28,235 |
| 2024 | 36,000 | 28,996 |
| 2025 | 37,500 | 29,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.
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.
| Method | Formula | Real 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%.
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.
07Five Traps
- Adding annual rates.
SUMdrops every rise on a rise. Multiply growth factors withPRODUCT, or build an index. - Averaging the rates and multiplying by five.
=AVERAGE(B3:B7)*5is the same 26.0% as the sum, with a formula that looks more careful. - Subtracting inflation from pay growth. Divide the growth factors instead. Subtraction overstated Fenwick's real fall by 0.8 points.
- Mixing index series. December-to-December rates and annual averages give different totals. Take every year from one published series.
- Rebasing the wrong way round.
=D2*C2/C7shrinks the 2020 salary instead of growing it. Money moves towards the later year by multiplying by the later index.
08Four Exercises
- Reproduce it. Enter the rates and salaries and confirm that the summed-rate formula returns a £300 catch-up.
- Fix it. Build the index in column C and confirm that
=D2*C7/C2-D7returns 1,087.01. - Rebase it. Add the
XLOOKUPcolumn and confirm that the grade's real pay in 2022 was £27,561 in 2020 money. - 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.