Efficient Frontier in Excel (2 Stocks): Build a Minimum Variance Portfolio Step-by-Step

Описание: In this step-by-step Excel tutorial, you’ll learn how to construct the efficient set (efficient frontier) of a two-asset portfolio using real historical stock data.

We download monthly price data from Yahoo Finance for Canadian Tire (CTC.A) and Loblaw (L.TO), clean the data in Excel, calculate monthly returns, and then compute:

Variance
Covariance
Correlation
Portfolio variance
Portfolio standard deviation
Expected (average) returns

From there, we use Excel’s Data Table (What-If Analysis) to generate portfolio risk across weights from 0% to 100%, identify the minimum variance portfolio, and graph the efficient frontier using a scatter plot of risk vs. return.

This tutorial walks through:

✔ Downloading and cleaning historical data
✔ Using Adjusted Close prices
✔ Calculating monthly returns
✔ Using Excel functions (VAR.S, COVARIANCE.S, CORREL, AVERAGE)
✔ Building the two-asset portfolio variance formula manually
✔ Finding the minimum variance weight
✔ Plotting the efficient frontier

⚠️ Disclaimer
This video is for educational purposes only. The securities discussed (Canadian Tire and Loblaw) are used strictly as examples to demonstrate portfolio construction techniques in Excel. This content does not constitute investment advice, financial advice, or a recommendation to buy or sell any security. Always conduct your own research or consult a qualified financial professional before making investment decisions.