Excel Homework

profileDuke1899!
Excel_Assignment_1_2_3.xlsx
Home>Business & Finance homework help>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%