Advanced Modelling in Finance Using Excel and VBA

Chapter 6. Portfolio Optimisation

In this chapter, we look at a single individual investor and explore how he can solve the problem of portfolio optimisation. The background theory and many practical examples are set out in Bodie et al. (1996, Chapters 6 and 7). We concentrate on spreadsheet implementation of calculation procedures, and include an exploration of the underlying generic portfolio problem types. For establishing the weights for risky portfolios, the main workhorse is Excel's Solver, whereas for the allocation between risky and risk-free assets in the generic portfolio problems, user-defined functions for spreadsheet formulas are preferable. Sections 6.1 and 6.2 cover important preliminaries for risky portfolios. Sections 6.3 to 6.5 concentrate on determining optimal portfolio weights. In section 6.6, the risk–return trade-off and risk aversion are introduced with the ensuing sections 6.7 to 6.9 applying spreadsheets to the generic portfolio problems. The chapter concludes with fuller details on the user-defined functions and macros developed to assist portfolio analysis in the associated workbook, EQUITY1.xls.

PORTFOLIO MEAN AND VARIANCE

The mean–variance model of portfolio choice is well described and explained in finance textbooks (for example Bodie et al., 1996, Chapter 7 on Optimal Risky Portfolios). Markowitz reduced the optimisation problem to that of finding mean–variance efficient portfolios, with efficient points having the highest expected return for a given ...