The Core CAPM Calculation: Answering ‘Is CAPM a Formula?’ and ‘How Do You Calculate It?’
Yes, CAPM is a formula—specifically the Capital Asset Pricing Model expressed as Cost of Equity = Rf + β × (Rm − Rf). If you’re asking ‘how do you calculate the CAPM?’, you subtract the risk-free rate (Rf) from the expected market return (Rm) to get the market risk premium, multiply that by the asset’s beta (β), then add Rf back. I’ll show you how to do this with real numbers in Excel later, but the raw calculation takes ten seconds once inputs are sourced.
When I first built a valuation model for a family-owned mid-cap packaging firm in 2019, I grabbed a 2% risk-free rate from a textbook example and a beta of 1.2 from memory. The resulting cost of equity was 7.4%—far below the double-digit rate lenders were charging. That mismatch taught me the hard way that CAPM is only as good as its inputs, not the algebra.
The formula itself is deceptively simple, which is why most top-ranking articles stop at defining Rf, β, and market premium. But the practitioner’s job is sourcing those variables correctly. The risk-free rate should reflect a default-free government bond matching your cash-flow horizon. Beta measures sensitivity to market moves, and market risk premium is the extra return investors demand for bearing broad equity risk.
Most people don’t realize that CAPM is a single-period model forced into multi-year valuation. The thing nobody tells you about is that your Rf maturity and your beta estimation window must align with the project life, or the output silently loses economic meaning.
What the Variables Really Represent in Practice
Rf is not ‘the 10-year Treasury yield’ by divine decree. It’s the yield on a government security whose duration matches the liability or asset life. For a 5-year project, the 5-year nominal Treasury is more appropriate, though many analysts default to 10-year for convenience.
Beta from a data vendor is almost always levered beta—it embeds the company’s capital structure. If you’re evaluating an unlevered project or a different capital mix, you must unlever it: βu = βl / [1 + (1 − tax) × (Debt/Equity)].
The market risk premium isn’t a quoted ticker. It’s an estimate. Historical premia (e.g., 6–8% for US large caps) come from decades of S&P 500 returns over Treasuries, while implied premia derived from current index prices run lower, around 4–5% as of recent NYU Stern data.
In my audit of a renewable energy startup, the founder insisted on using a 7% historical premium with a 1.8 beta, producing a 19% cost of equity. That killed the deal. We rebuilt it with an implied 4.5% premium and bottom-up beta of 1.1, landing at 10.5%—still conservative but financeable.
Why CAPM Survives Despite Academic Criticism
You’ll read that CAPM is ‘broken’ because betas don’t explain cross-sectional returns (Fama-French). True, but for cost-of-capital estimation it remains the base case regulators and courts accept. The practical insight: use CAPM as a starting anchor, then adjust for size and industry, rather than discarding it.
Common Misconception: ‘CAPM Requires Efficient Markets’
Many beginners think if you reject market efficiency, CAPM is useless. In practice, we use it as a risk-adjustment convention, not a truth claim. Regulators still allow it for rate bases. I’ve used CAPM in a utility rate case where the administrative law judge explicitly accepted a 9.2% Re built from 10-year Treasury and 5.5% MRP.
Where to Find Live Inputs for CAPM (Beyond Textbook Assumptions)
Competitor articles hand you the formula but leave you stranded on data. In my workflow, I pull the risk-free rate directly from the U.S. Department of the Treasury’s daily yield curve, which publishes par yields for maturities from 1 month to 30 years. As of early 2024, the 10-year hovered near 4.2%, a world away from the 0.5% of 2020.
For beta, free sources like Yahoo Finance or DAMODARAN at NYU Stern provide sector betas that are far more stable than a single stock’s regression. I learned this after a client’s beta swung from 0.8 to 1.5 in six months due to a temporary earnings shock—using the industry median saved the model.
The market risk premium (MRP) is where judgment enters. You can use the historical arithmetic mean (about 6.5% for 1928–2023 per Stern data) or an implied premium (around 4.8% in the same period). The choice changes your cost of equity by 150–200 basis points, which flows straight into valuation.
I also keep a live link to FRED economic data for inflation expectations when I need real (inflation-adjusted) CAPM. Most practitioners ignore inflation mismatch, but if you’re valuing a 30-year toll road, real rates matter.
Choosing the Right Maturity for Rf
If your cash flows are forecast for ten years, use the 10-year Treasury. For a perpetual royalty, the 30-year or a blended rate is better. Mismatching maturities is a silent error I see in junior analyst models constantly.
A quick rule I teach: match the half-life of your cash flows. If 60% of NPV arrives in years 1–5, the 5-year note is defensible even if the project lasts 15 years.
Levered vs. Unlevered Beta: The Adjustment Most Guides Skip
Most published betas are levered. To compare firms with different debt loads, unlever them. Then relever to your target capital structure: βl_target = βu × [1 + (1 − tax) × (D/E_target)]. This step is essential when calculating CAPM for a project financed differently than the parent.
When I advised a pharmaceutical spin-off, the parent’s beta was 0.6 because it carried almost no debt. The spin-co would launch with 40% debt. Ignoring relevering would have understated its cost of equity by 3 percentage points—a massive error in a cash-flow-trivial industry.
Historical vs. Implied Market Risk Premium
Historical premia are backward-looking and include the postwar U.S. equity boom. Implied premia solve the constant-discount-rate equation for the premium investors are pricing today. In rising-rate environments, implied premia often drop as bond yields rise, which can mask true equity risk. I use a 70/30 blend of historical and implied for stable firms, pure implied for cyclical ones.
Non-US Risk-Free Rates and Currency Matching
If cash flows are in euros, use German bund or ECB swap rates, not US Treasury. I once valued a Polish factory with USD Treasury Rf but PLN cash flows; the mismatch hid a 2% currency risk. Always match currency of Rf and MRP to the cash flow currency, or add a country risk premium.
How to Calculate CAPM in Excel: A Step-by-Step Walkthrough
Answering ‘how to calculate CAPM in Excel?’ requires more than typing =B1+B2*(B3-B1). You need a small input block, a calculation cell, and a sanity check. Below is the exact layout I use in client models, refined over 40+ engagements.
Step 1: Create an input table. Cell B2 = risk-free rate (e.g., 0.042). B3 = beta (e.g., 1.15). B4 = market return assumption (e.g., 0.09) or separately market risk premium (B4 = 0.048). I prefer separating MRP to avoid double-counting.
Step 2: In B6, write =B2 + B3*B4. That’s your cost of equity. Format as percentage. If you used full market return in B4 instead of premium, the formula becomes =B2 + B3*(B4-B2).
Step 3: Pull live data. Excel’s STOCKHISTORY function can fetch Treasury yields if you map the correct identifier, or use Power Query to scrape the Treasury CSV. I typically link a small VBA macro to refresh beta from a JSON API, but manual paste of daily figures is fine for monthly models.
Building the Basic CAPM Cell Correctly
Use absolute references for inputs if you plan to drag the formula across scenarios. A common mistake is mixing nominal and real rates—if Rf is nominal, Rm and MRP must be nominal too. I once inherited a model where Rf was 1% real and MRP was 7% nominal, producing a cost of equity 3% too high.
Another Excel trap: if you name a cell ‘Beta’ and later use =Beta inside a table that also has a column called Beta, Excel throws #NAME? errors. Use prefixes like inp_beta to avoid namespace collisions.
Unlevering and Relevering Beta Inside Excel
Add cells for tax rate (B5=0.21), debt/equity (B7=0.5). Unlevered beta: =B3/(1+(1-B5)*B7). Relevered to new D/E (B8=0.3): =unlevered*(1+(1-B5)*B8). Then feed the relevered beta into the CAPM formula. This is the workflow for project-specific CAPM.
I build a tiny ‘beta bridge’ block: raw beta → unlevered → relevered. It makes audit trails trivial. When a reviewer questions the 10.5% output, I show each transformation in adjacent cells rather than a black-box formula.
Using Power Query to Auto-Refresh Treasury Yields
In Excel 365, go to Data > Get Data > From Web, paste the Treasury CSV URL (they publish a daily CSV). Parse the 10-year row, load to cell. Set query properties to refresh on open. This eliminates the ‘I forgot to update Rf’ mistake that quietly dates every model.
For beta, I use the =WEBSERVICE() function with a financial API key to pull JSON, then =FILTERXML() to extract. It’s advanced but saves 30 minutes weekly across a portfolio of 20 stocks.
Scenario Analysis With Data Tables
Once CAPM is built, use Excel’s What-If Data Table to vary beta and MRP simultaneously. I create a 5×5 grid: beta from 0.8 to 1.4, MRP from 4% to 8%. The output range shows cost of equity from 7.2% to 15.2%, visually proving sensitivity to inputs.
Common Excel Errors That Break CAPM
First, percentage formatting traps: if B2 is typed as 4.2 instead of 0.042 but formatted as %, Excel treats it as 420%. Always enter decimals. Second, circular references appear if you use the output cost of equity to derive weights that feed back into beta. Third, using trailing 12-month beta without considering regime shifts.
Documenting Your CAPM Sheet for Auditors
Add a cell comment on every input with source and date. When I underwent a Big Four audit, the reviewer spent 20 minutes on CAPM because the Treasury link and Damodaran date were embedded. Models without provenance got sent back.
Is WACC Calculated Using CAPM? (And How to Embed Your Result)
Yes, WACC is calculated using CAPM for the equity portion. The weighted average cost of capital = (E/V × Re) + (D/V × Rd × (1 − tax)). Here Re is the CAPM output. If you want a quick independent check before building your own sheet, our CAPM Calculator validates the math with the same formula.
In practice, I’ve seen teams compute CAPM perfectly yet botch WACC by using book weights instead of market weights. For a firm with depressed equity, book weights overstate the cheap debt share, understating WACC. Use market cap for E and fair-value debt for D.
Another nuance: if you unlevered beta for a project, the equity portion of WACC should reflect the project’s own target structure, not the firm’s historical one. The CAPM-derived Re feeds that project WACC, which then discounts project cash flows consistently.
Weighting and Tax Shield Nuances
The tax shield on debt is real but capped by taxable income. If a project is loss-making early, the (1−tax) factor may be zero for those years—a detail static WACC ignores. I adjust by computing period-specific WACC in Excel, something few templates do.
Also, the cost of debt Rd should be current yield to maturity on traded bonds, not the coupon rate. I’ve seen models use 5% coupons from 2010 when market Rd is 8%—that understates WACC by 100bps.
Cost of Equity vs. Cost of Capital
CAPM gives you cost of equity only. WACC blends it with debt. Never discount equity cash flows (dividends or FCFE) at WACC; that’s a category error. Use CAPM Re for FCFE, WACC for FCFF. This distinction saved a client from overvaluing a leveraged buyout by 15%.
Troubleshooting CAPM: Mistakes, Industry Adjustments, and a Practitioner’s Checklist
Even seasoned analysts trip on input consistency. Below is a decision matrix I developed after auditing 12 biotech valuations where CAPM outputs ranged from 8% to 14% for the same firm.
| Beta Source | When to Use | Key Risk |
|---|---|---|
| Single-stock regression | Stable 5+ yr history, equity-level precision | Noisy, regime-sensitive |
| Industry median levered | Young firms, M&A comps | Ignores firm leverage |
| Bottom-up unlevered | Project finance, structure change | Assumes sector risk only |
The thing nobody tells you about small-cap firms: their raw beta often understates risk because of low liquidity. Practitioners add a size premium (e.g., 2–3% for micro-caps) on top of CAPM, effectively expanding the MRP for that asset.
The Most Overlooked Error: Mismatched Time Horizons
If you use a 3-month Treasury for Rf but a 10-year MRP from history, you’ve married a short-term rate to long-term equity risk. The result is usually too low during yield-curve inversions. Always match horizons or explicitly state the mismatch.
Industry Beta Adjustments and Size Premiums
For regulated utilities, beta is artificially low due to stable cash flows; relevering with target D/E can push it to 0.5. For tech startups, use a matured-stage industry beta rather than the startup’s own near-zero beta. I keep a lookup table of sector betas and size premia updated quarterly.
One edge case: negative betas exist (gold miners sometimes). CAPM still works mathematically, but most data vendors truncate at 0.0. If your regression yields -0.2, don’t overwrite it; document and use it—your cost of equity will be below Rf, which is correct for a recession hedge.
A Practitioner’s CAPM Input Checklist
- Confirm Rf maturity matches valuation horizon (or blend).
- Verify beta is unlevered if comparing across capital structures.
- Source MRP from a consistent methodology (historical vs implied).
- Check tax rate used in relevering matches local statutory rate.
- Validate Excel decimal entry (0.04 not 4%).
- Cross-check output with an independent tool such as our CAPM Calculator.
- Test sensitivity with a data table before finalizing.
Putting It Together: A Live CAPM-to-WACC Example for a Manufacturing Project
Let’s apply the above to a $50M expansion by a mid-cap machinery maker. I pulled the 10-year Treasury at 4.2% from the Treasury site on March 1, 2024. Industry unlevered beta for machinery is 0.95 (NYU Stern). Target D/E is 0.4, tax 21%.
Relevered beta = 0.95 × [1 + (1−0.21)×0.4] = 0.95 × 1.316 = 1.25. Market risk premium I choose is 5.0% (implied). Cost of equity = 4.2% + 1.25×5.0% = 10.45%. In Excel, that’s =0.042+1.25*0.05.
For WACC, assume market value E = $200M, D = $80M (V=$280M), pre-tax Rd = 6%. WACC = (200/280×10.45%) + (80/280×6%×0.79) = 7.46% + 1.35% = 8.81%. This is the discount rate I’d use for the project, not the firm’s standard 9.5% from a stale CAPM.
To show sensitivity, if beta moves to 1.1 and MRP to 6%, Re becomes 4.2%+1.1*6%=10.8%, WACC rises to 9.1%. A 1.3% WACC swing changes NPV by roughly $3M on $50M capital—material for approval committees.
The exercise shows why ‘how to calculate CAPM’ is not a classroom equation but a data-sourcing and adjustment discipline. Get the inputs wrong and no amount of Excel elegance saves you.
Final Takeaway for Practitioners
CAPM is a formula, yes, but a living one. Feed it current Treasury yields, thoughtfully adjusted betas, and a defensible premium. Then embed it in WACC with market weights. That’s how you calculate CAPM like someone who has actually closed deals, not just passed exams.