What is the formula for calculating the capital asset pricing model (CAPM) in Excel?

Find the best broker for your trading or investing needs

Dalio: Bitcoin is not a storehold of wealth

Our network of expert financial advisors field questions from our community.

Are you a financial advisor? Showcase your expertise to 20+ million investors.

Sophisticated content for financial advisors around investment strategies, industry trends, and advisor education.

A celebration of the 100 most influential advisors and their contributions to critical conversations on finance.

The latest markets news, real time quotes, financials and more.

Trade with a starting balance of $100,000 and zero risk!

View the performance of your stock and option holdings

Learn from the worlds leader in financial education

What is the formula for calculating the capital asset pricing model (CAPM) in Excel?

BySteven NickolasUpdated December 12, 2017 6:00 AM EST

Thecapital asset pricing model(CAPM) measures the amount of an assets expected return given the risk-free rate, the beta of the asset and the expected market return. To calculate an assets 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, seeThe Capital Asset Pricing Model: an Overview.)

Assume you want to compare the expected returns between twopublic 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 abetaof 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. Theexpected returnof General Motors is calculated by entering the formula =C2+C3*(C4-C2) into cell C5. General Motors Company has an expected return of 11%.

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

What is the formula for calculating return on equity (ROE) in Excel?

Find out more about return on equity, the formula to calculate it and how to calculate return on equity in Microsoft Excel.Read Answer

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

How do I calculate the cost of equity using Excel?

Learn how to calculate the cost of equity in Microsoft Excel using the capital asset pricing model, or CAPM, including brief …Read Answer

How do I calculate a price to sales ratio using Excel?

Learn what the price to sales ratio is, how to calculate the ratio using a formula, and how to calculate the price to sales …Read Answer

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 companys working capital using …Read Answer

Is Apples 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 …

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.

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

6 Tips For Getting A Good Cell Phone Plan

Avoid overspending on cell service with this expert advice.

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

Tesla Stock Generated Most Returns Among Car Makers This Year

The electric car maker is way ahead of established car majors in generating returns for stockholders.

Calculating Beta: Portfolio Math For The Average Investor

Beta is a useful tool for calculating risk, but the formulas provided online arent specific to you. Learn how to make your own.

Bellicums Technology May Pay Off Big (BLCM)

Bellicums approach augurs well with the successful cancer therapies and offers long-term potential for the company

More Ways to Evaluate Portfolio Performance

The Jensen measure is another tool investors use to include risk when measuring portfolio performance.

A model that describes the relationship between risk and expected …

International Capital Asset Pricing Model (CAPM)

A financial model that extends the concept of the capital asset …

A Protected Cell Company (PCC) is a corporate structure, a single …

Market risk premium is the difference between the expected return …

Better known as global beta, international beta is a measure …

Beta is a measure of the volatility, or systematic risk, of a …

Who Is Roger Ver, aka Bitcoin Jesus?

5 Social Security Changes to Expect in 2018

How the New Tax Law Impacts Cryptocurrencies

Credit Card Issuers Are Charging Fees for Buying Cryptocurrency

The quick ratio measures a companys ability to meet its short-term obligations with its most liquid assets.

Inventory Turnover is a ratio showing how many times a companys inventory is sold and replaced over a period. The days in …

Mutual funds combine money from many investors to invest in a portfolio of stocks, bonds or other securities.

A dividend is a distribution of a portion of a companys earnings, decided by the board of directors, to a class of its shareholders.

A watchlist is list of securities being monitored for potential trading or investing opportunities.

A high-level professional service that combines financial/investment advice, accounting/tax services, retirement planning …