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.
  5. Financial Advisor

    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 ...
  5. Market Risk Premium

    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 ...
Trading Center