Laspeyres price index: formula and calculation in Excel

The Laspeyres price index formula

Laspeyres = Σ(p₁ × q₀) ÷ Σ(p₀ × q₀) × 100

• p₀ are the prices in the base period (2022), p₁ the prices in the current period (2026).
• q₀ are the quantities bought in the base period. They stay fixed, which is why Laspeyres is called a fixed-basket index.

The bottom of the fraction is what the base basket actually cost, the top is what the very same basket would cost at today's prices. Only the prices change between the two, so the ratio is a pure price change. The base period is set to 100.

Calculating it in Excel with SUMPRODUCT

You could add a column of p₀ × q₀ and a column of p₁ × q₀ and divide their totals. SUMPRODUCT does each sum in one cell:

• Base basket at base prices: =SUMPRODUCT(D2:D6,B2:B6) = $16,310
• Base basket at current prices: =SUMPRODUCT(E2:E6,B2:B6) = $22,785
• Laspeyres index: =B10/B9*100 = 139.7

An index of 139.7 means the basket costs 39.7% more than in 2022 (=B11/100-1). Spread over four years, that is =(B11/100)^(1/4)-1 = 8.72% a year, the same compounding as in the CAGR formula.

Worked example: a bakery's input-cost index, 2022 = 100

Quantities and prices are inputs (yellow). Column F holds the price relatives, rows 9 to 15 build the Laspeyres index and rows 18 and 19 compare it with Paasche and Fisher.

Worked example: a bakery's input-cost index, 2022 = 100

	A	B	C	D	E	F
1	Item	Qty 2022 (q0)	Qty 2026 (q1)	Price 2022 (p0)	Price 2026 (p1)	Price relative
2	Flour (kg)	4,000	4,300	0.62	0.81	130.6
3	Butter (kg)	900	620	6.40	9.10	142.2
4	Eggs (dozen)	1,500	1,350	2.10	3.05	145.2
5	Sugar (kg)	1,200	1,150	0.95	1.10	115.8
6	Electricity (kWh)	21,000	20,500	0.18	0.26	144.4
7
8	Laspeyres step by step	Value
9	Base basket at 2022 prices, Σp0·q0	16,310.00
10	Base basket at 2026 prices, Σp1·q0	22,785.00
11	Laspeyres price index (2022 = 100)	139.7
12	Basket inflation 2022–2026	39.7%
13	Annual rate over 4 years	8.72%
14	Same index from weighted price relatives	139.7
15	Unweighted average of relatives	135.7
16
17	Compare	Value
18	Paasche (2026 quantities)	139.2
19	Fisher ideal index	139.4

Fictional bakery, illustrative prices and quantities. Simplified index calculations for learning, with no affiliation with any statistics office.

The same index from weighted price relatives

A price relative is one item's price change as an index: p₁ ÷ p₀ × 100. Butter went from $6.40 to $9.10 a kilo, a relative of 142.2, sugar only to 115.8.

The Laspeyres index is exactly the average of the relatives weighted by each item's share of base-period spending (p₀ × q₀): =SUMPRODUCT(F2:F6,D2:D6*B2:B6)/B9 returns 139.7 again. This is how most published price indices are built in practice: price relatives from price collection, weights from spending.

The plain average of the five relatives is only 135.7. It gives sugar, a small cost line with the smallest rise, the same weight as butter and electricity, the biggest cost lines. Weighting is the whole point of a price index.

Laspeyres vs Paasche vs Fisher

The Paasche index uses current quantities instead: Σp₁q₁ ÷ Σp₀q₁ × 100. The bakery bought less butter (620 kg instead of 900) and fewer eggs after their prices jumped, so Paasche gives those items less weight and comes out lower, at 139.2.

This is the substitution bias: buyers move away from items that became more expensive, but a fixed Laspeyres basket ignores the switch and tends to overstate the rise in costs. Paasche tends to understate it. The Fisher ideal index is the geometric mean of the two, =SQRT(B11*B18) = 139.4, and lies between them.

Laspeyres stays popular because it needs quantities from one past period only, while Paasche needs fresh quantity data every period. Many consumer price indices are Laspeyres-type indices whose basket weights are updated regularly.

Reading an index correctly

Index points are not percent. If an index rises from 120 to 139.7, that is 19.7 points but a 16.4% increase, because the change is measured against the starting level. Use the percent change formula on the index values. Only changes from the base year, where the index is 100, read directly as percent.

To move the base to another year, divide every index value by the index of the new base year and multiply by 100. The percentage changes between years stay the same.

The Laspeyres quantity index

Swap the roles of prices and quantities and you get a quantity index: Σq₁p₀ ÷ Σq₀p₀ × 100, base-period prices as weights. It measures how much more or less the bakery bought, valued at constant prices. A Laspeyres quantity index multiplied by a Paasche price index (both divided by 100) gives the value index, the change in total spending.

Common mistakes

• Adding up prices without quantities. A sum of prices compares a kilo of butter with a kilowatt-hour of electricity by its price tag only. Multiply by the base quantities first.
• Mixing baskets. Current quantities on top and base quantities below measure the change in spending (a value index), not prices.
• Reading index points as percent. From 120 to 139.7 is 19.7 points but a 16.4% rise. Divide by the starting level.
• Inconsistent units. If the price is per kilo, the quantity must be in kilos too. A price per pack with a quantity in kilos silently breaks the weights.
• Forgetting the × 100 or the − 1. The ratio 1.397 is not an index, and the index 139.7 is not an inflation rate. The rate is index ÷ 100 − 1 = 39.7%.