Three stocks. One market.
What does risk earn?
Use Apple (AAPL), Walmart (WMT), NVIDIA (NVDA), and the S&P 500 to calculate returns, measure risk, build portfolios, and evaluate CAPM.
Open the workbook in Excel and save your working copy. Complete your calculations before opening the check results below.
Start: understand the data
The workbook has A: Date · B: AAPL · C: WMT · D: NVDA · E: SP500. Row 1 contains headers; rows 2–61 contain September 2021–August 2026.
Date labels: 9/1/2021 represents September’s month-end close, not the September 1 price. All series use the same monthly labeling convention.
Sources: S&P 500 from FRED, aggregated to Monthly → End of Period; stock prices supplied through the stock-data tool.
This lab uses supplied closing prices and a price index; it does not add dividends. Verify split adjustments before using another price source.
View the supplied prices
| Date label | AAPL | WMT | NVDA | SP500 |
|---|
1. Calculate monthly returns
Clear the existing source notes in G1:G7 first; the same source information is provided above. Keep the price data in A:E.
Enter AAPL return, WMT return, NVDA return, Market return in cells F1:I1. Leave row 2 blank: the first price has no preceding price in this sample.
| Cell | Excel formula | Meaning |
|---|---|---|
| F3 | =B3/B2-1 | AAPL’s October 2021 return |
| G3 | =C3/C2-1 | WMT return |
| H3 | =D3/D2-1 | NVDA return |
| I3 | =E3/E2-1 | S&P 500 return |
Select F3:I3 and fill down through row 61. Format F3:I61 as percentages with two decimal places.
Quick check: AAPL’s October return should be approximately 5.87%. Explain why the return is labeled October even though it uses two months’ prices.
2. Calculate the mean and standard deviation
Create this summary in K1:N7. Enter AAPL, WMT, NVDA in L1:N1. Put the metric labels in column K.
| Row / metric | AAPL formula in column L |
|---|---|
| 2 · Mean monthly return | =AVERAGE(F3:F61) |
| 3 · Monthly standard deviation | =STDEV.S(F3:F61) |
| 4 · Beta | Complete in Step 4 |
| 5 · Annualized historical mean | =12*L2 |
| 6 · Annual CAPM return | Complete in Step 5 |
| 7 · Historical mean minus CAPM | Complete in Step 7 |
Copy L2:L3 across to M2:N3. Copy L5 across to N5. Format return and risk results as percentages; beta will be an ordinary number.
Discuss: Which stock had the highest average return? Which had the greatest total risk? Does the highest-return stock always have the lowest risk?
The annualized arithmetic mean is 12 × the monthly mean. It is not a compounded annual growth rate (CAGR). Optional annualized volatility: =L3*SQRT(12).
3. Calculate correlations between stocks
| Pair | Excel formula |
|---|---|
| AAPL and WMT | =CORREL(F3:F61,G3:G61) |
| AAPL and NVDA | =CORREL(F3:F61,H3:H61) |
| WMT and NVDA | =CORREL(G3:G61,H3:H61) |
Put these results in an unused area, such as K13:L16. Correlation runs from −1 to +1; it is not a percentage.
Discuss: Which pair has the lowest correlation? Why can combining stocks reduce risk? Low correlation helps diversification, but portfolio risk also depends on the stocks’ individual volatility and their weights.
4. Estimate each stock’s beta
Beta measures sensitivity to the market, rather than total volatility. Use monthly returns—not price levels.
In L4 enter:
=SLOPE(F3:F61,$I$3:$I$61)Copy across to N4. The stock returns are the Y values and market returns are the X values.
Verify AAPL’s beta independently:
=COVARIANCE.S(F3:F61,$I$3:$I$61)/VAR.S($I$3:$I$61)Discuss: Interpret beta below 1, equal to 1, and above 1. Is the stock with the highest standard deviation necessarily the stock with the highest beta?
5. Calculate the annual CAPM return
Enter Annual risk-free rate in K10 and Expected annual market return in K11. Enter 4% in L10 and 10% in L11.
CAPM required return = risk-free rate + beta × (expected market return − risk-free rate).
In L6 enter and copy across to N6:
=$L$10+L4*($L$11-$L$10)Discuss: Why does a higher beta imply a higher CAPM required return when the market risk premium is positive? Why does CAPM price systematic risk rather than all standard deviation?
6. Build portfolios with different weights
Enter AAPL, WMT, NVDA in L17:N17 and weights in L18:N18. Start with 40%, 40%, 20%. In O18 enter =SUM(L18:N18) and confirm the total is 100%.
Enter Portfolio return in J1. In J3 enter:
=SUMPRODUCT(F3:H3,$L$18:$N$18)Fill down through J61. Then calculate:
| Result | Cell | Formula |
|---|---|---|
| Mean monthly portfolio return | L20 | =AVERAGE(J3:J61) |
| Monthly portfolio standard deviation | L21 | =STDEV.S(J3:J61) |
| Portfolio beta | L22 | =SUMPRODUCT(L4:N4,L18:N18) |
| Annual CAPM portfolio return | L23 | =$L$10+L22*($L$11-$L$10) |
Check portfolio beta with =SLOPE(J3:J61,I3:I61). The two beta methods should agree, apart from rounding.
Try equal weights (enter =1/3 in each weight cell), 20% / 60% / 20%, and 20% / 20% / 60%. Record each portfolio’s return, standard deviation, beta, and CAPM return before changing weights.
Discuss: Which mix reduces risk most? Do not calculate portfolio standard deviation by taking the weighted average of individual standard deviations.
This exercise assumes constant weights, rebalanced each month, without fees or taxes. It is not a buy-and-hold portfolio simulation.
7. Draw the SML and compare historical returns
In L7 enter =L5-L6 and copy across to N7. This is the annualized historical mean minus the annual CAPM benchmark.
Create a scatter-chart table in K26:M29:
| Row | K · Asset | L · Beta | M · Annualized historical mean |
|---|---|---|---|
| 27 | AAPL | =L4 | =L5 |
| 28 | WMT | =M4 | =M5 |
| 29 | NVDA | =N4 | =N5 |
Select L26:M29 → Insert → Scatter → Markers only. Label the X axis Beta and the Y axis Annual return (%). Add stock names as data labels from K27:K29.
Create two SML points: K32 = “Beta,” L32 = “CAPM return”; K33 = 0; K34 = =MAX(3,MAX(L4:N4)+0.5). In L33 enter =$L$10+K33*($L$11-$L$10) and fill down to L34.
Right-click the chart → Select Data → Add. Name the series “SML,” set X values to K33:K34 and Y values to L33:L34. Format the SML series as a straight line; keep the stock series as separate markers.
| Position | If the return were a credible expected future return | What these historical data establish |
|---|---|---|
| Above SML | Return exceeds CAPM required return; potentially underpriced. | Positive historical return gap relative to the chosen CAPM benchmark. |
| On SML | Return matches CAPM required return; fairly priced under the model. | Historical mean is close to the benchmark. |
| Below SML | Return falls short of CAPM required return; potentially overpriced. | Negative historical return gap relative to the chosen benchmark. |
Write your conclusion: “_____ was above/below the SML by _____ percentage points using our assumptions. This indicates _____ historical performance relative to the benchmark. Before calling it a bargain or overpriced today, I would need _____.”
Use annual quantities on both axes’ return comparisons. Do not compare a monthly mean directly with an annual CAPM return. These are price returns; using total returns could change the estimates.
After Excel: check your results and explore
Show check results and the interactive SML
Checks use the 59 common monthly price returns through August 2026. Complete your workbook first, then compare.
| Asset | Monthly mean | Monthly SD | Beta | Annual historical mean | Annual CAPM | Gap (pp) |
|---|
Stock dots show annualized historical arithmetic means. The SML shows annual CAPM required returns using your assumptions. The portfolio dot updates when weights sum to 100%.
Submit your Excel workbook on Blackboard
This is individual work. Submit your completed Excel workbook on Blackboard for 5 extra-credit points added to either your first or second midterm exam.
- 59 monthly returns per asset; formulas visible in the formula bar.
- Mean, standard deviation, three pairwise correlations, and stock betas.
- CAPM assumptions and annual required returns.
- Results for at least three portfolio weight combinations that sum to 100%.
- SML scatter chart and a short explanation of above/below-SML results and the limits of a bargain conclusion.