How to calculate the optimal portfolio in Excel: two stocks
About this lesson
In this video, using Microsoft Excel, I will show how to invest in an optimum portfolio, which will maximize the profit and minimize the risk. This concept is also known as efficient diversification in modern portfolio theory.
Diversification in one formula
Combining two stocks gives a portfolio whose risk is usually lower than the average of their risks, because they don’t move perfectly together. That’s efficient diversification, the heart of modern portfolio theory.
With weights w₁ and w₂ (adding up to 1):
Expected return: E(rp) = w₁E(r₁) + w₂E(r₂)
Variance: σp² = w₁²σ₁² + w₂²σ₂² + 2w₁w₂Cov(r₁, r₂)
where Cov(r₁, r₂) = ρ × σ₁ × σ₂, and ρ is the correlation.
The lower the correlation, the bigger the diversification benefit.
Example
| Expected return | Standard deviation | |
|---|---|---|
| Stock 1 | 10% | 20% |
| Stock 2 | 15% | 30% |
Correlation 0.2, so Cov = 0.2 × 0.20 × 0.30 = 0.012. Risk-free rate 4%.
Minimum-variance portfolio
The mix with the lowest possible risk:
w₁ = (σ₂² − Cov) ÷ (σ₁² + σ₂² − 2Cov) = (0.09 − 0.012) ÷ (0.04 + 0.09 − 0.024) = 73.6% in Stock 1.
Optimal risky portfolio (highest Sharpe ratio)
With R as each stock’s excess return over the risk-free rate (6% and 11%):
w₁ = [R₁σ₂² − R₂Cov] ÷ [R₁σ₂² + R₂σ₁² − (R₁ + R₂)Cov] = 52.6%
So 52.6% in Stock 1 and 47.4% in Stock 2:
- Expected return: 12.37%
- Standard deviation: 19.31%
- Sharpe ratio: (12.37 − 4) ÷ 19.31 = 0.43, better than either stock alone (0.30 and 0.37).
In Excel
- Put the weights in two cells, with the second as
=1-B1. - Calculate the portfolio return and standard deviation with the formulas above.
- Calculate the Sharpe ratio.
- Use Data > Solver to maximize the Sharpe ratio cell by changing the weight cell.
Then combine this optimal portfolio with the risk-free asset in the proportion that suits your risk tolerance.
This lesson is for education only and isn’t investment advice.