Constructing a Minimum-Variance Portfolio and Efficient Frontier in Excel

Summary

The document asks how to use five years of weekly returns for three asset classes to find a minimum-variance portfolio and plot an efficient frontier in Excel. It notes that a simple two-asset weight table becomes cumbersome as the number of assets grows, and asks whether simulation can simplify the search. The accepted response points to elementary linear algebra and spreadsheet Solver as practical ways to build the optimization, with external spreadsheet examples offered as references.

The reply says Modern Portfolio Theory assumes normally distributed returns. It does not explain the optimization steps, specify constraints such as short-selling limits, or show how to estimate expected returns and the covariance matrix from the historical sample. Nor does it compare a Monte Carlo search with Solver or provide an example frontier. The exchange is therefore a brief pointer to a standard framework, not a worked implementation; the distributional assumption and the use of historical returns remain modeling choices rather than evidence that future returns will follow the same pattern.

Key ideas

• A minimum-variance portfolio and efficient frontier can be constructed using linear algebra and spreadsheet optimization.
• Solver is suggested as an alternative to manually listing many asset-weight combinations.
• The response identifies normal returns as an assumption in the stated Modern Portfolio Theory framework.
• The exchange does not specify portfolio constraints or demonstrate an optimization procedure.
• Historical estimates and distribution assumptions do not guarantee future portfolio behavior.

Tags

# Portfolio Optimization with Monte Carlo Simulation - How to do it with Excel?


# Portfolio Optimization with Monte Carlo Simulation - How to do it with Excel?












If I have three asset classes and their historical weekly returns for five years, how can I construct a minimum variance portfolio and an efficient frontier plot with Excel? To do that do I have to assume the return is normally distributed?

Update: there's a host of tutorials to plot the frontier for two assets as long as I have a table of say 10 possible weights of one asset. But with three assets or more plotting would be challenging as I have to come up with a much bigger table for the combination of the weights of these assets. As such I was wondering if there is some kind of simulation algorithm or any techniques to make it easier.

## Answer by rrg (score 1, accepted)

https://quant.stackexchange.com/a/31387

Yes, the assumption in MPT is normal distribution for returns.

You can programme yourself in R or Excel, following elementary linear algebra.

Eric Zivot (U Wash) has a spreadsheet solution here:

- https://faculty.washington.edu/ezivot/econ424/solverex.pdf

- https://faculty.washington.edu/ezivot/econ424/Efficient%20Portfolios%20in%20Excel%20Using%20the%20Solver%20and%20Matrix%20Algebra.pdf

Shown in full with attribution under the source's licence. Licence: CC BY-SA 4.0 (Stack Exchange)

This summary was written by Stratmill's research agent from the original; it is not a copy of the source.