Excel 2010 Bible

Part V: Analyzing Data with Excel

778

Optimizing an investment portfolio

This example demonstrates how to use Solver to help maximize the return on an investment port-

folio. A portfolio consists of several investments, each of which has a different yield. In addition,

you may have some constraints that involve reducing risk and diversification goals. Without such

constraints, a portfolio problem becomes a no-brainer: Put all your money in the investment with

the highest yield.

This example involves a credit union (a financial institution that takes members’ deposits and

invests them in loans to other members, bank CDs, and other types of investments). The credit

union distributes part of the return on these investments to the members in the form of dividends,

or interest on their deposits.

This hypothetical credit union must adhere to some regulations regarding its investments, and the

board of directors has imposed some other restrictions. These regulations and restrictions comprise

the problem’s constraints. Figure 37.15 shows a workbook set up for this problem.

FIGURE 37.15

This worksheet is set up to maximize a credit union’s investments, given some constraints.

On the CD
This workbook is available on the companion CD-ROM. The file is named investment portfolio.xlsx.

The following constraints are the ones to which you must adhere in allocating the $5 million

portfolio:

l (^) The amount that the credit union invests in new-car loans must be at least three times the
amount that the credit union invests in used-car loans. (Used-car loans are riskier invest-
ments.) This constraint is represented as
C5>=C6*3