Quantitative Finance
Campbell-Shiller CAPE Ratio & Excess CAPE Yield (ECY) Formula
Calculate the 10-year inflation-adjusted Cyclically Adjusted P/E ratio CAPE = Real_Price / AVERAGE(Real_EPS_10y) and Shiller’s Excess CAPE Yield ECY = (1 / CAPE) - Real_10Y_Bond_Yield.
Smooth business-cycle profit spikes and recessions over 10 real years!
Trailing 12-month P/E ratios look artificially expensive during recessions (when trough earnings collapse) and deceptively cheap at cyclical profit peaks! Campbell & Shiller (1998) solved this distortion by dividing today’s CPI-adjusted index level P_real by the 10-year average of CPI-adjusted earnings AVERAGE(Real_EPS_1..10): CAPE = P_real / AVERAGE(Real_EPS_10y).
Because equity valuation also depends on competing real interest rates, Shiller’s Excess CAPE Yield (ECY) subtracts the 10-year real sovereign bond yield (TIPS / real 10Y yield) from the inverse CAPE cyclically adjusted earnings yield: ECY = (1 / CAPE) - r_real_10y!
| Real Index Price (10y Avg Real EPS = $127) | CAPE Multiple | Cyclical Earnings Yield (1/CAPE) | Excess CAPE Yield (r_real = 1.75%) |
|---|---|---|---|
| 3,810 | 30.00x | 3.3333% | +1.5833% |
| 2,540 | 20.00x | 5.0000% | +3.2500% |
| 1,905 | 15.00x | 6.6667% | +4.9167% |
Campbell-Shiller 10-Year CAPE Multiple
Divides current real index level B12 by the 10-year real EPS average B2:B11.
=IF(OR(B12 <= 0, COUNT(B2:B11) < 10, AVERAGE(B2:B11) <= 0), "Invalid Price or Earnings", ROUND(B12 / AVERAGE(B2:B11), 4))Excess CAPE Yield (% ECY = Cyclical Earnings Yield - Real 10Y Yield)
Computes ((AVERAGE(B2:B11) / B12) - B13) * 100 in percentage points.
=IF(OR(B12 <= 0, COUNT(B2:B11) < 10, AVERAGE(B2:B11) <= 0), "Invalid Price or Earnings", ROUND(((AVERAGE(B2:B11) / B12) - B13) * 100, 4))Try the example
Use the original values or change them to test your understanding.