Portfolio Optimisation by Hand: Covariance Tables, Two- and Three-Asset Mixes, Minimum-Variance Portfolios, the Efficient Frontier & the Tangency Portfolio

What you'll build

What you will be able to do

Each lab ends with a concrete result that you build yourself and the checker confirms, cell by cell.

• Turn 24 monthly returns of three fictional asset classes into monthly means and standard deviations, then scale both to annual figures the right way.
• Build the 3 × 3 covariance table from the deviations with SUMPRODUCT, turn it into a correlation table and scale it to annual figures – without any ready-made covariance or correlation function.
• Trace every mix of Shares and Bonds from 0 % to 100 % in 10 % steps, calculate return and volatility for three correlation values and plot the risk–return curve.
• Solve the lowest-risk weight of Shares and Bonds with the closed-form formula, calculate its return and σ, and check the answer against a 5 % weight grid with MIN and INDEX/MATCH.
• Calculate return, variance and σ for a given list of three-asset mixes, writing the nine covariance terms with one SUMPRODUCT per row of the covariance table, and flag the mixes that break the rules.
• Sort 66 three-asset mixes into return bands, find the lowest σ in each band with MINIFS, count which mixes are dominated and plot the efficient frontier.
• Rate all 66 mixes by their Sharpe ratio for a given risk-free rate, find the tangency mix with MAX and INDEX/MATCH, and calculate mixes along the capital allocation line.
• Pick the best capped mix with MAXIFS, repeat the choice under two slightly changed estimates, measure how far the weights move and chart the three answers side by side.