Financial
Historical beta, regression intercept and modeled CAPM return
Estimate historical Beta = Cov(Ri, Rm) / Var(Rm) in one SLOPE(StockReturns, MarketReturns) call and compute CAPM Required Return Rf + Beta * (Rm - Rf).
Estimate historical beta with an OLS slope
SLOPE(stock returns, market returns) estimates the slope of the specified historical observations.
The CAPM expression Rf+Beta*(ExpectedMarketReturn-Rf) is a model of required return, using inputs on a consistent basis.
| Period | Market Return X (A) | Stock Return Y (B) | CAPM Inputs |
|---|---|---|---|
| Q1 | -4.0% | -5.0% | Risk-Free (C2) = 4.0% |
| Q2 | 0.0% | +1.0% | Market Return (D2) = 10.0% |
| Q3 | +4.0% | +7.0% | Beta = SLOPE(B, A) = 1.50 |
| Q4 | +8.0% | +13.0% | CAPM Ke = 4% + 1.5*(6%) = 13.0% |
Historical Beta and CAPM Cost of Equity formulas
Watch argument order in SLOPE(known_ys, known_xs): Stock Returns MUST come first and Market Returns second!
=ROUND(C2 + SLOPE(B2:B5, A2:A5) * (D2 - C2), 4)Direct Stock Beta formula
Beta describes sensitivity to the chosen market series, not total risk; negative beta implies opposite direction.
=ROUND(SLOPE(B2:B5, A2:A5), 4)Additional worked calculation
Using the initial example data:
=ROUND(INTERCEPT(B2:B5, A2:A5), 4)Inputs and interpretation
Estimate beta using aligned return observations at the same frequency with nonzero market variance. The raw-return slope is descriptive; Jensen alpha uses excess returns relative to a matching risk-free series, not simply the raw intercept shown here. CAPM inputs must share currency and horizon. Historical beta and modeled required return do not guarantee future returns; negative beta reverses direction.
Try the example
Use the original values or change them to test your understanding.