Price elasticity of demand from a log-log regression in Excel

What price elasticity of demand means

Price elasticity e = % change in quantity ÷ % change in price

An elasticity of −1.79 means that a 1% higher price sells about 1.79% fewer units. Below −1 (in absolute terms above 1) demand is elastic: a price rise loses more units than it gains in price, so revenue falls. Between 0 and −1 demand is inelastic and a price rise increases revenue. Elasticity is almost always negative, and many analysts quote it without the sign.

Why a log-log regression

One price change gives one noisy estimate. With many weeks at different prices you can use all of them at once. The usual model is the constant-elasticity demand curve Q = A × Pe. Take natural logs on both sides and it becomes a straight line: ln Q = ln A + e × ln P. Its slope is the elasticity, unit-free and directly readable as a percentage, at every price on the curve.

A plain regression of units on price would give "units lost per $1", which depends on the size of the product and changes along the curve. The log-log slope does not.

Step by step in Excel

Add two helper columns, =LN(B2) for the price and =LN(C2) for the units, and fill them down. Then:

• Elasticity: =SLOPE(E2:E13,D2:D13) = −1.79 (y-values first, x-values second)
• Intercept: =INTERCEPT(E2:E13,D2:D13) = 9.236, which is ln A
• Fit: =RSQ(E2:E13,D2:D13) = 0.979

To forecast, go back from the log scale: =EXP(intercept)*price^elasticity. At the current $6.49 the model expects 364 units a week. A 10% price rise changes units by 1.1^−1.79 − 1 = −15.6%, slightly less than 10 × 1.79 because the relationship is not linear. An XY scatter chart of LN(units) against LN(price) with a linear trendline shows the same slope and lets you spot outlier weeks.

Worked example: elasticity of a loose-leaf tea

Twelve weeks of prices and units are inputs. The log columns, the regression and the price decision are formulas.

Worked example: elasticity of a loose-leaf tea

	A	B	C	D	E
1	Week	Price ($)	Units sold	LN(price)	LN(units)
2	Week 1	5.99	433	1.790	6.071
3	Week 2	6.49	353	1.870	5.866
4	Week 3	5.49	501	1.703	6.217
5	Week 4	6.99	331	1.944	5.802
6	Week 5	5.99	403	1.790	5.999
7	Week 6	7.49	275	2.014	5.617
8	Week 7	6.49	374	1.870	5.924
9	Week 8	5.79	442	1.756	6.091
10	Week 9	6.99	309	1.944	5.733
11	Week 10	6.29	388	1.839	5.961
12	Week 11	7.29	301	1.987	5.707
13	Week 12	5.49	482	1.703	6.178
14
15	Result				Value
16	Price elasticity e (slope of the log-log line)				-1.79
17	Intercept (log scale)				9.236
18	R² of the fit				0.979
19	Units change for a 10% price rise				-15.6%
20	Current price ($)				6.49
21	Forecast units/week at the current price				364
22	Unit cost ($)				3.10
23	Profit-maximising price: cost × e ÷ (1 + e)				7.05
24	Forecast units/week at that price				314
25	Weekly profit at the current price ($)				1,232.49
26	Weekly profit at the optimal price ($)				1,238.78

Fictional product and illustrative sales data.

From elasticity to the best price

For a constant elasticity e (more elastic than −1), profit is highest where price = unit cost × e ÷ (1 + e), the markup rule. With e = −1.79 the multiplier is 2.27, so a $3.10 unit cost gives an optimal price of $7.05. The forecast then sells 314 units and earns $1,238.78 a week against $1,232.49 at $6.49. The gain is small because a profit curve is flat near its peak: a price somewhat too low or too high costs little, which is a good reason to move in steps and test. See markup vs margin for the pricing arithmetic.

Arc elasticity: the quick check

Without a regression, the arc (midpoint) elasticity between two price points divides the changes by their averages: (Q₂ − Q₁) ÷ ((Q₁ + Q₂) ÷ 2) divided by (P₂ − P₁) ÷ ((P₁ + P₂) ÷ 2). Between the $5.49 week (501 units) and the $7.49 week (275 units) it gives −1.89. It is useful as a sanity check, but it rests on two weeks only, and whatever else happened in those weeks ends up in the number. Using the averages as the base makes the result the same whichever week you start from, unlike a plain percent change.

Promotions, seasons and other traps

If price cuts came with a display, an email or a holiday, the slope credits the price for the extra sales and the elasticity looks too high. Add dummy variables for promotion weeks and seasons and fit them together with LN(price) using LINEST: the price coefficient is then the elasticity with the other effects held constant. Also check that prices varied enough: weeks at only two prices give a slope with a large error. And remember that elasticity holds within the price range you observed. Far outside it the model is a guess.

Common mistakes

• Swapping x and y in SLOPE. The units are the y-values and come first: SLOPE(ln units, ln price).
• Forgetting EXP in the forecast. The fitted line predicts ln(units), a number around 6, not the units themselves.
• Using LOG instead of LN. Base-10 logs give the same slope only if both columns use them, so stay with LN for both.
• Ignoring promotions and seasonality. Price cuts that come with marketing make demand look more elastic than it is. Control for them with dummy variables in LINEST.
• Applying the markup rule to inelastic demand. With e between 0 and −1 the formula breaks down, which means the price can still go up profitably.
• Extrapolating far outside the tested prices. The constant-elasticity curve fits the range in the data, not every price.