How to Calculate CAPM in Excel

To calculate CAPM in Excel, put the risk-free rate in B1, beta in B2, and the expected market return in B3, then type =B1+B2*(B3-B1) in B4. The capital asset pricing model (CAPM) estimates the return an investment should offer for the market risk it carries: Expected return = Risk-free rate + Beta × (Market return − Risk-free rate). If you do not have a beta, you can work one out from price history with the SLOPE function.

This is general education, not investment advice. CAPM is a simple model, its answer is only as good as the three numbers you feed it, and real returns often differ.

What you need before you start

The CAPM formula uses only addition, subtraction, and multiplication, so it works in any version of Excel on Windows, Mac, or the web. The SLOPE function used for beta is also a long-standing Excel function. Only the optional STOCKHISTORY shortcut described later needs a Microsoft 365 subscription.

You need three inputs:

• Risk-free rate: the return on an investment treated as having no risk. Analysts often use the current yield on a government bond, such as the 10-year US Treasury.
• Beta: a number that shows how strongly a stock has moved compared with the overall market. The market itself has a beta of 1.
• Expected market return: the return you assume for a broad index such as the S&P 500, often based on a long-term average.

The part in brackets, market return minus risk-free rate, is called the market risk premium. It is the extra return investors expect for holding stocks instead of the risk-free asset.

How to set up the CAPM formula in Excel

1. Open a blank workbook. In A1, A2, A3, and A4, type the labels Risk-free rate, Beta, Market return, and Expected return.
2. In B1, type the risk-free rate with a percent sign, such as 4%. Excel stores it as 0.04 and shows it as a percentage.
3. In B2, type the beta as a plain number, such as 1.2. Do not format this cell as a percentage.
4. In B3, type the expected market return with a percent sign, such as 9%.
5. In B4, type =B1+B2*(B3-B1) and press Enter.
6. If B4 shows a decimal such as 0.1, select it and click Percent Style in the Number group on the Home tab, or press Ctrl+Shift+%.

With a 4% risk-free rate, a beta of 1.2, and a 9% market return, the result is 4% + 1.2 × (9% − 4%) = 10%. The brackets matter. Without them Excel multiplies beta by the market return alone and the answer is wrong.

Percent Style shows whole percentages by default. To see more detail, open the Format Cells dialog, choose Percentage, and raise the number in the Decimal places box. Microsoft explains the options in Format numbers as percentages.

Check that the formula is right

A quick test confirms the worksheet is wired correctly. Change B2 to 1. The expected return should now equal the market return in B3, because a beta of 1 means the stock is assumed to move with the market. Change B2 to 0 and the result should equal the risk-free rate in B1. If either test fails, look for missing brackets or a cell reference that points to the wrong row.

To undo a test, press Ctrl+Z or retype the original beta.

How to calculate beta in Excel

Many financial sites publish a beta for each stock, and typing that number into B2 is the fastest route. To calculate your own, compare the stock’s returns with the market’s returns over the same period.

1. Get monthly closing prices for the stock and for a market index, such as the S&P 500, covering the same dates.
2. On a new sheet, put headings in row 1, the dates in column A, the stock prices in column B, and the index prices in column C. Sort the rows from oldest to newest.
3. In D3, type =B3/B2-1 to get the stock’s return for that month. In E3, type =C3/C2-1 for the index return.
4. Select D3 and E3, then drag the fill handle (the small square at the corner of the selection) down to the last row of prices.
5. In an empty cell, type =SLOPE(D3:D61,E3:E61) and press Enter. Change the ranges to match your rows. The result is beta.

The order of the two ranges matters. Microsoft’s SLOPE function page gives the syntax as SLOPE(known_y’s, known_x’s), where the first range is the dependent data. For beta, that is the stock’s returns, followed by the market’s returns. Swapping them gives a different number.

The returns start in row 3 because the first price has nothing before it to compare with. That means 60 months of prices in rows 2 to 61 produce 59 monthly returns. Five years of monthly data is a common choice, but there is no single correct period, and a different period or index will give a different beta.

Alternative: covariance divided by variance

Beta is also defined as the covariance of the stock and market returns divided by the variance of the market returns. In Excel that is =COVARIANCE.P(D3:D61,E3:E61)/VAR.P(E3:E61). It should match the SLOPE result on the same ranges, so it is a useful cross-check. See Microsoft’s COVARIANCE.P function page for details.

Optional: pull prices with STOCKHISTORY

In Excel for Microsoft 365, the STOCKHISTORY function can fill in the price columns for you. For example, =STOCKHISTORY(“MSFT”,DATE(2021,10,1),DATE(2026,9,30),2) returns the date and closing price for each month in that range. The 2 sets the interval to monthly.

Microsoft’s STOCKHISTORY function page says it requires a Microsoft 365 Personal, Family, Business Standard, or Business Premium subscription. It also notes that some instruments, including some indexes, may not have historical data available. If the index you want returns an error, paste its prices in by hand or use an index fund’s ticker as a stand-in.

Make the model easier to read and reuse

Cell references such as B1 are short, but names are clearer. Select B1, click the Name Box to the left of the formula bar, type RiskFree, and press Enter. Name B2 Beta and B3 MarketReturn the same way. The formula in B4 can then read =RiskFree+Beta*(MarketReturn-RiskFree). Microsoft covers this in Define and use names in formulas.

To compare several stocks, use rows instead. Keep the risk-free rate in B1 and the market return in B2, list tickers from A5 down and their betas from B5 down, then type =$B$1+B5*($B$2-$B$1) in C5 and fill it down. The dollar signs lock the two shared inputs so they do not shift as the formula is copied.

What the result means

The CAPM result is a required return, a hurdle the investment should clear to justify its market risk. It is not a forecast of what the stock will do.

Beta	What it suggests	Expected return (4% risk-free, 9% market)
0.8	Has moved less than the market	8%
1.0	Has moved with the market	9%
1.2	Has moved more than the market	10%
1.5	Has moved much more than the market	11.5%

A common use is to compare the CAPM figure with the return you expect from the investment based on your own research. If your expected return is lower than the CAPM figure, the model says the investment may not pay enough for its risk. Analysts also use the CAPM result as the cost of equity when they value a company.

Keep the limits in mind. Beta is calculated from past prices and changes over time. The market return and risk-free rate are assumptions, and small changes to them move the answer. CAPM also considers only market-wide risk, not risks specific to one company.

Troubleshooting

• The result is far too large, such as 1000%. The rates were typed as whole numbers, for example 4 and 9 instead of 4% and 9%. Retype the rates in B1 and B3 with a percent sign.
• The rate became 400% after formatting. Applying Percent Style to a cell that already holds a number multiplies the display by 100. Retype the value as 4% or 0.04.
• The result shows 0.1 instead of 10%. The value is correct. Apply Percent Style to B4.
• SLOPE returns #N/A. The two ranges contain different numbers of data points or are empty. Make both ranges start and end on the same rows.
• SLOPE returns #DIV/0!. The market returns have no variation, which usually means the range points at blank or identical cells. Check the second range.
• The return formulas show #DIV/0! or #VALUE!. A price cell is empty, zero, or stored as text. Fix the gaps so both columns have a price on every date.
• Beta looks wrong. Confirm the stock and index dates line up row by row, the data runs oldest to newest, and the stock returns are the first range in SLOPE.
• STOCKHISTORY shows #NAME?. Your version of Excel does not include the function. Enter prices manually.

Frequently asked questions

Is there a CAPM function in Excel?

No. Excel has no built-in CAPM function, so you build it from cell references: =B1+B2*(B3-B1), with the risk-free rate in B1, beta in B2, and the market return in B3.

Why does my CAPM result show as a decimal?

Excel shows 0.1 instead of 10% until you apply percentage formatting to the result cell. For more on this, see how to calculate and format percentages in Excel.

Should I use daily, weekly, or monthly returns for beta?

Any of them works in the SLOPE formula as long as the stock and the index use the same dates and interval. Monthly returns over about five years is a common convention. Different intervals and periods produce different betas, so note which one you used.

Can beta be negative?

Yes. A negative beta means the investment has tended to move in the opposite direction to the market. The formula still works, and the expected return comes out below the risk-free rate.

Can the inputs update on their own?

Yes, if the prices or yields come from a data connection or from STOCKHISTORY instead of typed values. See how to make an Excel spreadsheet update automatically.

Does the same formula work in Excel for the web and on a Mac?

Yes. The CAPM formula and the SLOPE function use the same syntax across Excel for Windows, Mac, and the web. Only the ribbon layout and keyboard shortcuts vary.

Next step

Save the workbook, then try changing one input at a time to see how sensitive the expected return is to your assumptions. If your price sheet includes a date column and you want to do more with it, see how to calculate age from a birthdate in Excel for an example of date math.