Are your investments too risky? Find out with the Solver tool

by Frank Kuhn

Application:

Microsoft Excel 2000/2002/2003/2004/2007

Operating Systems:

Microsoft Windows, Macintosh

 

 

Knowing which stocks to pick and in what quantities so as to maximize your returns while minimizing your risk is a trick that can baffle even the best of the Wall Street wizards. You can take a giant step toward mastering this trick — even if you don’t have years of experience or education under your belt — by mastering Excel’s Solver tool.

 

To show you how to maximize investment returns while minimizing investment risk, we’ll:

• Set up a worksheet that illustrates the problem facing all investors: how to increase your gains without accruing too much risk.

• Point out the various constraints and variables present in such a problem.

• Introduce you to the Solver tool and use it to unravel this investment riddle.

 

 

Investors want to maximize the overall return while minimizing the overall risk, but they only have so much money to work with. The difficulty lies in the number and complexity of the related variables, constraints, and conditions involved. And, each stock in the portfolio has its own beta risk measure as well as its own expected return. Goal Seek can only handle scenario-testing dealing with one constraint. Luckily, here’s where the Solver tool comes to the rescue.

 

Set up a portfolio worksheet

Most investors have not just one but several investments in their portfolio. Each of these investments has its own respective degree of risk and expected return. Some of these investments offer greater returns than others, but they also likely bring greater risk in the bargain. How do you adjust the weights of these investments so as to optimize and balance both risk and reward?

To illustrate this problem, we’ve set up a sample portfolio spreadsheet, as shown in Figure A. In our sample portfolio, we have three stocks: Stock A, B, and C. You’re investigating Stock X, whose beta is 1.39. It has an expected return of 15.6 percent. You’re interested in this exciting but somewhat risky stock, but you don’t want your portfolio beta to exceed the market beta of 1.0: That is to say, you don’t want your portfolio to be any more risky than the market as a whole. Using risk-free T-bills as a counter-weight to the risky Stock X, how much of Stock X in relation to T-bills should you buy to maximize your portfolio return while maintaining a portfolio beta of 1.0?

 

Download: You can follow along with our example by downloading and extracting the file investment.xls from the URL given at the beginning of this article — or build your own worksheet using ours as a guide.

 

To answer this question, we’ll use Excel’s Solver tool. Before explaining how to use it, however, we’ll first show you how to set up this portfolio worksheet.

 

 

A:

Our portfolio spreadsheet takes into account both risk and reward.

 

To set up a portfolio worksheet:

1. Enter the necessary column headings and formatting, using Figure A as your guide.

2. Apply Percentage formatting to columns C, E, and G, if desired.

3. Enter your security names, the amount invested in each, and their respective returns and betas. Or, use our data to follow along with our example.

4. Enter =SUM(B2:B6) in cell B7.

 

Tip: Don’t forget that you can create SUM formulas in a flash with the AutoSum button autosum, which you can find on the Standard toolbar. Just select a cell, click the AutoSum button, ensure that it selects the cells you want to sum correctly, and then press [Enter].

 

5. Type =B2/$B$7 in cell E2 to determine Stock A’s weight in the portfolio. Press [Enter].

6. Enter =D2*E2 in cell F2 to determine Stock A’s portfolio beta (that stock’s beta as modified by its portfolio weight). Press [Enter].

7. Enter =C2*E2 in cell G2 to determine Stock