ECON 424/AMATH 462:  Computational Finance and Financial Econometrics

Excel Hints
R Hints


Final Project: Winter 2015

Note: Please check this page for updates and corrections every few days. Additions will be dated.

Last updated: February 20, 2015

Selecting Data

For this project, everyone will be using the same data.

  1. S&P 500 index: vfinx

  2. European stock index: veurx

  3. Emerging markets fund: veiex

  4. Long-term bond fund: vbltx

  5. Short-term bond fund: vbisx

  6. Pacific stock index: vpacx

Information on these funds is available on the Yahoo! finance site. After typing in the sticker symbol and retrieving the quote data, choose Profile to get a summary of the fund. Please review each fund before doing any of the analysis below.

Downloading Data

For the project you will analyze 5 years of monthly closing price data from the end of December 2009 through the end of December 2014.

The following R script file guides you through the creation of the necessary R objects for the analysis of the data in R

Organization of Results

As in the homework assignments, summarize your R work in a Word file. You will find it helpful to organize your Excel results in a spreadsheet by task. That is, put all of the data in one worksheet tab, put all the graphs in another, put the portfolio analysis in another tab, etc. This will make it easier for you to print out results. It is also helpful to use names for your data and for certain results. This makes working with formulas much easier and it also helps to eliminate errors in formulas etc.

You will find it helpful to add text boxes in your spreadsheet to organize comments etc.

Remember to save your work often as Excel has a tendency to crash with large complicated spreadsheets. Also, keep a back-up copy of your project.

Formal Write-up

I want you to give a formal write-up, separate from the Excel spreadsheet analysis and R statistical analysis. Treat this write-up as a term-paper project. Typically, the write-up is between 10 and 20 pages (double spaced with graphs and tables). Your write up should consist of:

  1. An executive summary, which gives a brief summary of the main results using bullet points

  2. Sections that summarize the results of your statistical analysis by topic (see below)

You may find it helpful to include parts of your spreadsheet and computer output as part of your write-up. Alternatively, you can refer to your spreadsheets for the quantitative results, graphs etc.

You only need to turn in the formal write-up. Turning in print-outs of your Excel spreadsheets and R output is optional.

I have boxes of projects from previous classes. Feel free to come by my office to look at them.

Exerpts from an example class project: 424projectExample.pdf


Return calculations and Sample Statistics 

  • Compute time plots of monthly prices and continuously compounded returns and comment. Are there any unusually large or small returns? Can you identify any news events that may explain these unusual values? Give a plot showing the growth of $1 in each of the funds over the five year period (this is called an "equity curve"). Which fund gives the highest future value? Are you surprised?

  • Create four panel diagnostic plots containing histograms, boxplots, qq-plots, and SACFs for each return series and comment. Do the returns look normally distributed? Are there any outliers in the data? Is there any evidence of linear time dependence? Also, create a boxplot showing the distributions of all of the assets in one graph.

  • Compute univariate descriptive statistics (mean, variance, standard deviation, skewness, kurtosis, quantiles) for each return series and comment. Which funds have the highest and lowest average return? Which funds have the highest and lowest standard deviation? Which funds look most and least normally distributed?

  • Using a monthly risk free rate equal to 0.0004167 per month (which corresponds to a continuously compounded annual rate of 0.5%), compute Sharpe's slope/ratio for each asset. Use the boostrap to calculate estimated standard errors for the Sharpe ratios. Arrange these values nicely in a table. Which asset has the highest slope? Are the Sharpe slopes estimated precisely?

  • Compute estimated standard errors and form 95% confidence intervals for the the estimates of the mean and standard deviation. Arrange these values nicely in a table. Are these means and standard deviations estimated very precisely? Which estimates are more precise: the estimated means or standard deviations?

  • Convert the monthly sample means into annual estimates by multiplying by 12 and convert the monthly sample SDs into annual estimates by multiplying by the square root of 12. Comment on the values of these annual numbers. Using these values, compute annualized Sharpe ratios. Are the asset rankings the same as with the monthly Sharpe ratios? Assuming you get the average annual return every year for 5 years, how much would $1 grow to after 5 years? (Remember, the annual return you compute is a cc annual return).

  • Compute and plot all pair-wise scatterplots between your 6 assets. Briefly comment on any relationships you see

    • Compute the sample covariance matrix of the returns on your six assets and comment on the direction of linear association between the asset returns

  • Compute the sample correlation matrix of the returns on your six assets and plot this correlation matrix using the R corrplot package function corrplot(). Which assets are most highly correlated?  Which are least correlated?  Based on the estimated correlation values do you think diversification will reduce risk with these assets?

Value-at-Risk Calculations

  • Assume that you have $100,000 to invest starting at December 31, 2014.  For each asset, determine the 1% and 5% value-at-risk of the $100,000 investment over a one month investment horizon based on the normal distribution using the estimated means and variances of your assets.  Use the bootstrap to compute estimated standard errors and 95% confidence intervals for your 1% and 5% VaR estimates. Create a table showing the 1% and 5% VaR estimates along with the bootstrap standard errors and 95% confidence intervals. Using these results, comment on the precision of your VaR estimates. Which assets have the highest and lowest VaR at each horizon? 

  • Using the monthly mean and standard deviation estimates, compute the annualized mean (12 time monthly mean) and standard deviation (square root of 12 time monthly std dev) and determine the 1% and 5% value-at-risk of the $100,000 investment over a one year investment horizon. Arrange these results nicely in a table.

  • Repeat the VaR analysis (but skip the bootstrapping and the annualized VaR calculation), but this time use the empirical 1% and 5% quantiles of the return distributions (which do not assume a normal distribution - this method is often called historical simulation). How different are the results from those based on the normal distribution?

Rolling Analysis of the CER Model Parameters

  • For each asset, compute 24 month rolling estimates of the mean and standard deviation of the continuously compounded returns using the R function rollapply() (see the script file  for lab 5 for examples). For each asset, graph these rolling estimates together with the returns (so that you have just one graph for each asset). Briefly comment on the stability of the mean and SD parameters of the constant expected return model.

  • With 6 assets there are 6*5/2 = 15 pairwise correlations. To keep things simple, compute and plot 24 month rolling estimates of the sample correlation between the S&P 500 index (vfinx) and the long-term bond index (vbltx). Is this correlation stable over time? When is the correlation the highest and when is it the lowest?