Excel Finance Project

Final Project

Professor Mühlhofer School of Business Administration

University of Miami FIN423: Introduction to Alternative Investments

Spring 2018

This assignment is due via Blackboard by 5PM, Monday, May 7. Collaboration is allowed, and you may work on this assignment in groups of up to three students. Please submit a single solution for your group with all members’ names on it, under the name of one of your group members.

You must submit your Excel Spreadsheet, as well as a separate document that answers the interpretation questions. The Excel spreadsheet must be laid out in a professional and easily understandable way, with rel- evant results highlighted, and cells labeled appropriately. The writing must be typed and have a professional appearance.

There is no official minimum length for the writing; just answer each point completely and adhere to the maximum length. Remember, more is not necessarily better; you must actually have something intelligent to say.

1 Layout and Data

Please refer to the handout for Lecture 18 for theory on what we’re doing, as well as equations, where relevant. The spreadsheet on Blackboard consists of the following tabs:

1. Returns. This contains the returns series for the different investment assets. Markowitz optimization will be based on these.

2. Currencies-Prices-Interest-Rates. This contains exchange rates and interest rates, and will also be where you construct your pseudo price series for the S&P-500 ETF.

3. Black-Scholes Calculations. This will be the place to construct the call option premia.

4. Markowitz. This will be the place to do calculations for your Markowitz optimization.

The data series for the investment assets are:

1. US Stocks. Data from the SPY (ETF that tracks the S&P 500).

1

2. US Corporate Bonds. Data from the LQD (ETF that tracks an investment-grade US corporate-bond index).

3. Foreign Stocks. Data from the FEZ (ETF that tracks the EuroStoxx 50 index). See lecture handout on what this shows.

4. US Securitized Real Estate Equity (REITs). Data from the IYR (ETF that tracks the Dow Jones US Real Estate index).

5. Venture Capital. TRVCI (Thomson Reuters Venture Capital Index) an index that mirrors the perfor- mance of this investment category.

The data series are monthly and go from the beginning of 2012 to the end of 2017.

On the second sheet, you have the following data:

1. TRVCI Index. These are index levels for this data series. I have used these to compute returns for you (already done).

2. EUR Curncy. This is the Euro/US-Dollar exchange rate, quoted in US Dollars per Euro.

3. EUSWEA Curncy. This is the Euro-Zone risk-free rate.

4. USSOA Curncy. This is the US-Dollar risk-free rate.

5. SPY Price/Pseudo Price. This only has the starting price for your SPY series, and you will use it to compute the series of pseudo-prices.

Note about risk-free rates: these are quoted as whole percentage points per year. That means, for example, on September 29, 2017, the US risk-free rate was 1.1585% per year, and the Euro risk-free rate was -.359% per year. This means, you have to divide these by 12, but also have to divide them by 100 (since everything else is in decimals, rather than whole precentage points).

2 Excel Instructions

You will use Excel to compute optimal portfolios, for up to 5 asset classes at a time. You will do this by using the Solver function, which performs constrained optimization. Step-by-step instructions are below. Generally, solver maximizes or minimizes the value in a target cell, by changing a set of allowable cells until the optimization is achieved. Further, this optimization can be made subject to certain constraints.

First, you will make manipulations to the data, to set up the two additional data series (Hedged FEZ) and SPY Call Options. Then, you will set up cells for the portfolio mean and portfolio standard deviations. With more than two assets, it is useful to employ matrix multiplication to do this. Lastly, you will use Solver to produce portfolio allocations.

1. For your US-Dollar risk-free rate series, just compute one average. Record this off to the side some- where. This will be your risk-free rate, used for both option pricing and Markowitz Optimization. Mark this as such. Be sure to divide by 12 here, as everything else is monthly, and these numbers are annualized.

2

2. Simulate hedging the currency risk of the FEZ ETF through futures. Compute the Hedged FEZ returns, as described in Section 3.1 of the lecture handout (especially Equation 5). For any period for which you are computing returns E0 is the exchange rate, the previous period, while E1 is the exchange rate in the same period. Use for the risk-free rates, use the risk-free rate in each period. Use T = 1/12, and do not divide the risk-free rates in this calculation. Be sure to match dates correctly: 12/30/2011 will not have a return value here; the first one will be in 1/31/2012.

3. Compute the pseudo price series for the SPY on the second tab, using the starting price already given there, and the procedure outlined in the lecture notes (Section 2, at the end) and on the board in class.

4. Using the pseudo price series you computed, fill in the third tab to compute Black-Scholes option prices for the SPY. K is the start price from the second tab (keep this fixed, as these options are at the money at the start of the window). To find sigma compute the standard deviation of returns to the SPY (i.e. use the Excel function =STDEV(<array of returns>), with the SPY column from the first tab. r will be the average risk-free rate for the US-Dollar that you already computed (use the one that has been divided by 12). NORMSDIST is the Excel function for the standard normal distribution.

5. Based on the set of option prices (C) that you compute on the third tab, construct a series of returns of these, to fill into the appropriate column on the first tab. These will be constructed the usual way of C1/C0 − 1. Be sure to match dates correctly: 12/30/2011 will not have a return value here too; the first one will be in 1/31/2012.

6. Begin computations for your Markowitz setup. In the fourth tab, compute the mean return of each asset. The function to use here is =AVERAGE(<array of an asset’s returns from first sheet>). If you set it up correctly you can compute one explicitly, and then drag the handle across for the rest. In my case, after making appropriate labels, my means are in cells A3 through G3. You can put them wherever you want, but remember this array, as I will refer back to it later.

7. Next, we need the standard deviations for each asset. The function you want here is =STDEV(<array of returns>). For me, these are in A7 through G7; once again you can put them wherever you would like, but I will be referring back to this cell range.

8. After this, we need a correlation matrix (for our own reference and interpretation), as well as a covari- ance matrix (for performing portfolio calculations quickly). The simplest way to generate these is by installing Excel’s Analysis add-in (if you do not have it already).1 Once you have the add-in installed, in the Data tab, you will see an Analysis box, with an option called Data Analysis. Clicking this will bring up a menu, from which you can select Correlation. Follow the steps, to create a correlation table; put this into the same sheet in which you are doing the calculations. I picked A11 as my starting cell, so the values (i.e. the numbers rather than the labels) of my correlation matrix are in cells B12 through H18, with only the diagonal and sub-diagonal cells filled. Notice that we have many low correlations. This is nice!

For covariance, you can use the equivalent function, found in the same menu as correlation. Regrettably, once again, this only gives you the lower half of the matrix. For correlation, this is OK, as it makes the table more readable. For covariance, however, since we will use this matrix for calculations, we need to create a full covariance matrix. This means, you will need to reflect the matrix about the diagonal. Thus, for all sub-diagonal elements, columns need to become rows, in the other half of the matrix, so you get to a point where, for example, the covariance between IYR and SPY is the same as that between SPY and IYR. There is no really easy way to do it, so for a 7x7 matrix, copying and pasting values by hand (or using the Transpose checkbox under paste special and going column by column, or by highlighting a row and using the =TRANSPOSE function, with ctrl-shift-enter) is about as good as any other way that exists to do this. My covariance matrix ends up in cells B23 through H29, with all cells filled. A22 is my starting cell.

1Search for this in Help, for step-by-step instructions on how to install add-ins.

3

9. Set up a row of seven cells to contain the portfolio weights. Solver will change these to determine portfolio allocations. For now, set them all to 1/7 (i.e. you would put an equal amount of your wealth in each asset). This is a 1 × n row-vector of weights, which I will call T. For me these are in cells A32 through G32. Somewhere next to this, construct a cell with the sum of your weights, by using the function SUM=. This should always be 1. For me, this cell is in H32. Note: The distinction between rows and columns matters here. Make sure things that are supposed to be rows, actually are.

10. For computing portfolio statistics, we will use matrix multiplication, which saves us a lot of typing, as the statistical formulas in seven assets get somewhat cumbersome in scalar notation. Set up a cell for the portfolio expected return. This can be written as Tµ′, where µ is the row vector of individual asset expected returns in A3–G3.2 To compute this, type into the cell which you want to contain this value =MMULT(A32:G32,TRANSPOSE(A3:G3)). Be sure to hit Ctrl-shift-enter after typing this, to make Excel understand that it is dealing with vectors and matrices.

11. Set up a cell for the portfolio variance. This can be written as TV T ′ where V is the covariance matrix for your assets, set up in B23–H29. To compute this, type into the cell which you want to contain this value =MMULT(MMULT(A32:G32,B23:H29),TRANSPOSE(A32:G32)). Again, be sure to hit Ctrl-shift-enter.

12. Set up a cell for portfolio standard deviation, computed as square root of portfolio variance.

13. Set up a cell for the Sharpe Ratio. To do this, use the average risk-free rate per quarter that you computed in step 1.

14. Now use solver to find optimal portfolios. You will find various different tangency portfolios, with each portfolio combination. To do this, click on the Data tab, find the Analysis box, and open up Solver.3

Skip the Objective cell for now and enter your portfolio weights as the cells that need to be changed. Then, enter a constraint that the weights need to sum to 1 (i.e. constrain the cell that contains the sum of the weights to equal 1).

Note: Solver has a check box to constrain the obtained solution to be positive. This would prevent short selling. You do not want this option checked: you want to allow short selling, for all assets except venture capital, which cannot be short sold. In line with this, now enter one more constraint, that the weight on TRVCI needs to be greater than or equal to zero.

Then switch off all assets except SPY and LQD to begin with (use only traditional asset classes). To do this, constrain the weight on all assets except SPY and LQD to be exactly zero. For the solving method, you want the default, which is GRG Nonlinear. You do not want LP, as this stands for Linear Program, and this is a non-linear program (there is a square-root in the standard-deviation formula, and there are squares in the variance formula).

15. Compute the tangency portfolio. To do this, set your target cell to the cell containing your Sharpe Ratio, and instruct Solver to maximize this cell. Record the expected return, standard deviation, Sharpe Ratio, and portfolio weights of your tangency portfolio, by copying them and pasting as values.

16. Repeat step 15, adding each of the alternative asset classes in turn. Add them in the following order:

(a) Foreign Stocks.

(b) Real Estate.

2From here on out, I will use references to the locations of my arrays, described earlier. If your respective arrays are somewhere else, be sure to adjust your references accordingly.

3Install the add-in if you cannot find it there. Go to help and search for “solver”. Follow the directions under Load the Solver Add-in. Once it is loaded it is located under the data tab

4

(c) Venture Capital.

To add an asset, remove the constraint that sets its weight to zero. Then, re-run solver, and record the new expected return, standard deviation, Sharpe Ratio, and portfolio weights of your tangency portfolio.

17. Add both hedges to your portfolio, first each by itself, then both at the same time, as follows:

(a) Remove the constraint on your hedged FEZ, and add a constraint to make the weight on unhedged FEZ zero (this replaces unhedged FEZ with hedged FEZ).

(b) Constrain hedged FEZ back to zero, and remove the constraint for unhedged FEZ. Remove the constraint for the call options. Now you have a portfolio that hedges the S&P 500 and does not hedge forex risk.

(c) Lastly, use both hedges. To do so, remove the constraint for hedged FEZ, and constrain unhedged FEZ to zero.

For the steps above, each time run solver to find the tangency portfolio, and record expected return, standard deviation, Sharpe Ratio, and portfolio weights of your tangency portfolio.

18. The main results from your investigation are your tangency portfolios, including their expected returns, standard deviations, Sharpe Ratios, and portfolio weights. Make sure these are readily identifiable in your spreadsheet, and well labeled. Submit your excel file via Blackboard as one of the two components of your submission.

3 Interpretation

In a separate file, also to be submitted to Blackboard, answer the following questions (maximum four sentences each):

1. What happens to portfolio weights and Sharpe Ratios as you progressively add alternative asset classes to your portfolio? Consider what you can learn from the correlation matrix to help explain this answer.

2. What effect does hedging forex risk have on your portfolio’s Sharpe Ratio and the weights given to each asset?

3. What effect does hedging the S&P 500 have on your portfolio’s Sharpe Ratio and the weights given to each asset?

4. Given that transactions costs for your hedges could potentially be significant, and given the benefits of each hedge, would you recommend hedging forex risk, the S&P-500, both, or neither?

5