Quadratic programming applications in finance using Excel

The paper describes two applications of quadratic programming in finance, one from the early years (Markowitz’s efficient portfolios with minimum risk) and the other a more recent innovation (Sharpe’s style analysis which estimates an implied asset allocation for an investment fund). We show how, in the presence of inequality constraints, Excel’s Solver can be used to find the optimal weights in both quadratic programming applications. We also implement a direct analytic solution for generating the efficient frontier when there are no inequality constraints using the matrix functions in Excel. Both applications use only a small number of asset classes and require repeated use of the minimisation task. We show how Visual Basic for Applications (Microsoft’s macro language for Excel) can be used to program such tasks, confirming that techniques that were the preserve of dedicated software only a few years ago can now be easily replicated using Excel to solve real problems.