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 how long it takes an investment to double (AKA 'The Rule of 72') ...

    Find out more about the rule of 72, what the rule of 72 measures and how to calculate the rule of 72 for investments using ... Read Answer >>
  2. How do you calculate return on equity (ROE) in Excel?

    Find out more about return on equity, the formula to calculate ROE and how to calculate this measure of a company's profitability ... Read Answer >>
  3. 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 >>
  4. How do I calculate stock value using the Gordon Grown Model in Excel?

    Learn about the Gordon Growth Model, also known as the dividend discount model. See how to use Microsoft Excel to determine ... Read Answer >>
  5. How do I calculate fixed asset depreciation using Excel?

    Learn what depreciation is and how to use Excel to calculate depreciation for fixed assets with the straight line and sum ... Read Answer >>
  6. How do you use Microsoft Excel to calculate liquidity ratios?

    Learn how to calculate the most common liquidity ratios in Microsoft Excel by inputting financial figures from a company's ... Read Answer >>
Related Articles
  1. Investing

    Capital Asset Pricing (CAPM) Model: Pros and Cons

    CAPM, while criticized for its unrealistic assumptions, provides a more useful outcome than either the DDM or WACC in many situations.
  2. Investing

    Is Apple's Stock Over Valued Or Undervalued?

    Despite several drawbacks, the CAPM gives an overview of the level of return that investors should expect for bearing only systematic risk. Applying Apple, we get annual expected return of about ...
  3. 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.
  4. Investing

    Taking Shots at CAPM

    Find out why many investors think the capital asset pricing model is full of holes.
  5. 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 ...
  6. Investing

    Catch on to the CCAPM

    The consumption capital asset pricing model smoothes over some of CAPM's weaknesses.
  7. Investing

    How to calculate required rate of return

    The required rate of return is used by investors and corporate-finance professionals to evaluate investments. In this article, we explore the various ways it can be calculated and put to use.
  8. Investing

    Toyota Plans 100 Fuel Cell Buses by 2020 (TM)

    With field tests confirmed, Toyota is planning to produce over 100 fuel cell buses for the Tokyo metropolitan area before the 2020 Olympic Games.
  9. Investing

    What to Watch Out for in Tesla's Earnings Today

    It's all about the Model 3.
RELATED TERMS
  1. Capital Asset Pricing Model - CAPM

    Capital Asset Pricing Model is a model that describes the relationship ...
  2. Work Cell

    A work cell is an arrangement of resources in a manufacturing ...
  3. Risk-Free Return

    Risk-free return is the theoretical return attributed to an investment ...
  4. Stated Annual Interest Rate

    A stated annual interest rate is the return on an investment ...
  5. Cost Of Equity

    The cost of equity is the rate of return required on an investment ...
  6. Market Risk Premium

    Market risk premium is the difference between the expected return ...
Trading Center