Instructions for Reverse Optimization Worksheet

General Instructions

This worksheet has been designed and tested using Netscape 3.0. It should work with later versions of Netscape's browsers but will not work with other browsers. It uses only JavaScript for computations and should cause no problems when used with the appropriate browsers. The operative word here is "should" -- the author cannot guarantee fault-free operation.

You should be able to use the worksheet when not connected to the internet. Save the program file (ws_***.htm) and the accompanying instruction file (wi_***.htm) on your disk using the browser's command to File Save. At a later time you may retrieve the file using the browser's File Open file command; you may then use the page as you would if you were on the network.

When using the worksheet, you may change any inputs. To do so, click inside the appropriate box, then make your changes. When finished, click any area outside the boxes on the form.

You may copy inputs from other sources such as spreadsheets, word processing documents, and other worksheets in this series, then paste the results into the appropriate boxes on this form. To copy an area from an Excel spreadsheet, select it, then select Edit Copy. In the browser, select a position in the input box, then select Edit Paste. To copy an area from a box in the browser to an Excel spreadsheet, select the text in the browser and select Edit Copy. In the Excel spreadsheet, select a position, then select Edit Paste. This brings each row into the spreadsheet as text. To convert to a matrix, select the column in which the information is located (the left-most one shown), then select Data Text-to-columns. Choose Delimited and Spaces as delimiters and the information will appear in the requisite number of cells.

When you save a page on your own disk (using the browser's File Save As command), only the original material in the form will be saved. To overcome this, you may click the MAKE RECORD button. This will create a new page with the relevant inputs and outputs from your most recent case. You may print this or save it on your own disk. At a later time you may open this file in your browser and copy information from it to a regular worksheet, if you wish. When you are through with the record page, choose either the File Close command or click the X in the upper right corner (or equivalent on your platform) to return to the worksheet.

There are two other ways to save and retrieve worksheet information. You can copy the information you wish to save to some other document, such as a spreadsheet, word processing document or text file. You can also load the source (ws_***.htm) file in a word processor and edit it to include your inputs. You will find the default information in blocks marked TEXTAREA and in the VALUE attributes of INPUT tags. Simply replace the default values with your information, then save the page as a file on your disk under any desired name.

Whenever you change an input, the output area will be cleared to avoid having old outputs appear simultaneusly with new inputs. To produce new outputs, click the PROCESS button.


The Inputs box should contain all the needed input information other than the expected returns for two asset classes. Each row provides the inputs for a single decision variable (for example, an asset class). The columns must be in the order indicated below. Each must be given a short heading, although these are not actually checked for content.

The first column indicates the proportions invested in the assets for a portfolio that is assumed to be efficient -- that is, to maximize utility for some risk tolerance. These proportions will usually sum to 1.0 although this is not necessary.

The next column provides the Standard Deviations (StdDev) for the decision variables. These are usually stated in terms of return per year (for example, 10.5 for 10.5% per year). The remaining columns provide the Correlation Coefficients for the variables. The order must be the same as that used for the rows in the table.

The other input boxes provides the Expected Returns for two selected asset classes (decision variables). Any two may be chosen and the expected returns must differ.

You may choose either of two output formats. The first provides only the computed expected returns. The second provides all the information in the format used for inputs in the optimization worksheet.



The worksheet uses the method described in Sharpe ["Imputing Expected Returns From Portfolio Composition," Journal of Financial and Quantitative Analysis, June 1974, pp. 463-472] to find a full set of expected returns compatible with the inputs. If a unique solution cannot be obtained, an error message is shown.


The major outputs are provided in a single box. A separate box shows the risk tolerance for which the given portfolio is optimal.



You may enter any desired text in this box to describe the source of the input data, etc..