Using Excel Solver Assignment Help-FTS II – International Diversification with International ETFs

pressays

 Using Excel Solver Assignment Help-FTS II – International Diversification with International ETFs

 

FTS II – International Diversification with International ETF

 

Objective

The project objective is to create an internationally diversified portfolio of ETFs with calculations of expected risk and return. One week later, evaluate how your portfolio did versus the S&P 500.

 

This is an optional case study and is an individual project.

 

Navigating the FTS Platform (Software instructions): 

 

You may download the FTS platform software as follows:

Install the software (PC, or MAC with Windows emulator):

http://www.ftsmodules.com/public/modules/ftssystemmanager/setupSystemManager.htm

Once you have opened the software, use the FTS Real Time Client 4.0.0.4 (New) version of the software.  Choose “Download again before running.”

 

Once in the login page, select this case.  

 

 

 

 

 

In the bottom right corner, you have the fourth window with the following Tabs: Financial TV and

News/Analytics/Company Filings.  Click on the Analytics to calculate covariances. From the RT Client, select “Covariances and Returns (CAPM)” in the Analytics area (at the bottom right.)

 

 

You will see your portfolio weights and the expected returns and covariances of all the ETFs.  In the Edit menu of the Analytics area, select “Export to Excel.”  This will transfer the data into an Excel spreadsheet:

 

 

 

  1. Make sure Solver is available in Excel.

 

  1. Implement the formulas for calculating the portfolio’s expected return and variance
    • In cell B2, enter the formula =SUMPRODUCT(B7:B57,C7:C57)
    • In cell B3, enter the formula =SUMPRODUCT(B7:B57,MMULT(D7:BB57,B7:B57)) to obtain the portfolio variance as a function of the weights. Now, as the weights change, both the expected return and the portfolio variance will be recomputed.  Without this step of changing formulas, Solver will not be able to calculate the optimal portfolio weights.
    • In cell B4 enter a target return of .08
    • In cell B5, enter the formula =SUM(B7:B57)
    • To execute array functions, you will have to press SHIFT+CTRL+ENTER. Otherwise, you will get an error: #VALUE.

 

  1. Run Solver, define the objective (which is to minimize the variance) and the constraints (the two basic constraints plus additional constraints you chose), and calculate the portfolio weights. Note that if your constraints are unreasonable, there may not be a solution.  Sometimes, without additional constraints, there

may not be a solution.  We will restrict short selling,  i.e. wi ≥ 0.0 for all i.  For this problem, the Solver dialog box for the ETF’s looks like:

 

 

 

You should start with these constraints, and then experiment with others.  This step requires some amount of experimentation.  If you don’t impose a constraint like the third one (a lower bound on the weights), Solver may not be able to find a solution.

 

  • One technical problem you may face is that the covariance matrix is close to being singular. In this case, Solver may not be able to find a solution. One way you will know that there is a problem is if

Solver tells you that the “cell values fail to converge.”   In that case, you will have to use more stocks.

 

  • The FTS Real Time Client overrides the inputs of a stock’s volatility if it is inconsistent. Technically, it increases the volatility internally to make sure that all correlations across stock returns are between -1 and 1.  The covariance matrix that is exported to Excel contains this adjustment.   If you want to use your own numbers, you can modify them directly in the spreadsheet.

 

You should then choose a set of 12-15 ETF’s along a theme: regional or types of countries, or ETF’s involved in similar sectors, and so on. To select these ETF’s, just below where it says “By Changing Cells,” enter the cells with the weights you want to use separated by commas, e.g., $B$7,$B$9,$B$15

 

  1. Record the expected return and variance of each of your ETF’s.

 

  1. Construct and record the efficient frontier of your portfolio holdings using different expected returns which will result in different risks. (For example, with expected returns of .04; .045; .05; .055; .06; .065; .07; .075; .08) Record all the risk/return pairings.

 

  1. Chose one of these return/risk pairings as your desired strategy.

 

  1. Implement your trading strategy using the FTS software. This means that you have to take the weights and using current prices, calculate the number of shares of each ETF to buy. It is fine to end up with 1-5% cash, so the share percentages do not need to be exact and you can round your holdings.

 

Note: If when you try to buy an ETF, FTS may respond by saying the bid/offer spread is too wide. That often happens when the international market is closed. If so, try again the next morning, because if you buy the ETF on a wide spread, your performance will be very low. If it continues, exclude that ETF.

 

  1. Minimum number of ETFs in a portfolio: 9. Maximum number of ETFs in a portfolio: 15

 

Performance Report: In the format of a professional research report, you should include:

  • the name and objective of the fund;
  • a short bio of the manager (you);
  • the fund holdings/percentages;
  • graph of the minimum variance for each target return level when utilizing all ETF’s
  • graph of the minimum variance for each target return for your selected ETF’s, highlighting your selected risk/return portfolio, and the risk/return plot point of each of the individual assets in your portfolio
  • graph of your actual return versus the S&P500 over the holding period.
  • Analysis of your portfolio’s return over the investment period
  • The report should be done in Microsoft Word and submitted via TURNITIN.

 

 Buy/Sell

*Bottom left after click the Currency (Under Ticker Window)

Please follow and like us: