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. What is the formula for calculating free cash flow in Excel?

    Find out more about free cash flow, the formula for calculating free cash flow and how to calculate a company's free cash ... Read Answer >>
  3. What is the formula for calculating working capital in Excel?

    Find out more about working capital, what working capital measures, and how to calculate a company's working capital using ... Read Answer >>
  4. What is the difference between Macaulay duration and modified duration?

    Find out more about the forward price to earnings ratio and how to calculate the forward price to earnings ratios of companies ... Read Answer >>
  5. 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 >>
  6. 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 >>
Related Articles
  1. 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.
  2. Insights

    Cell Phone Evolution

    Follow the evolution of cell phones from 20 pound bricks to the sleek smart phones of today.
  3. Personal Finance

    6 Tips For Getting A Good Cell Phone Plan

    Avoid overspending on cell service with this expert advice.
  4. 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.
  5. Investing

    If You Had Invested Right After Tesla's IPO

    Find out how much you would have earned if you had invested in Tesla, Inc. right after its initial public offering, or IPO.
  6. Investing

    What to Watch Out for in Tesla's Earnings Today

    It's all about the Model 3.
  7. Investing

    How To Calculate Beta Of A Private Company

    We explain two methods for calculating the beta of a private company.
  8. Personal Finance

    How Cell Phones Have Changed Your Budget

    The cost of owning a cell phone wasn't a factor for people less than two decades ago. Today, there seems to be no end to the additional charges that come from owning a smartphone.
  9. Investing

    Does Tesla Have Enough Cash for Model 3 Production?

    A brief look at how Tesla is spending money on Model 3 production and what this means for investors.
RELATED TERMS
  1. Financial Modeling

    The process by which a firm constructs a financial representation ...
  2. International Beta

    International beta (often known as "global beta") is a measure ...
  3. Stated Annual Interest Rate

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

    The cost of equity is the rate of return required on an investment ...
  5. Abnormal Return

    A term used to describe the returns generated by a given security ...
  6. Risk-Free Asset

    A risk-free asset is an asset which has a certain future return ...
Trading Center