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.
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%.