How to calculate the optimal portfolio in Excel: five stocks
About this lesson
In this video, using five stocks, I will show how to find the optimal portfolio, which will maximize the return and minimize the risk. We will use Microsoft Excel, Excel Solver, and matrix multiplication to compute the variance and covariance matrix.
From two stocks to many
With two stocks, the portfolio variance formula has three terms. With five, it has 25: every pair of stocks has a covariance. Matrices keep this manageable:
σp² = wᵀ Σ w
where w is the column of weights and Σ is the covariance matrix.
Step 1: returns
Download prices for the five stocks over the same dates and calculate each period’s returns, one column per stock (see measuring a stock’s risk in Excel).
Step 2: average returns and the covariance matrix
- Average return for each stock:
=AVERAGE(...) - Covariance matrix (5 × 5):
=COVARIANCE.S(stock i returns, stock j returns)for each pair, or Data > Data Analysis > Covariance. (The Data Analysis tool calculates population covariance, and fills in only half the table; copy the values across the diagonal.)
Step 3: the portfolio
Put five weights in a column (for example H2:H6), and their sum in another cell.
- Portfolio return:
=SUMPRODUCT(H2:H6, average returns) - Portfolio variance:
=MMULT(TRANSPOSE(H2:H6),MMULT(covariance matrix,H2:H6)) - Standard deviation:
=SQRT(variance) - Sharpe ratio:
=(return − risk-free rate)/standard deviation
In Excel 2019 and earlier, confirm the MMULT formula with Ctrl + Shift + Enter.
Step 4: Solver
Turn on Solver under File > Options > Add-ins > Excel Add-ins if you don’t see it, then click Data > Solver:
- Set Objective: the Sharpe ratio cell, Max
- By Changing Variable Cells: the five weights
- Constraint: sum of weights = 1
- To rule out short selling, tick Make Unconstrained Variables Non-Negative
- Solving method: GRG Nonlinear
Solver finds the weights of the optimal risky portfolio. To trace the efficient frontier, run Solver again to minimize the standard deviation for a series of target returns.
Keep in mind
The result depends heavily on the average returns you put in, which are noisy estimates from the past. Small changes in inputs can produce very different weights, so treat the output as a starting point, not a precise answer.
This lesson is for education only and isn’t investment advice.
Next chapter: the efficient market hypothesis.