Excel Homework

Assignment_1

Average –> Covariance –> Correlation –>

Standard Deviation–> Covariance (excel Formula) Correlation (excel function) –>

Prices Risk-Free Rate Returns Deviation from Mean Deviation from Mean Product

S&P 500 Real Estate IVV IYR IVV IYR

Date (IVV) (IYR)

6/1/00 98.627174 37 0.474%

7/1/00 96.978729 37.296875 0.497%

8/1/00 103.307556 36.734375 0.508%

9/1/00 97.498184 37.875 0.500%

10/1/00 97.300697 35.275002 0.509%

11/1/00 89.940231 36 0.514%

12/1/00 89.292358 37.974998 0.481%

1/1/01 93.356224 38.150002 0.429%

2/1/01 84.607117 37.625 0.407%

3/1/01 79.594917 37.470001 0.368%

4/1/01 85.546074 38.68 0.323%

5/1/01 85.908195 39.564999 0.302%

6/1/01 83.831001 41.165001 0.291%

7/1/01 83.111382 40.455002 0.293%

8/1/01 78.267181 41.549999 0.280%

9/1/01 71.531952 39.630001 0.220%

10/1/01 72.532288 37.209999 0.180%

11/1/01 78.459099 39.650002 0.156%

12/1/01 78.631042 39.900002 0.141%

1/1/02 78.047554 39.945 0.138%

2/1/02 76.598907 40.735001 0.143%

3/1/02 78.978798 42.599998 0.149%

4/1/02 74.654388 42.724998 0.143%

5/1/02 74.05275 43.549999 0.144%

6/1/02 68.478714 43.599998 0.142%

7/1/02 63.393661 41.544998 0.140%

8/1/02 63.692108 41.695 0.135%

9/1/02 56.84119 39.075001 0.136%

10/1/02 61.840359 37.494999 0.132%

11/1/02 65.38208 39.310001 0.103%

12/1/02 61.596359 38.650002 0.099%

1/1/03 60.362286 37.224998 0.098%

2/1/03 59.521702 37.945 0.098%

3/1/03 59.409615 38.325001 0.094%

4/1/03 64.71302 39.705002 0.094%

5/1/03 68.215553 42.174999 0.089%

6/1/03 68.771164 42.299999 0.077%

7/1/03 70.211525 44.625 0.075%

8/1/03 71.53167 45 0.079%

9/1/03 70.522156 45.705002 0.078%

10/1/03 74.640526 46.5 0.077%

11/1/03 75.547821 48.52 0.078%

12/1/03 78.836823 49.634998 0.075%

1/1/04 81.209419 51.654999 0.073%

2/1/04 82.231621 52.584999 0.078%

3/1/04 80.937843 54.724998 0.078%

4/1/04 79.7005 46.549999 0.078%

5/1/04 81.034691 49.720001 0.085%

6/1/04 82.182419 50.514999 0.106%

7/1/04 79.890488 50.650002 0.111%

8/1/04 80.048851 54.825001 0.123%

9/1/04 80.48082 53.98 0.138%

10/1/04 81.82769 56.755001 0.147%

11/1/04 85.456467 59.165001 0.173%

12/1/04 87.465996 61.599998 0.183%

1/1/05 86.104218 56.349998 0.194%

2/1/05 87.894859 58.23 0.212%

3/1/05 85.762093 56.099998 0.228%

4/1/05 84.752396 59.255001 0.232%

5/1/05 87.418747 61.474998 0.237%

6/1/05 87.25029 63.599998 0.248%

7/1/05 90.90126 67.800003 0.268%

8/1/05 90.019417 64.889999 0.287%

9/1/05 90.386871 64.269997 0.285%

10/1/05 88.813705 62.299999 0.309%

11/1/05 92.510628 65.25 0.323%

12/1/05 91.994041 64.150002 0.324%

1/1/06 94.648819 68.830002 0.353%

2/1/06 95.152588 70.050003 0.369%

3/1/06 96.256538 73.5 0.376%

4/1/06 97.888519 71.099998 0.383%

5/1/06 95.02478 68.75 0.393%

6/1/06 94.875999 71.25 0.399%

7/1/06 95.651176 73.849998 0.413%

8/1/06 97.757622 76.050003 0.413%

9/1/06 99.908958 77.150002 0.401%

10/1/06 103.590508 82.150002 0.410%

11/1/06 105.624176 85.949997 0.412%

12/1/06 106.562202 83.349998 0.404%

1/1/07 108.622742 91.400002 0.415%

2/1/07 106.563881 88.129997 0.419%

3/1/07 107.348206 85.239998 0.412%

4/1/07 112.478378 85.489998 0.406%

5/1/07 116.319069 85.620003 0.394%

6/1/07 114.016167 77.419998 0.384%

7/1/07 110.868759 70.400002 0.402%

8/1/07 112.322083 74.099998 0.350%

9/1/07 116.392769 76.470001 0.324%

10/1/07 118.570908 77.050003 0.325%

11/1/07 113.694496 70.019997 0.273%

12/1/07 112.15815 65.699997 0.250%

1/1/08 105.686043 65.769997 0.229%

2/1/08 102.773796 63.009998 0.177%

3/1/08 101.605812 65.099998 0.105%

4/1/08 107.0522 68.879997 0.108%

5/1/08 108.426918 69.440002 0.144%

6/1/08 98.857788 60.799999 0.155%

7/1/08 98.594437 62.299999 0.136%

8/1/08 100.061111 63.43 0.143%

9/1/08 90.65564 61.950001 0.094%

10/1/08 76.009682 42.549999 0.056%

11/1/08 70.335678 33.380001 0.016%

12/1/08 70.483963 37.23 0.003%

1/1/09 65.251999 31.34 0.011%

2/1/09 58.362694 25.139999 0.025%

3/1/09 62.617367 25.459999 0.018%

4/1/09 69.438934 33 0.013%

5/1/09 73.493301 33.75 0.015%

6/1/09 73.129066 32.419998 0.015%

7/1/09 78.950523 35.849998 0.015%

8/1/09 81.840393 40.580002 0.014%

9/1/09 84.395905 42.669998 0.010%

10/1/09 83.176414 40.549999 0.006%

11/1/09 88.240448 43.419998 0.004%

12/1/09 89.448441 45.919998 0.004%

1/1/10 86.586945 43.43 0.005%

2/1/10 89.313385 45.799999 0.009%

3/1/10 94.372208 49.779999 0.013%

4/1/10 96.306892 52.959999 0.013%

5/1/10 88.601677 49.950001 0.013%

6/1/10 83.561806 47.209999 0.010%

7/1/10 89.804543 51.650002 0.013%

8/1/10 85.829102 50.98 0.013%

9/1/10 92.887543 52.880001 0.013%

10/1/10 96.992149 54.959999 0.011%

11/1/10 96.926872 54.09 0.012%

12/1/10 102.996521 55.959999 0.012%

1/1/11 105.876419 57.959999 0.013%

2/1/11 109.540894 60.580002 0.011%

3/1/11 109.04081 59.400002 0.008%

4/1/11 112.77462 62.169998 0.005%

5/1/11 111.432236 62.799999 0.003%

6/1/11 109.052246 60.299999 0.003%

7/1/11 107.403793 60.43 0.003%

8/1/11 101.471367 57.220001 0.002%

9/1/11 94.066223 50.57 0.001%

10/1/11 104.662254 57.240002 0.002%

11/1/11 104.329483 55.139999 0.001%

12/1/11 104.795395 56.810001 0.001%

1/1/12 110.306908 60.560001 0.003%

2/1/12 114.952934 60.139999 0.008%

3/1/12 118.209297 62.299999 0.007%

4/1/12 117.960205 63.91 0.007%

5/1/12 110.871506 61.220001 0.008%

6/1/12 114.991844 63.939999 0.008%

7/1/12 116.972992 65.43 0.008%

8/1/12 119.870895 65.629997 0.008%

9/1/12 121.999969 64.379997 0.009%

10/1/12 120.18531 64 0.008%

11/1/12 121.230171 63.630001 0.008%

12/1/12 121.595406 64.669998 0.006%

1/1/13 128.632751 67.269997 0.006%

2/1/13 130.376785 68.120003 0.008%

3/1/13 134.531815 69.480003 0.008%

4/1/13 137.825424 73.459999 0.005%

5/1/13 141.132523 68.690002 0.003%

6/1/13 138.194778 66.43 0.004%

7/1/13 146.380524 66.589996 0.003%

8/1/13 141.934296 62.240002 0.003%

9/1/13 145.819336 63.790001 0.002%

10/1/13 153.29007 66.169998 0.004%

11/1/13 157.862167 63.099998 0.006%

12/1/13 161.0634 63.080002 0.006%

1/1/14 156.258133 65.25 0.003%

2/1/14 163.383392 68.349998 0.004%

3/1/14 164.081055 67.669998 0.004%

4/1/14 166.038269 69.720001 0.003%

5/1/14 169.831375 71.669998 0.003%

6/1/14 172.573242 71.790001 0.003%

7/1/14 170.953949 71.709999 0.003%

8/1/14 177.739304 74.199997 0.003%

9/1/14 174.483032 69.199997 0.002%

10/1/14 179.482742 74.980003 0.002%

11/1/14 184.425415 77.019997 0.002%

12/1/14 182.913422 76.839996 0.003%

1/1/15 178.544601 81.230003 0.003%

2/1/15 188.624252 79.120003 0.002%

3/1/15 184.731033 79.32 0.003%

4/1/15 187.415833 75.5 0.002%

5/1/15 189.853989 75.25 0.002%

6/1/15 185.066956 71.300003 0.002%

7/1/15 190.168793 74.830002 0.003%

8/1/15 178.485321 70.480003 0.006%

9/1/15 173.061157 70.949997 0.002%

10/1/15 188.782333 75.360001 0.002%

11/1/15 189.522797 75.209999 0.010%

12/1/15 185.007584 75.080002 0.019%

1/1/16 176.920807 72 0.022%

2/1/16 176.820816 71.459999 0.026%

3/1/16 187.856781 77.860001 0.024%

4/1/16 188.593094 76.559998 0.019%

5/1/16 191.783875 78.25 0.023%

6/1/16 191.356613 82.389999 0.023%

7/1/16 199.458664 85.400002 0.025%

8/1/16 199.714401 82.540001 0.025%

9/1/16 198.718811 80.639999 0.024%

10/1/16 196.174973 76.639999 0.028%

11/1/16 203.372345 74.849998 0.038%

12/1/16 206.548752 76.940002 0.043%

1/1/17 211.369125 77.040001 0.043%

2/1/17 219.771088 80.410004 0.043%

3/1/17 219.069397 78.489998 0.062%

4/1/17 222.153015 78.93 0.067%

5/1/17 225.250336 78.839996 0.074%

6/1/17 225.723267 79.769997 0.082%

7/1/17 231.45726 80.660004 0.089%

8/1/17 232.090744 81.199997 0.084%

9/1/17 235.621399 79.879997 0.086%

10/1/17 242.33432 79.919998 0.089%

11/1/17 249.899963 81.970001 0.103%

12/1/17 251.735184 81.010002 0.110%

1/1/18 267.314667 78.559998 0.118%

2/1/18 257.145111 73.330002 0.131%

3/1/18 249.647385 75.470001 0.142%

4/1/18 251.662262 75.639999 0.147%

5/1/18 257.729248 78.190002 0.155%

6/1/18 258.031586 80.580002 0.158%

7/1/18 268.955841 81.25 0.163%

8/1/18 277.652679 83.18 0.169%

9/1/18 277.92804 80.019997 0.178%

10/1/18 260.097015 78.110001 0.188%

11/1/18 265.093689 81.779999 0.194%

12/1/18 239.928848 74.940002 0.198%

1/1/19 260.709656 83.540001 0.198%

2/1/19 269.129639 84.150002 0.199%

3/1/19 273.200378 87.040001 0.200%

4/1/19 285.254913 87 0.198%

5/1/19 267.287903 86.870003 0.196%

6/1/19 284.10788 87.309998 0.181%

7/1/19 290.197693 89.389999 0.175%

8/1/19 285.38739 92.459999 0.163%

9/1/19 289.509125 93.540001 0.158%

10/1/19 297.22699 94.260002 0.138%

11/1/19 308.035461 93.199997 0.128%

12/1/19 315.033142 93.080002 0.128%

1/1/20 317.059235 94.57 0.127%

2/1/20 290.251831 87.400002 0.127%

3/1/20 253.459061 69.57 0.024%

4/1/20 287.38205 76.050003 0.012%

5/1/20 301.220123 77.419998 0.011%

6/1/20 305.6716 78.809998 0.013%

7/1/20 324.908112 82.059998 0.011%

8/1/20 347.654236 82.25 0.008%

9/1/20 333.074921 79.839996 0.009%

10/1/20 326.185181 77.510002 0.008%

11/1/20 361.728851 84.160004 0.008%

12/1/20 373.746002 85.650002 0.008%

Assignment_2

Expected Return Standard Deviation

IVV 0.518% 4.415%

IYR 0.396% 5.826%

Correlation 0.200

Covariance 0.001

Risk-Free 0.10%

Risk-Aversion 2

Portfolio Portfolio Tangency Portfolio Portfolio Portfolio

Weight: IVV Weight: IYR Expected Return Standard Deviation Mean-Variance Utility Weight IVV–> Weight Tangency Weight Risk-Free Expected Return Standard Deviation

1 0 Weight IYR –> 1 0

0.9 0.1 Expected Return –> 0.9 0.1

0.8 0.2 Standard Deviation –> 0.8 0.2

0.7 0.3 0.7 0.3

0.6 0.4 0.6 0.4

0.5 0.5 Optimal Asset Allocation 0.5 0.5

0.4 0.6 Weight Portfolio (IVV+IYR) –> 0.4 0.6

0.3 0.7 Weight Risk-Free –> 0.3 0.7

0.2 0.8 0.2 0.8

0.1 0.9 0.1 0.9

0 1 0 1

Set of Feasible Portfolios

Capital Allocation Line

Assignment_3

Expected Return Correlation Coefficients

Return Std. Dev. US AUS CAN MEX JAPAN SING FRA GER ITA SPAIN SWED SWITZ UK

US 5.50% 19.30% 1.00

AUS 8.50% 25.60% 0.70 1.00

CAN 9.30% 24.00% 0.75 0.75 1.00

MEX 12.00% 32.20% 0.72 0.65 0.64 1.00

JAPAN -2.20% 23.00% 0.53 0.55 0.52 0.48 1.00

SING 1.80% 29.90% 0.59 0.59 0.56 0.56 0.53 1.00

FRA 5.10% 24.60% 0.79 0.73 0.72 0.66 0.56 0.60 1.00

GER 4.50% 26.50% 0.79 0.70 0.73 0.68 0.54 0.61 0.90 1.00

ITA 4.10% 26.70% 0.70 0.70 0.65 0.61 0.48 0.55 0.86 0.82 1.00

SPAIN 8.60% 26.00% 0.68 0.67 0.64 0.64 0.46 0.55 0.83 0.81 0.82 1.00

SWED 7.80% 30.80% 0.74 0.69 0.71 0.65 0.49 0.58 0.83 0.83 0.77 0.76 1.00

SWITZ 5.40% 20.90% 0.70 0.64 0.64 0.57 0.50 0.53 0.79 0.79 0.74 0.74 0.72 1.00

UK 4.40% 21.80% 0.79 0.72 0.71 0.68 0.54 0.62 0.83 0.81 0.76 0.73 0.75 0.75 1.00

US AUS CAN MEX JAPAN SING FRA GER ITA SPAIN SWED SWITZ UK

0.00% 0.00% 100.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00%

US 0.00% 0.0372 0.0346 0.0347 0.0447 0.0235 0.0340 0.0375 0.0404 0.0361 0.0341 0.0440 0.0282 0.0332

AUS 0.00% 0.0346 0.0655 0.0461 0.0536 0.0324 0.0452 0.0460 0.0475 0.0478 0.0446 0.0544 0.0342 0.0402

CAN 100.00% 0.0347 0.0461 0.0576 0.0495 0.0287 0.0402 0.0425 0.0464 0.0417 0.0399 0.0525 0.0321 0.0371

MEX 0.00% 0.0447 0.0536 0.0495 0.1037 0.0355 0.0539 0.0523 0.0580 0.0524 0.0536 0.0645 0.0384 0.0477

JAPAN 0.00% 0.0235 0.0324 0.0287 0.0355 0.0529 0.0364 0.0317 0.0329 0.0295 0.0275 0.0347 0.0240 0.0271

SING 0.00% 0.0340 0.0452 0.0402 0.0539 0.0364 0.0894 0.0441 0.0483 0.0439 0.0428 0.0534 0.0331 0.0404

FRA 0.00% 0.0375 0.0460 0.0425 0.0523 0.0317 0.0441 0.0605 0.0587 0.0565 0.0531 0.0629 0.0406 0.0445

GER 0.00% 0.0404 0.0475 0.0464 0.0580 0.0329 0.0483 0.0587 0.0702 0.0580 0.0558 0.0677 0.0438 0.0468

ITA 0.00% 0.0361 0.0478 0.0417 0.0524 0.0295 0.0439 0.0565 0.0580 0.0713 0.0569 0.0633 0.0413 0.0442

SPAIN 0.00% 0.0341 0.0446 0.0399 0.0536 0.0275 0.0428 0.0531 0.0558 0.0569 0.0676 0.0609 0.0402 0.0414

SWED 0.00% 0.0440 0.0544 0.0525 0.0645 0.0347 0.0534 0.0629 0.0677 0.0633 0.0609 0.0949 0.0463 0.0504

SWITZ 0.00% 0.0282 0.0342 0.0321 0.0384 0.0240 0.0331 0.0406 0.0438 0.0413 0.0402 0.0463 0.0437 0.0342

UK 0.00% 0.0332 0.0402 0.0371 0.0477 0.0271 0.0404 0.0445 0.0468 0.0442 0.0414 0.0504 0.0342 0.0475

100.00% 0 0 0.0576 0 0 0 0 0 0 0 0 0 0

Portfolio Return 9.30%

Portfolio St.Dev 24.00%

Short Sales Allowed Short Sales Not Allowed

US

AUS

CAN

MEX

JAPAN

SING

FRA

GER

ITA

SPAIN

SWED

SWITZ

UK

Target Return 7.00% 7.00%