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. There are two 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.
When you change an input, the output area will generally 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 investor's rsk tolerance. 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 three columns indicate the Minimum proportions that may be invested in the items, the Initial portions invested, and the Maximum proportions that may be invested. The initial proportions will usually sum to 1.0 although this is not necessary. Each initial proportion must lie within or at an end of the range given by the minimum and maximum allowed values. The optimizer will consider only portfolios that are feasible (with each proportion greater than or equal to its minimum value and less than or equal to its maximum value) and for which the proportions sum to the same value as the sum of the proportions in the initial portfolio.
The next two columns provide the Expected Returns (ExpRet) and 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 next input box provides the investor's Risk Tolerance. This indicates the marginal rate of substitution of variance for expected return in the investor's utility function. It is normally positive, but a zero value is allowed for the special case in which the objective is to find the feasible portfolio with the smallest possible standard deviation.
The final input box provides the Marginal Utility Cutoff. This is used to terminate the algorithm. When the marginal utility of the best feasible swap falls below this amount, the program stops. The smaller this amount, the more precise the solution but the longer the time required to obtain it.
The worksheet uses the gradient quadratic programming method of Sharpe ["An Algorithm for Portfolio Improvement," , in Advances in Mathematical Programming and Financial Planning, K.D.Lawrence, J.B. Guerard, Jr., and Gary D. Reeves, Editors, JAI Press, Inc., 1987, pp. 155-170] to find the feasible portfolio with the maximum possible Utility:
Up = ep - (sdp/t)
where:
Up = the utility of the portfolio
ep = the portfolio's expected return
sdp = the portfolio's standard deviation of return
t = the investor's risk tolerance
All output is provided in a single box. Two tables are included. The first table shows the Initial Portfolio, the Optimal Portfolio and the Change (Optimal - Initial). The second table shows the characteristics of the Initial and Optimal Portfolio and the associated changes in the level of each one(Optimal - Initial). Unless the risk tolerance is zero, the third line shows the utility of the portfolios and the change therein.
You may enter any desired text in this box to describe the source of the input data, etc..
Go
to Prof. P.V. Viswanath's Home Page
Go
to the FIN 652 Home Page
Go
to the FIN 652 Webnotes Page
Go to Optimization
Program