How To Graph A Security Market Line

The security market line (SML) is a graphical representation of the capital asset pricing model (CAPM), a basic estimate of the relationship between risk and return in a stock price. By estimating the SML and comparing it to actual historical returns of a stock, an investor can get a sense of whether the stock is undervalued or overvalued, based on the investor's assumptions about future performance. If returns are consistently beneath the SML line, that stock is expected to rise, and if the returns are consistently above the line, that stock is due for a drop. Graphing the SML for a particular stock requires a calculation of the stock's "beta," which is how closely it adheres to the overall performance of the market. A stock with a beta higher than 1 outperforms the market, and a beta less than 1 means it underperforms the market.

Step 1

Open Microsoft Excel or other similar calculating spreadsheet software.

Step 2

List all of the months for which you have returns data in Column A. The more months you have, the more solid your estimate will be. Five years is a standard number for significant certainty.

Step 3

Type the returns, expressed as a decimal, in Column B. For example, a return of 12 percent should be typed as "0.12" in the cell.

Step 4

Type the returns of the overall stock market in Column C. Choose an index that is most relevant to your security to represent the whole market. For example, for a stock listed on the Dow Jones Industrial Average, use the Dow Jones Industrial Average.

Step 5

Type the following formula in Cell D1: "=COVAR(B1:BXX,$C$1:$C$XX)COUNT(B1:BXX)/((COUNT(B1:BXX)-1)VAR($C1:$CXX))" Replace "XX" throughout with the number of the row where your monthly returns data ends, which will be row 60 if you used exactly five years of data. The result of this cell will be your security's beta.

Step 1

Make an estimate of the risk-free rate of return and type it in cell E2. This is the return that you would earn if you invested in a "risk-free" security, such as a U.S. Treasury Bill. Keep in mind that "risk-free" means an extremely small amount of risk, as no investment can ever be completely without risk. For the purpose of this example, use the risk-free rate of 3 percent (0.03).

Step 2

Make an estimate of future market return and type it in Cell E3. This can be based on past market return by using the data from the beta calculation or just an educated guess based on your knowledge of the market and economy. For the purpose of this example, use the expected market return of 8 percent (0.08).

Step 3

Type the number 0 in Cell D2 and the number 1 in Cell D3. These represent the beta of risk-free investment and total market investment, respectively. By definition, investing without risk always carries a beta of zero, and investing in the entire market always carries a beta of one.

Step 4

Type the following formula in E1: "=(E3-E2)*D1". This generates the slope of the SML line and multiplies it by your stock's beta to generate the expected return of the stock.

Step 5

Highlight the cells from E1 to E3, then choose the "Chart" menu and click on "Line." This creates a security market line with returns on the Y-axis and beta on the X-axis.