8.9: Appendixes

A Statistics Refresher for Risk and Return

This appendix set provides a finance-friendly, practical review of statistical tools used throughout the finance major. The emphasis is not “statistics for its own sake,” but how statistical ideas support real financial decisions—evaluating investments, comparing risk–return tradeoffs, constructing portfolios, estimating market risk, and interpreting results in Excel.

Treat Appendix 8A as a reference library. When you encounter expected returns, standard deviation, covariance/correlation, or portfolio risk in Chapter 8 (and later chapters), return here for reminders, examples, and spreadsheet workflows.

How to Use These Appendices

• Read 8A.1–8A.3 once (probability → expected value → variance/standard deviation).
• Use 8A.4–8A.6 as a repeated reference for covariance/correlation, distributions, and return definitions.
• Use 8A.7–8A.8 for Excel workflow, interpretation, and common pitfalls.
• Use 8A.9 for practice and 8A.10 as a “map back” to Chapter 8.

---

Appendix 8A: Statistics Toolkit for Risk and Return in Finance

8A.1 Probability Foundations for Finance

At the core of finance is uncertainty. Future returns, cash flows, interest rates, and economic conditions are rarely known with certainty. Probability is the language we use to model uncertainty in a structured way.

Discrete vs. Continuous Uncertainty

• Discrete outcomes: a limited number of states (e.g., recession/normal/expansion; low/base/high).
• Continuous outcomes: a range of possibilities (e.g., daily stock returns over time).

Rules of Probability Used in Finance

• Each probability must satisfy: \(0 \le p_s \le 1\).
• Probabilities must sum to 1: \(\sum_{s=1}^{S} p_s = 1\).

Common Student Pitfall

Probabilities are not “rough weights.” If they do not sum to 1, expected values and risk measures will be distorted.

Where Probabilities Come From

• Historical frequency (past patterns)
• Forecasts (macro indicators, analyst outlooks)
• Scenario planning (managerial judgment)

---

8A.2 Expected Value and “Center” of Outcomes

Expected value is a probability-weighted average and represents the statistical “center” of outcomes. In finance, this appears most often as expected return.

Discrete expected value:

\[ E[X] = \sum_{s=1}^{S} p_s X_s \]

Expected return:

\[ E[R] = \sum_{s=1}^{S} p_s R_s \]

Why Expected Return Is Not a Guarantee

• It is a long-run average, not what happens in a single period.
• It is most useful for comparing opportunities and feeding later models (portfolio return, CAPM inputs, valuation assumptions).

Example 8A.2A — Expected Return

Outcome	Probability	Return
Weak	0.30	−5%
Normal	0.50	8%
Strong	0.20	18%

Solution:

\[ E[R] = (0.30)(-0.05) + (0.50)(0.08) + (0.20)(0.18) = 6.1\% \]

Key Insight

Expected return gives the center; risk tools explain how far outcomes can wander away from that center.

---

8A.3 Variance and Standard Deviation

Risk is commonly measured as the dispersion of returns around the expected return. In finance, the most used measures are variance and standard deviation (volatility).

Variance (discrete):

\[ \sigma^2 = \sum_{s=1}^{S} p_s (R_s - E[R])^2 \]

Standard deviation:

\[ \sigma = \sqrt{\sigma^2} \]

Population vs. Sample (Why Excel Has Two Versions)

• Population (rare in finance): use STDEV.P, VAR.P
• Sample (typical in finance with historical returns): use STDEV.S, VAR.S

Example 8A.3A — Variance and Standard Deviation

Using Example 8A.2A where \(E[R]=0.061\):

State	\(p\)	\(R\)	\(R-E[R]\)	\((R-E[R])^2\)	\(p(R-E[R])^2\)
Weak	0.30	−0.05	−0.111	0.012321	0.003696
Normal	0.50	0.08	0.019	0.000361	0.000181
Strong	0.20	0.18	0.119	0.014161	0.002832

Variance: \(\sigma^2 = 0.003696 + 0.000181 + 0.002832 = 0.006709\)

Std dev: \(\sigma = \sqrt{0.006709} \approx 0.0819 = 8.19\%\)

Coefficient of Variation

\[ CV = \frac{\sigma}{E[R]} \]

Common Student Pitfall

Mixing percentages and decimals creates errors of 100×. In calculations, use 0.08 for 8% (not 8).

---

8A.4 Covariance and Correlation

Diversification depends on co-movement. Covariance and correlation capture whether two assets move together (or offset one another).

Covariance (discrete):

\[ \operatorname{Cov}(R_i,R_j)=\sum_{s=1}^{S} p_s (R_{i,s}-E[R_i])(R_{j,s}-E[R_j]) \]

Correlation:

\[ \rho_{ij}=\frac{\operatorname{Cov}(R_i,R_j)}{\sigma_i\sigma_j} \]

• \(\rho\) near +1 → move together → limited diversification
• \(\rho\) near 0 → weak linear link → diversification improves
• \(\rho\) negative → offsetting movement → strongest diversification

Portfolio Preview (Two Assets)

\[ \sigma_p^2 = w_1^2\sigma_1^2 + w_2^2\sigma_2^2 + 2w_1w_2\sigma_1\sigma_2\rho_{12} \]

Key Insight

Diversification is mathematics. Correlation is the lever that determines how much risk reduction is possible.

---

8A.5 Distributions, Normal Approximation, and Interpretation

In quick intuition settings, returns are sometimes approximated as “roughly normal.” Real returns can deviate (fat tails, skewness), but the normal model is a useful baseline.

• About 68% of outcomes fall within \(\pm1\sigma\) of the mean
• About 95% within \(\pm2\sigma\)
• About 99.7% within \(\pm3\sigma\)

Example 8A.5A — Interpreting Volatility

If \(E[R]=10\%\) and \(\sigma=20\%\), then a “typical” range might be around \(10\%\pm20\%\) → roughly −10% to +30%.

Important

“Normal” is a modeling convenience, not a safety guarantee. Finance professionals still use scenarios and stress tests.

---

8A.6 Return Definitions Used in Finance

Simple (Arithmetic) Return

\[ R_t = \frac{P_t - P_{t-1}}{P_{t-1}} = \frac{P_t}{P_{t-1}} - 1 \]

Log Return (Continuously Compounded)

\[ r_t = \ln\left(\frac{P_t}{P_{t-1}}\right) \]

Total Return (Including Dividends)

\[ R_t = \frac{P_t - P_{t-1} + D_t}{P_{t-1}} \]

Most undergraduate finance work uses simple returns. When dividends matter, use total return.

---

8A.7 Excel Toolkit for Risk and Return

The professional standard is a spreadsheet that is auditable: inputs are clear, formulas reference cells (not hard-coded numbers), and outputs are easy to interpret.

Professional Layout

• Inputs: prices, probabilities, assumptions
• Calculations: returns, mean, variance/std dev, covariance/correlation
• Outputs: risk–return metrics and interpretation

Key Excel Functions

• =AVERAGE(range)
• =STDEV.S(range)
• =VAR.S(range)
• =COVARIANCE.S(range1, range2)
• =CORREL(range1, range2)

Workflow: Prices → Returns → Risk Metrics

1. Import prices at a consistent frequency (monthly is common for class projects).
2. Compute returns: =(B3/B2)-1
3. Compute mean: =AVERAGE(C3:C62)
4. Compute volatility: =STDEV.S(C3:C62)
5. Compute correlation: =CORREL(C3:C62, D3:D62)

Tip

Do not compute correlation on prices. Compute returns first. Correlation is about co-movement in returns, not price levels.

---

8A.8 Interpretation and Common Errors

Reasonableness Checks

• Expected return (discrete) should lie between the minimum and maximum outcomes.
• Variance and standard deviation must be non-negative.
• Correlation must be between −1 and +1.

Common Setup Errors

• Using prices instead of returns
• Mixing decimals and percentages
• Mixing frequencies (daily vs monthly vs annual)
• Using population functions instead of sample functions on historical data

---

8A.9 Practice Set (Excel-Friendly)

An asset has returns of −10%, 6%, and 20% with probabilities 0.2, 0.5, and 0.3.

1. Compute \(E[R]\).
2. Compute \(\sigma^2\) and \(\sigma\).
3. Explain what the standard deviation means in finance terms.

Excel hints (not full solutions):

• \(E[R]\): =SUMPRODUCT(prob_range, ret_range)
• Variance: =SUMPRODUCT(prob_range, (ret_range - expected_cell)^2)
• Std dev: =SQRT(variance_cell)

---

8A.10 Map Back to Chapter 8

• Section 8.2 expected return ↔ Appendix 8A.2
• Section 8.2 variance/std dev ↔ Appendix 8A.3
• Section 8.3 covariance/correlation & diversification ↔ Appendix 8A.4
• Section 8.3 spreadsheet workflow ↔ Appendix 8A.7
• Section 8.4 CAPM inputs ↔ Appendix 8A.2, 8A.3, 8A.4

Return to Chapter 8 when ready and proceed into Section 8.3 (portfolio risk) or Section 8.4 (CAPM) as needed.

---

 

Appendix 8B: Excel Modeling Guide for Risk, Correlation, and Portfolio Volatility

Appendix 8B is a hands-on modeling guide. The goal is to help you build a clean spreadsheet model that mirrors what analysts do in professional practice: (1) compute returns from price data, (2) estimate risk and correlation, and (3) compute portfolio risk under different weights and correlation assumptions.

Model Standard

• Separate Inputs, Calculations, and Outputs.
• Use cell references only (no hard-coded numbers inside formulas).
• Label units (monthly vs annual) and format returns consistently.

---

8B.1 Spreadsheet Layout Template

Use three blocks, stacked vertically:

• Inputs block: tickers/labels, dates, prices, assumptions (frequency, risk-free rate if needed later).
• Calculations block: returns, mean, variance, std dev, covariance, correlation, portfolio variance.
• Outputs block: key results and short interpretation (“what does this mean?”).

Suggested columns (monthly example):

• Column A: Date
• Column B: Stock A Price
• Column C: Stock A Return
• Column D: Stock B Price
• Column E: Stock B Return

---

8B.2 Returns from Prices (Setup)

If Stock A prices begin in B2, then the first return can be computed in C3:

• C3 = (B3/B2)-1

Copy down for the full sample. Repeat for Stock B (e.g., price in D, return in E).

Common Student Pitfall

Do not compute volatility or correlation on price levels. Always compute returns first, then risk measures on the return series.

---

8B.3 Estimating Mean, Variance, and Standard Deviation

Sample mean: =AVERAGE(C3:C62)

Sample variance: =VAR.S(C3:C62)

Sample standard deviation: =STDEV.S(C3:C62)

If you want to annualize a monthly standard deviation (common in finance), a standard approximation is:

\[ \sigma_{\text{annual}} \approx \sigma_{\text{monthly}} \sqrt{12} \]

(Annualizing is optional depending on your course expectations. Always state which frequency you are using.)

---

8B.4 Estimating Covariance and Correlation

If Stock A returns are in C3:C62 and Stock B returns are in E3:E62:

• Covariance: =COVARIANCE.S(C3:C62, E3:E62)
• Correlation: =CORREL(C3:C62, E3:E62)

Correlation is usually the easiest statistic to interpret. Covariance is useful because it appears directly in portfolio variance formulas.

---

8B.5 Portfolio Volatility (Two Assets)

Once you have \(\sigma_1\), \(\sigma_2\), and \(\rho_{12}\), you can compute portfolio variance:

\[ \sigma_p^2 = w_1^2\sigma_1^2 + w_2^2\sigma_2^2 + 2w_1w_2\sigma_1\sigma_2\rho_{12} \]

A clean Excel setup uses named cells:

• w1, w2
• sigma1, sigma2
• rho

Excel (variance):

=w1^2*sigma1^2 + w2^2*sigma2^2 + 2*w1*w2*sigma1*sigma2*rho

Excel (std dev):

=SQRT(portfolio_variance_cell)

Tip

Keep weights in a dedicated input block and verify \(w_1 + w_2 = 1\). Most portfolio spreadsheet errors begin with weights that do not sum to 1.

---

8B.6 Scenario Table: “What If Correlation Changes?”

A powerful learning tool is to compute \(\sigma_p\) across different correlations. Create a column of \(\rho\) values (e.g., +1, +0.5, 0, −0.5, −1) and compute portfolio volatility for each.

Interpretation goal: Show that lower correlation reduces portfolio risk even when individual asset volatility does not change.

---

8B.7 Mini-Case Spreadsheet Setup Guide

If you are working a mini-case (a small dataset plus questions), set up your spreadsheet so it is easy to follow and easy to audit. Use the same structure throughout: Inputs, Calculations, and Outputs.

• Inputs: Enter the given data (probabilities, returns, prices, weights, correlation) exactly as provided.
• Calculations: Use cell references (not hard-coded numbers) to compute expected return, variance, standard deviation, covariance/correlation, and portfolio risk (if required).
• Outputs: Display your key results clearly and add 2–4 sentences interpreting what the results mean.

Spreadsheet Setup Checklist

• Probabilities sum to 1 (if using states).
• Weights sum to 1 (if building a portfolio).
• Correlation stays between −1 and +1.
• Returns are computed from prices before volatility/correlation (if using real data).
• Numbers are entered as decimals in formulas (0.08 for 8%).

This structure creates a repeatable workflow you can use across finance courses and professional internship projects.

---

 

Appendix 8C: Finance Data Sources, Documentation Standards, and Data Ethics

Risk and return analysis is only as good as the data behind it. Appendix 8C provides a practical guide to selecting credible sources, documenting assumptions, and avoiding common data errors in finance.

---

8C.1 Common Data Sources Used in Finance Coursework

• FRED (Federal Reserve Bank of St. Louis): macroeconomic series, rates, market indexes, inflation measures.
• Yahoo Finance: historical prices (daily/monthly), corporate actions (splits/dividends).
• SEC EDGAR: filings (10-K/10-Q) for accounting-based analysis.
• Company investor relations pages: earnings releases, presentations.

In FIN 3400, the goal is credibility and repeatability: any student should be able to pull the same series and replicate results.

---

8C.2 “Adjusted Close” vs. “Close” (Why It Matters)

When pulling historical stock data, you may see “Close” and “Adjusted Close.” Adjusted Close attempts to account for dividends and splits. If your goal is total return, adjusted prices are often more appropriate than raw closes.

Common Student Pitfall

Students compute returns on non-adjusted prices and then compare to “total return” discussions. If dividends/splits matter, your return definition and your data fields must match.

---

8C.3 Frequency Choice: Daily vs Monthly vs Annual

• Daily: more observations, more noise; often used in advanced analytics.
• Monthly: common for learning and many class projects; smoother patterns.
• Annual: small sample sizes; not ideal for estimating correlation reliably.

State your frequency clearly. If you annualize monthly volatility, document your method (e.g., multiply by \(\sqrt{12}\)).

---

8C.4 Documentation Standards (What “Good Work” Looks Like)

Finance majors should be able to answer the following for any analysis:

• What is the data source and series name?
• What is the time window (start and end dates)?
• What frequency is used (daily/monthly)?
• How are returns defined (simple/log; adjusted/not)?
• Which Excel functions were used to compute risk and correlation?

In professional settings, reproducibility is part of credibility.

---

8C.5 Data Ethics and Responsible Use

• Transparency: Don’t hide assumptions. Document them.
• Privacy: Do not use non-public or sensitive personal data in class projects.
• Integrity: Do not “cherry-pick” time windows to force a conclusion.
• Verification: If results look extreme, double-check units and formulas before writing interpretations.

Professional Mindset

The spreadsheet is not the analysis. The analysis is the logic, documentation, and interpretation that makes the spreadsheet defensible.

---

 

Appendix 8D: Study and Exam Toolkit for Risk, Return, and Diversification

Appendix 8D provides a structured checklist and practice framework for mastering risk and return concepts at the 3000-level standard: correct computation, correct setup, and clear interpretation.

---

8D.1 Master Checklist (Before You Submit Any Work)

• Have I used decimals in calculations (0.08) and formatted results as %?
• Do my probabilities sum to 1 (if using discrete states)?
• Does expected return fall between min and max outcomes (discrete check)?
• Did I compute returns from prices before volatility/correlation?
• Did I use sample functions (STDEV.S, VAR.S) for historical returns?
• Is correlation between −1 and +1?
• If portfolio weights are used, do weights sum to 1?

---

8D.2 “Explain It Like an Analyst” Sentence Starters

• “Asset A has higher expected return, but it also has higher volatility, which implies …”
• “Asset B has a lower coefficient of variation, meaning it delivers … per unit of expected return.”
• “Correlation is strongly positive, so diversification benefits are … because …”
• “Lower correlation reduces portfolio volatility even if individual volatilities remain the same because …”

---

8D.3 Practice Set (Short, High-Value)

1. Compute expected return for a 3-state investment.
2. Compute variance and standard deviation for the same investment.
3. Compute correlation between two return series in Excel.
4. Compute two-asset portfolio volatility for \(\rho = +1\), \(0\), and \(-0.5\) with the same weights.
5. Write a 4–6 sentence interpretation comparing risk, return, and diversification.

Tip

On exams, the highest scores come from students who show a clean setup and then explain the result in plain language. The math earns points, but the interpretation earns the distinction.