A Dynamic Spreadsheet Model for Determining the Portfolio Frontier

Designing a spreadsheet model to explain the portfolio theory is a highly effective way of teaching portfolio theory at an MBA level. A few of the well-known textbooks on Investments do include these spreadsheet models in the book itself. These models however assume that the number of stocks one includes in a portfolio is known in advance. Though these models are highly powerful in explaining the basics of portfolio theory, they are not dynamic in the sense if one wants to include or exclude a stock and see how the portfolio frontier changes, then one has to redo the entire exercise (starting from the determination of the variance-covariance matrix) once again. Some financial modeling textbooks suggest the use of VBA to make the portfolio model dynamic. In this paper, we develop a spreadsheet model (without using VBA) that automatically takes care of addition or deletion of stocks and generates the portfolio frontier, the capital market line and the tangent portfolio from the given data