Portfolio return, expected return and standard deviation

◈ 10 cards

A portfolio’s return is the value-weighted average of its holdings’; an expected return is SUMPRODUCT over a probability table; risk is STDEV.S of the series — and n − 1 is where the S lives.

Portfolio return — a weighted average

A portfolio holds three positions by value: 50 % in Tamarack Foods, 30 % in Lakehead Robotics, 20 % in Cobalt Ridge Mining. This year they return 8 %, 12 % and −4 %. The portfolio's return is each holding's return weighted by its share of the money:

$r_{p} = \sum w_{i} r_{i} = 0.50 (0.08) + 0.30 (0.12) + 0.20 (- 0.04)$

=SUMPRODUCT({0.5,0.3,0.2},{0.08,0.12,-0.04}) = 0.068 — 6.80 %. The weights are value weights and sum to 1; a simple average of the three returns (5.33 %) ignores that half the money was in Tamarack. SUMPRODUCT multiplies two ranges pairwise and adds — it is the Excel verb for every weighted average in this module.

Expected return — the same verb, with probabilities

Before the year, nobody knows the return. An analyst writes three scenarios for Lakehead with probabilities that sum to 1:

Scenario	Probability	Return	p × r
Recession	0.25	−10 %	−0.0250
Normal	0.50	8 %	0.0400
Boom	0.25	25 %	0.0625

$E (r) = \sum p_{i} r_{i}$ =SUMPRODUCT(probs,returns) = 0.0775 — 7.75 %. The probabilities are the weights. The expected return is not any scenario's return; it is the probability-weighted centre of them.

Standard deviation — the risk of a series

Lakehead's last five annual returns: 12 %, −6 %, 9 %, 15 %, 3 %. The mean is =AVERAGE(...) = 6.60 %. Risk in this course is the dispersion of returns around that mean — the standard deviation:

$s = \frac{\sum ( r _{i} - r ˉ ) ^{2}}{n - 1}$

=STDEV.S(B2:B6) = 0.083247 — 8.32 %. The deviations are 5.4, −12.6, 2.4, 8.4, −3.6 points; squared and summed they are 277.2 (in %²); divided by n − 1 = 4 that is 69.3; the square root is 8.32 %. The S in STDEV.S means sample: dividing by n − 1 rather than n corrects for the fact that the five returns are a sample of what the share could do, not the whole population. =STDEV.P(B2:B6) divides by n = 5 and gives 0.074458 — 7.45 %, always smaller. Variance is the square — =VAR.S = 0.00693, in %² — and is not a number in the same units as return; σ is.

Which one the paper wants

A historical series of returns is a sample: use STDEV.S. A question that says population, or that gives a complete probability distribution of scenarios, wants STDEV.P (for a scenario table the probability-weighted version, =SQRT(SUMPRODUCT(probs,(returns-E)^2))). When in doubt, the default for a list of past returns is S.

Recompute

Reweight the portfolio 40 / 40 / 20: =0.4*0.08+0.4*0.12+0.2*-0.04 = 7.20 %. Cold: portfolio return is a weighted average; expected return is SUMPRODUCT of probabilities and returns; risk is STDEV.S, and the S divides by n − 1.

Expected return as SUMPRODUCT. Each row multiplies a probability by a return; the column of products sums to 0.0775. The probabilities are the weights, and they must sum to 1.