A:

The capital asset pricing model (CAPM) measures the amount of an asset's expected return given the risk-free rate, the beta of the asset and the expected market return. To calculate an asset's expected return, subtract the risk-free rate from the expected market return and multiply the resulting value by the beta of the asset. Next, add the risk-free rate to that resulting value. This formula can be calculated in Microsoft Excel. (For more, see The Capital Asset Pricing Model: an Overview.)

Calculating CAPM in Excel

Assume you want to compare the expected returns between two public companies, Tesla Motors Incorporated and General Motors Company. Enter "Tesla Motors Incorporated" into cell B1 and "General Motors Company" into cell C1.

Tesla and GM are car manufacturing companies. Tesla has a beta of 1.16, while GM has a beta of 1.11. Assume the risk-free rate is 0.25% and the expected market return is 10% for the year.

Enter "Risk-Free Rate" into cell A2, "Beta" into cell A3, "Expected Market Return" into cell A4 and "Expected Asset Return" into cell A5.

Enter "0.25%" into cell B2, "=1.17" into cell B3 and "10%" into cell B4. The expected return of Tesla Motors for the year is calculated using the CAPM formula. In cell B5, enter "=B2+B3*(B4-B2)". The resulting expected return of Tesla is 12%.

Next, enter "0.25%" into cell C2, "=1.11" into cell C3 and "10%" into cell C4. The expected return of General Motors is calculated by entering the formula "=C2+C3*(C4-C2)" into cell C5. General Motors Company has an expected return of 11%.

RELATED FAQS
1. How do I calculate the equity risk premium in Excel?

Find out how to calculate the equity risk premium for an individual security using Microsoft Excel, including how to estimate ... Read Answer >>
2. What is the formula for calculating return on assets (ROA) in Excel?

Find out more about the return on assets, or ROA, ratio and the formula for calculating a company's return on assets in Microsoft ... Read Answer >>
3. What is the formula for calculating the price-to-earnings (P/E) ratio in Excel?

Find out more about the price-to-earnings, or P/E, ratio, the P/E ratio formula and how to calculate the P/E ratio in Microsoft ... Read Answer >>
4. How do I calculate the expected return of my portfolio in Excel?

Find out how to calculate the total expected annual return of your portfolio in Microsoft Excel using the value and return ... Read Answer >>
5. What is the formula for calculating the inventory turnover ratio in Excel?

Find out more about inventory turnover ratio and the formula for calculating a company's inventory turnover ratio using Microsoft ... Read Answer >>
6. How do you calculate present value in Excel?

Learn what present value and future value are and how to use Microsoft Excel to calculate present value, given the future ... Read Answer >>
Related Articles
1. Investing

The Capital Asset Pricing Model: an Overview

CAPM helps you determine what return you deserve for putting your money at risk.
2. Tech

CAPM vs. Arbitrage Pricing Theory: How They Differ

Both project the expected rate of return given the level of risk assumed, but they consider different variables.
3. Investing

How AQR Places Bets Against Beta

Learn how the bet against beta strategy is used by a large hedge fund to profit from a pricing anomaly in the stock market caused by high stock prices.
4. Investing

Taking Shots at CAPM

Find out why many investors think the capital asset pricing model is full of holes.

Calculating Beta: Portfolio Math For The Average Investor

Beta is a useful tool for calculating risk, but the formulas provided online aren't specific to you. Learn how to make your own.
6. Insights

Simulating stock prices using excel

Here, we'll use the average of the change in log prices, volatility, normal distribution and Excel to formulate the future prices of an asset. This can amount to a very valuable tool for investors ...
7. Investing

Beta: Know the Risk

Beta says something about measuring price risk in stocks, but how much does it say about fundamental risk factors too?
8. Insights

Tesla Announces Solar Panel Collaboration With Panasonic, Delays New Product Launch (TSLA, SCTY)

Tesla's "unexpected" product launch will have to wait two more days.
RELATED TERMS
1. Financial Modeling

The process by which a firm constructs a financial representation ...
2. International Capital Asset Pricing Model (CAPM)

The international capital asset pricing model (CAPM) is a financial ...
3. Manufacturing Cells

Manufacturing cells are sets of machines that are grouped by ...
4. Stated Annual Interest Rate

A stated annual interest rate is the return on an investment ...

Market risk premium is the difference between the expected return ...
6. Abnormal Return

A term used to describe the returns generated by a given security ...
Hot Definitions
1. Futures Contract

An agreement to buy or sell the underlying commodity or asset at a specific price at a future date.
2. Yield Curve

A yield curve is a line that plots the interest rates, at a set point in time, of bonds having equal credit quality, but ...
3. Portfolio

A portfolio is a grouping of financial assets such as stocks, bonds and cash equivalents, also their mutual, exchange-traded ...
4. Gross Profit

Gross profit is the profit a company makes after deducting the costs of making and selling its products, or the costs of ...
5. Diversification

Diversification is the strategy of investing in a variety of securities in order to lower the risk involved with putting ...
6. Intrinsic Value

Intrinsic value is the perceived or calculated value of a company, including tangible and intangible factors, and may differ ...