How to measure the risk of Apple Inc. in Excel

What you need

Historical prices for the stock, such as Apple (AAPL), and for a market index such as the S&P 500. Free sources such as Yahoo Finance let you download daily, weekly or monthly prices as a CSV file. Use the adjusted close, which accounts for dividends and stock splits.

Step 1: calculate the returns

With dates in column A and adjusted prices in column B (oldest first), the return for each period is:

=B3/B2-1 in C3, then fill down.

Step 2: average return and standard deviation

• Average return: =AVERAGE(C3:C62)
• Standard deviation: =STDEV.S(C3:C62)

The standard deviation is the stock’s total risk, or volatility.

Step 3: annualize

If you used monthly returns:

• Annual average return ≈ monthly average × 12
• Annual standard deviation ≈ monthly standard deviation × √12, in Excel =STDEV.S(C3:C62)*SQRT(12)

For daily returns, use about 252 trading days instead of 12.

Step 4: compare with the market (beta)

Put the index returns for the same dates in column D. Beta measures how much the stock tends to move when the market moves:

=SLOPE(C3:C62,D3:D62)

• Beta = 1: moves with the market on average.
• Beta > 1: tends to move more than the market.
• Beta < 1: tends to move less.

Beta measures systematic (market) risk, the part that diversification can’t remove. You’ll use it in the CAPM.

Keep in mind

• Results change with the period and frequency you choose, so state them when you report the numbers.
• Past volatility is a guide to future risk, not a promise.

This lesson is for education only and isn’t investment advice.

Next chapter: free cash flow.