FTS II – International Diversification with International ETFs

 FTS II – International Diversification with International ETFs

Note: this project requires use of Excel’s Solver


The project objective is to create an internationally diversified portfolio of ETFs with calculations of expected risk and return. Each of you will construct such a portfolio in class. At least three business days later, evaluate how your portfolio did versus the S&P 500.

 Navigating the FTS Platform (Software instructions):

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


 Use the FTS Real Time Client (New) version of the software. Choose “Download again before running.”

Once in the login page, select this case. 

Enter your trading name and password and login.  When you start, you have $1m in cash, and you have to invest this in the ETF’s.  Minimize the pop-up screen with tickers and names of the ishares.

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.

  1. 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. In the spreadsheet, enter target expected return; the S&P500 expected return is automatically entered
  2. In your spreadsheet, implement the formulas for calculating the portfolio’s expected return and variance
    • For this case, the weights will be exported to cells B7:B59 and the expected returns to C7:C59.  So in cell B2, you should enter the formula =SUMPRODUCT(B7:B57,C7:C57)
    • The covariance matrix is in the range D7:BD59.  In cell B3, you should enter =SUMPRODUCT(B7:B57,MMULT(D7:BD57,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.
    • Add the formula for the sum of the weights in cell B5.  In this example, you would enter =SUM(B7:B57)
    • To execute array functions, you will have to press SHIFT+CTRL+ENTER.  Otherwise, you will get an error: #VALUE.

  3. 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 without   One way you will know that there is a problem is if Solver tells you that the “cell values fail to converge.”  This is more likely to happen with a large number of stocks.  In that case, you will have to modify the covariance matrix in the spreadsheet to make it non-singular.
  • 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.
  • If you want to use fewer stocks, set the weights of the other stocks to zero, and in Solver, 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
    • Solver is more likely to find a solution with fewer stocks
  1. Record the expected return and variance of each of your ETF’s.
  2. Construct and record the efficient frontier of your portfolio holdings using different expected returns which will result in different risks.
  3. Chose one of these return/risk pairings as your desired strategy.
  4. 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


  1. Minimum number of ETFs in a portfolio: 5. Maximum number of ETFs in a portfolio: 10
  2. Performance Report: You should structure your report to include the name and objective of the fund; a short bio of the manager (you); the fund holdings/percentages; analysis (and graph) of your expected risk and return on the efficient frontier including each of the individual assets in your portfolio; analysis and graph of your expected risk and return vs. the benchmark S&P500; and your ultimate return versus the S&P500 over the holding period. The report should be done in Microsoft Word and submitted via TURNITIN.

Login  Password
cbu1 cbu1 Aldhaheri Saeed

cbu2 cbu2 Alzhanova Assel

cbu3 cbu3 Barbosa Emiel

cbu4 cbu4 Barlubayeva Anara

cbu5 cbu5 Bascetta Lauren

cbu6 cbu6 Belrhiti Alaoui Driss

cbu7 cbu7 Bentley Oren

cbu8 cbu8 Berry Michael

cbu9 cbu9 Bhalla Vidur

cbu10 cbu10 Bhatia Sanjana

cbu11 cbu11 Bickford Michael

cbu12 cbu12 Bitto Michael

cbu13 cbu13 Bolick Timothy

cbu14 cbu14 Brooks Patrick

cbu15 cbu15 Caito Austin

cbu16 cbu16 Caramadre Michael

cbu17 cbu17 Carliang Patricia

cbu18 cbu18 Chan Phalsothira

cbu19 cbu19 Chase Collin

cbu20 cbu20 Clifford Zachary

cbu21 cbu21 Cormier Peter

cbu22 cbu22 Crossen Matthew

cbu23 cbu23 Cullen Brianne

cbu24 cbu24 Cullity Jonathan

cbu25 cbu25 DiCienzo Franco

cbu26 cbu26 Duodu Kwasi

cbu27 cbu27 Dutra Hannah

cbu28 cbu28 Dziedzic Daniel

cbu29 cbu29 Dzwil Alexander

cbu30 cbu30 Emerson Eric

cbu31 cbu31 Fan Chia chi

cbu32 cbu32 Farley James

cbu33 cbu33 Fernandes Maria Eduarda

cbu34 cbu34 Ferraro Thomas

cbu35 cbu35 Field Sidney

cbu36 cbu36 Foerschner Alexandra

cbu37 cbu37 Frederick Emily

cbu38 cbu38 Fu Chien chen

cbu39 cbu39 Garcia Austin

cbu40 cbu40 Gemba Valerie

cbu41 cbu41 Genovese Rachel

cbu42 cbu42 Gidley Adam

cbu43 cbu43 Goddard Ryan

cbu44 cbu44 Golab Matthew

cbu45 cbu45 Greene Dylan

cbu46 cbu46 Gupta Juhi

cbu47 cbu47 Haas Niklaus

cbu48 cbu48 Hussey Patrick

cbu49 cbu49 Imtiaz Muhammad Musa

cbu50 cbu50 Juez Juez Freddy

cbu51 cbu51 Kalpaxis Aliki

cbu52 cbu52 Karnane Pooja

cbu53 cbu53 Keller Stephanie

cbu54 cbu54 Khosla Nippun

cbu55 cbu55 Kocaman Bugra

cbu56 cbu56 Koudelka Christopher

cbu57 cbu57 Le Trang

cbu58 cbu58 Leake Evan

cbu59 cbu59 LeNeveu Philip

cbu60 cbu60 Lin Ting Ting

cbu61 cbu61 Lin Xingjun

cbu62 cbu62 Little Scott

cbu63 cbu63 Liu Khung Thay

cbu64 cbu64 Liu Pang Cheng

cbu65 cbu65 Liu Sicheng

cbu66 cbu66 Liu Xu

cbu67 cbu67 Lo Kimberly

cbu68 cbu68 Lonardo Daniel

cbu69 cbu69 Lunden Taylor

cbu70 cbu70 MacDonald Erin

cbu71 cbu71 Mailhot Ryan

cbu72 cbu72 Masand Shiv

cbu73 cbu73 Mastromarino Kathryn

cbu74 cbu74 Mehta Rupak

cbu75 cbu75 Mejia Lorena

cbu76 cbu76 Mittal Sarthak

cbu77 cbu77 Monroe Janie

cbu78 cbu78 Morena Julia

cbu79 cbu79 Muller Daniel

cbu80 cbu80 Murphy Rachel

cbu81 cbu81 Neumann Connor

cbu82 cbu82 Nghiem Ngoc

cbu83 cbu83 Norris Paul

cbu84 cbu84 Pahwa Ishang

cbu85 cbu85 Palmer Brady

cbu86 cbu86 Papadimitriou Peter

cbu87 cbu87 Park Se Jin

cbu88 cbu88 Perkins John

cbu89 cbu89 Pidgeon Ryan

cbu90 cbu90 Poon Sam

cbu91 cbu91 Quintana Brea Larry

cbu92 cbu92 Ramirez Antonio

cbu93 cbu93 Ramos Erikson

cbu94 cbu94 Reyes Ruben

cbu95 cbu95 Rodriguez Glenys

cbu96 cbu96 Rogers John

cbu97 cbu97 Russell Alana

cbu98 cbu98 Ryan Henry

cbu99 cbu99 Saklou Abdulrahman

cbu100 cbu100 Santiano Evania Paz

cbu101 cbu101 Sellali Meriem

cbu102 cbu102 Sendzik Peter

cbu103 cbu103 Shah Eshita

cbu104 cbu104 Shahnazaryan Alexander

cbu105 cbu105 Shang Jason Yap

cbu106 cbu106 Sheets Andrew

cbu107 cbu107 Sivovlos Samantha

cbu108 cbu108 Smith Kelsey

cbu109 cbu109 Sonia Michael

cbu110 cbu110 Tenczar Kyle

cbu111 cbu111 Tracey Thomas

cbu112 cbu112 Travers Brendan

cbu113 cbu113 Trieu Van

cbu114 cbu114 Tu Emily

cbu115 cbu115 Tucker Anthony

cbu116 cbu116 Tuvshintugs Dulguun

cbu117 cbu117 Volpe Christopher

cbu118 cbu118 Wang Zijun

cbu119 cbu119 Wilson Brett

cbu120 cbu120 Zappala Christopher

cbu121 cbu121 Zhao Pengwei

Theoretical Background

 Modern portfolio theory provides a technique for both measuring risk and return and determining the best way to diversify.  In this project, you will use Excel’s Solver to create an “optimal” diversified portfolio.

Let wi denote the proportion of your money invested in ETF i.  So if your total investment is $1m, you hold 1000 shares of ETF i, and the price of ETF i is 25, then wi = 0.025, i.e. you have invested 2.5% of your money in ETF i.  wi is also called a portfolio weight.   Since there is a direct relationship between the number of shares and the weight, once you have determined the weight, you can easily calculate the number of shares you must hold (given the price and the total investment).

 Constructing a diversified portfolio

Let E(ri) denote the expected return from ETF i.  This is usually measured annually, so E(ri)=10% means you expect the ETF to return 10% over the year.  There are many ways to estimate the expected return; default values are provided by the FTS Real Time Client, though you can override them via the “Parameters” menu item.  Given the portfolio weights, the expected return of the portfolio is:

Finally, we need to describe risk.  Modern portfolio theory uses the variance of returns as a measure of risk (or equivalently, the standard deviation, which is also referred to as volatility).  To calculate the risk of a portfolio, you also need the covariances between ETF returns.   In notation, let ij denote the covariance between the returns of ETFs i and j, so ii is the variance of the return of ETF i.  Then, given the portfolio weights, the variance of the portfolio return is:

The FTS Real Time Client calculates all the covariances for you.  The portfolio selection problem is to find weights that minimize the variance subject to some constraints.  The first is that the sum of the weights equals 1; this simply means that you invest all the money you have allocated to ETF’s.  The second is that the expected return from the portfolio equals your target return.  Beyond that, you can impose more conditions.  For example, you may restrict short selling, either completely, which says wi ≥0.  Or you may require that you will not invest more some amount in any one ETF; this says wi ≤ 0.1.  Common constraints for this case would be that you do not invest more than 25% in any one ETF, and if there is no short selling, that you invest at least 10% in every ETF.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: