hedge fund analysis - Excel Models

Last update: April 2010. The following changes have been implemented:

• A new algorithm has been introduced to calculate inverse matrices more accurately via the Gauss-Jordan algorithm.
• The formula can be used to display regression residuals.
• General Least Squares estimation without constant (intercept at origin) and data labels.
• Extended version of Errorhandler.

The above template uses matrix algebra to determine regression coefficients, predictions, and confidence intervals. Thus, the function REGOUTPUT() is not subject to the limitations of the Excel-inherent Linest()-Function / Matrix-Functions. In addition, the template provides extensive regression diagnostics: test for collinearity of consecutive error terms, normality, and goodness of fit.

This model uses a forward stepwise regression algorithm to select predictor variables from a range of parameters. The input parameters include the level of tolerance, F-to-leave and F-to-enter. The maximum number of iterations is set to 1000 but can be changed manually in the source code (please inquire about the password first). The output parameters are identical to the multiple factor regression model.

New! This model allows for different calculations of coefficient standard error estimates in time series regression:

• Standard OLS estimate assuming constant variance.
• White standard error estimate.
• GMM standard errors and Newey-West estimate (weighted according to Bartlett kernel).

Lag truncation for Newey-West estimates may be specified separately. 

In accordance with the Sharpe Multifactor Pricing models, the above routine calculates the coefficient estimates for a number of regressors based on the assumption that the sum of coefficients = 1. This allows for a more meaningful interpretation of the factor loadings. The spreadsheet uses an example estimating the performance of a hedge fund from five input parameters.

This spreadsheet calculates the Durbin-Watson test statictic for serial correlation based on a single factor regression model. In addition, it returns the critical lower and upper limits from the Durbin-Watson tables (for up to n = 200 observations and k = 20 regressors). Please refer to the multifactor models above when testing for serial correlations in residuals with k > 2 (more than one regressor).

Data series can be tested for normality using either the non-parametric Chi-Squared Test for Normality or the Jarque-Bera statistic. The latter uses the third and fourth moment of the distribution (also referred to as skew and kurtosis). Some series are found to be more sensitive to one or the other test statistic.

This spreadsheet provides macros to calculate a number of downside risk measures including maximum continuous drawdown, semi-deviation and Value-at-Risk. Some of the code snippets were downloaded from http://www.andreassteiner.net and altered to expressing the sub-period returns as (1 + R_{t, t + 1}) to account for compounding effects, where R_{t, t + 1} is the perentage return between t and time t + 1.

This simple algorithm compares two time series on the basis of the t-test for equal means. The input options for the experimental designs are: paired vs random and one-tail vs two-tail. Testing for equality of sample variances is conducted via the F-test. All templates are function-based.

The macro calculates the autocorrelation coefficients and partial autocorrelation coefficients of order k of any time series. In addition, it uses the Breusch-Godfrey LM test to detect autocorrelation at distinct lags. The Durbin-Watson test and Durbin h test for ARMA-Models may be used to identify serial correlation among consecutive residuals in linear regression. All tests function-based.

The unit root test is conducted as an augmented Dickey Fuller test. The test accounts for first differences (extension of model follows shortly), intercept and appropriate lags. The p-value is calculated from Dickey Fuller tables of critical values included in the macro and approximated via linear interpolation. The statistic is function-based.

New! Allows for a number of unit root tests including Dickey-Fuller, Augmented-Dickey-Fuller (ADF) and Phillips-Perron (PP). The last test statistic uses the heteroskedasticity-autocorrelation-consistent (HAC) error variance estimator of Newey and West (with Bartlett kernel). The following specifications can be made with respect to the test regression: differences considered, intercept and trend line included, lags considered and type of error estimation. The signficance of the AR[p]-processes is determined according to the Akaike Information Criterion (AIC). The maximum number of lags follows from Schwartz (if not specified otherwise). The spreadsheet also includes McKinnon critical values for the ADF-tests as well as Engle-Granger. You may use the CommandButton in Worksheet "AR(1)-Process" to generate AR[1]-Processes.  

New! This VBA script uses Monte Carlo to simulate critical values for the Dickey Fuller test statistic from many replications of random walks (with or without intercept/trend). The user may specify the sample size, confidence level as well as the number of replications. An excerpt from the original DF-tables is included for comparison.

The following changes have been made as of March 2010:

• Errors occuring with unbalanced ANOVA problems where the number of treatments is larger then 6 (e.g. n * k > 6) have been addressed. The model now works for any possible number of levels of factors A and B.
• Some additional error messages were introduced with respect to ill-specified models violating the regression requirements of Type III SS models (minimum number of replicates required for a specified number of treatments).
• Two additional large-sample examples were included showing how pivottables can be used in conjunction with the ANOVA-functions.
• Treatment()-functionality (rather than pivottables) to convert raw data into tables that can be used in conjunction with ANOVA.

This macro allows users to conduct the following Analysis of Variance tests: Single-Factor ANOVA, Randomized Blocks Experiment, Unbalanced Single-Factor ANOVA, Two-factor ANOVA, Unbalanced Two-Factor ANOVA. The unbalanced experiments require a simple multivariate regression routine (included) to compare partial and full models. Note that one may use ANOVA for more than two factors. This approach may be undesirable due to computational efforts involved.

This spreadsheet includes three distinct tests to check for the presence of heteroscedasticity in the error terms of regression models. For univariate models, the Szroeter test statistic or Breusch-Pagan test may be used. For multivariate models, the Breusch-Pagan test or, alternatively, for extended models different White test may be applicable. Alternatively, manual solutions using Excel-inherent functions are provided.

These simple algorithms for option valuation circumvent the tedious manual calculations of the Black-Scholes-Merton model. The functions make provisions for foreign interest rate / dividend leakage and allow for calculation of all relevant option sensitivities (Delta, Theta, Gamma, Vega, Rho). Alternatively, prices of floating strike lookback options may be determined. All options assumed to be European style (exercise only at expiry date).

The spreadsheet gives an example as to the use of multiple linear regression in determining the significance of factors and interaction thereof when sample sizes / treatments are unequal in size. For a VBA-implementation see ANOVA model above.  

This workbook includes a financial planning model that can be used to analyze the expected ROI of a business venture. It includes predicted balance sheets, cashflow and income statements, as well as sensitivity analysis. This applet is extremely useful when drafting business plans or assessing investment opportunities.

This spreadsheet requires the Solver Add-in, which can be downloaded from the Frontline Systems homepage. It includes an example to construct Minimum Variance portfolios from five assets, as well as an example of multiple objective optimization (optimal risk-return portfolios) using percentage deviations and sensitivity analysis. The sensitivity assistant too may be downloaded from Frontline Systems. Please note that Solver is not free-ware. A VBA-based optimization algorithm is currenlty under development.

This spreadsheets compares efficient portfolios of hedge funds and standard asset indices. It is helpful in finding the optimal asset allocation with regard to standard and alternative investments. The spreadsheet requires a Bloomberg uplink for data refresh.

This spreadsheet shows the relationship between maximum likelihood (ML) estimators of regression and ordinary least squares (OLS), as well as the the calculation of an unbiased estimator of the variability in Y from the ML estimator of variance. Additionally, it shows the determination and application of the Box-Cox power transformation in Y to ensure the assumptions of the Linear Model. In order to solve for the ML estimates, the Solver-Addin is required. Although this spreadsheet does not rely on VB Macros, regressions are calculated using the user-defined REGOUTPUT() function. Please enable macros or change the formula into LINEST().

This freeware statistical software was written by Herman J Bierens from Pennsylvania State University. It is a suprisingly powerful tool capable of conducting a multitude of operations. Please note that this is not an official mirror site. The install file has been placed here for convenience. However, no support or updates will be provided. Please follow this link to the official website.

Libero sponsors a number of extremely powerful Excel Add-ins that include matrix calculations and linear algebra. All software is freeware and Excel 2003/XP and VB6 compatible. The matrix.xla download has been tested in a Excel 2007/Vista environment and worked flawlessly. However, no responsibility is taken for compatibility issues. Please refer to Libero for further inquiries and browse their catalogue for other software.