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. There are two ways to save and retrieve this 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 simultaneously with new inputs. To produce new outputs, click the PROCESS button.
The Inputs box should contain the data observations, with each observation in a separate row. For each observation the first column contains the month (preferably in form YYYYMM); it is not used in the computations. The remaining entries in each row give the monthly returns (as percentages -- thus 2.34 for 2.34% in that month).All returns must be separated by one or more spaces and/or tabs. The top row should contain column identifiers for the return series. Each should use six or fewer characters (of any type), but no spaces. Blank rows may be included in the table and it is not necessary for your data to "line up" in columns as long as it conforms to the rules given above.
The first series should give the returns a one-month riskless asset. The second should give the returns on a benchmark portfolio. The remaining series should give the returns on one or more funds for which historic performance is to be evaluated.
The Risk Tolerance parameter is used in the computation of mean-variance utility measures (described below). The Relative Disutility parameter is used An be used in the computation of linear utility measures (also described below).
The output is in the form of a table with a columns for the benchmark portfolio and all the funds for which returns were provided. All statistics are based on the historic returns provided, with all observations weighted equally.
The first row provides cumulative returns, based on the ratio of the ending value to the beginning value, assuming that returns are compounded throughout the period. Thus a cumulative return of 50% would indicate that $1 invested at the beginning of the period would have grown to $1.50 by the end of the period.
The block labeled Monthly Rtn contains statistics computed from the monthly total returns, with the results expressed in terms of return per month. The first is the mean monthly return, computed by summing all the monthly returns and then dividing by the number of months. The second statistic gives the monthly standard deviation of return. This is computed by averaging the squared differences of the monthly returns from their mean, then taking the square root. No correction is made for degrees of freedom, so this can be considered as a "population" standard deviation. The third statistic is the geometric mean monthly return. This is the monthly return that, if earned every month, would compound to give the same cumulative value as did the investment in question.
The block labeled Annualized Rtn contains annualized versions of the statistics in the first block. The annualized mean monthly return is simply the mean monthly return times 12. The annualized monthly standard deviation of return equals the monthly standard deviation of return times the square root of 12. The annualized geometric mean return is that return that, if earned every year, would compound to give the same cumulative value as did the investment in question. More precisely, if 1+ga raised to the number of years covered will equal one plus the cumulative return.
The Monthly ER block contains statistics computed using monthly excess returns. The excess return for a benchmark or fund in a given month equals its return minus that of the risk-free asset. The first two statistics for an investment provide its mean monthly excess return, computed by summing all the monthly excess returns and then dividing by the number of months and the monthly standard deviation of excess return, computed by averaging the squared differences of the monthly excess returns from their mean, then taking the square root. As before, no correction is made for degrees of freedom, so this can be considered as a "population" standard deviation. The third statistic is the monthly Sharpe Ratio, computed by dividing the mean monthly excess return by the monthly standard deviation of excess return.
The Annualized ER block contains statistics that are annualized versions of those in the prior block. The annualized mean monthly excess return is simply the mean monthly excess return times 12. The annualized monthly standard deviation of excess return equals the monthly standard deviation of excess return times the square root of 12. The annualized Sharpe Ratio is computed by dividing the annualized mean monthly excess return by the annualized monthly standard deviation of excess return. Equivalently, the annualized Sharpe Ratio equals the monthly Sharpe Ratio times the square root of 12.
The subsequent block, labeled Mthly ER, provides statistics calculated separately for months in which excess returns were positive and those in which excess returns were negative. The first statistic (Prop >= 0) indicates the proportion of months in which the fund's excess return was positive or zero. The next row shows the mean monthly return in all such months. The next row (Prop < 0) shows the proportion of months in which the fund's excess return was negative, with the following row showing the mean monthly return in all such months.
The Utility block provides measures of utility based on both the funds' historic performances and the parameters specified in the form. The first calculation uses the annualized mean total return (am) and annualized standard deviation of total return (asd) to compute a mean-variance utility of the form:
u = am - ( asd^2) / t
where t is the risk-tolerance specified in the form.
The second calculation in the utility block is based on a piecewise linear utility function. Every positive excess return is given a utility equal to the excess return, while every negative excess return is given a utility equal to rd times its excess return, where rd is the relative disutility specified in the form. The resulting monthly utility values are averaged, then multiplied by 12 to give an annualized monthly utility. Thus:
u = 12 * averaget ( u( ert ) )
where:
u ( ert ) = k*ert
with:
k = 1 if er >0
k = rd if er < 0
The next block, labeled ER Regression, provides the results obtained by performing standard regression analyses in which the benchmark excess return is the independent variable and each of the fund' excess returns is, in turn, the dependent variable. The Beta value is the slope coefficient and the Monthly Alpha value the intercept. The Monthly Residual Standard Deviation (RSD) is the standard deviation of the residuals from the regression. In this calculation, the actual residual standard deviation is adjusted for two lost degrees of freedom and thus can be considered a "sample estimate".
The final block (labeled Annualized) provides annualized performance measures derived from the excess return regression analysis. The Annualized Alpha value is equal to 12 times the monthly alpha value. The Annualized Alpha/Beta ratio equals the annualized alpha divided by the beta value. The value labeled RSD is the Annualized Monthly Residual Standard Deviation. It equals the Monthly Residual Standard Deviation times the square root of 12. The final value, Alpha/RSD, is the ratio of the Annualized Alpha divided by the Annualized Monthly Residual Standard Deviation. Otherwise stated, this is the Sharpe Ratio of the Residual.
You may enter any desired text in this box to describe the source of the input data, etc..