Portfolio Models

Crystal Ball is useful for investigating different allocations of investment funds to a set of risky assets. This chapter demonstrates the use of Crystal Ball and OptQuest for determining the optimal allocation of funds in an investment portfolio based on the decision maker’s risk tolerance. We use Crystal Ball and OptQuest to find an optimal allocation for a situation where we know the true optimal allocation, and one where we do not.


In this example, we consider investing in four asset classes: large- and small-company stocks and corporate and government bonds. Figure 9.1 shows a segment of the single-period Crystal Ball model in Portfolio.xls. The spreadsheet contains annual rates of return in percent on the four asset classes. These rates of return were calculated from values listed in the Indices worksheet of Portfolio.xls. The indices were constructed from data collected from various sources for use only in the examples presented in this book. For more specific data on asset returns available to investors during the period 1926 to 2010 (and more), see the Center for Research in Security Prices (, Ibbotson Associates (2011), Bodie, Kane, and Marcus (2011), or the web site (

FIGURE 9.1 Spreadsheet segment from model to simulate a single-period portfolio model.

Overview. ...

Get Financial Modeling with Crystal Ball and Excel, + Website, 2nd Edition now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.