Financial Engineering 3

profileshoomoosh
Lecture09dmInClassExercise1.xlsx
Home>Business & Finance homework help>Financial markets homework help>Financial Engineering 3

Example

Objective Adj Price Date APPL YHOO KO RoR APPL YHOO KO Summary Statistics
Semiannual Return 14% 1/25/10 203.07 15.86 51.21 APPL YHOO KO
Days in year 252 1/26/10 205.94 15.99 51.01 Average Daily RoR
Days in half year 126 1/27/10 207.88 15.98 51.36 StDev
1/28/10 199.29 15.44 51.05 Variance
1/29/10 192.06 15.01 51.12 Semiannual RoR
2/1/10 194.73 15.05 51.24 Semiannual StDev
2/2/10 195.86 15.17 51.87
2/3/10 199.23 15.46 51.7 Covariance Matrix
2/4/10 192.05 15.01 50.34
2/5/10 195.46 15.19 50.02
2/8/10 194.12 14.99 49.61
2/9/10 196.19 15.07 50.89
2/10/10 195.12 14.8 50.68
2/11/10 198.67 15.22 51.09 Markowitz' Equations
2/12/10 200.38 15.17 50.86 w1 w2 w3 λ1 λ2
2/16/10 203.4 15.41 51.65 dL/dw1 =
2/17/10 202.55 15.44 52.13 dL/dw2 =
2/18/10 202.93 15.54 52.68 dL/dw3 =
2/19/10 201.67 15.58 52.5 dL/dλ1 =
2/22/10 200.42 15.49 52.18 dL/dλ2 =
2/23/10 197.06 15.38 51.66
2/24/10 200.66 15.59 51.97 Inverse
2/25/10 202 15.24 50.05
2/26/10 204.62 15.31 49.68
3/1/10 208.99 15.79 50.19
3/2/10 208.85 15.73 50.22
3/3/10 209.33 15.57 50.82
3/4/10 210.71 15.81 51.32
3/5/10 218.95 16.06 51.54 Solution
3/8/10 219.08 16.52 51.32 Weight Original Zero Neg Adjusted
3/9/10 223.02 16.53 51.05 w1
3/10/10 224.84 16.79 51.07 w2
3/11/10 225.5 16.53 50.92 w3
3/12/10 226.6 16.32 50.68 λ1
3/15/10 223.84 16.46 50.97 λ2
3/16/10 224.45 16.36 51.01 Total w
3/17/10 224.12 16.5 51.15
3/18/10 224.65 16.56 51.25 WxW' Matrix
3/19/10 222.25 16.44 52.01
3/22/10 224.75 16.34 51.81
3/23/10 228.36 16.03 52.53
3/24/10 229.37 16.09 51.89
3/25/10 226.65 16.32 52.06 Weighted Covariance Matrix
3/26/10 230.9 16.54 51.92
3/29/10 232.39 16.56 52.03
3/30/10 235.85 16.61 52.12
3/31/10 235 16.53 52.25
4/1/10 235.97 16.29 52.53 Ta-da!
4/5/10 238.49 16.51 52.11 Var =
4/6/10 239.54 16.92 51.57 StDev =
4/7/10 240.6 16.87 51.13
4/8/10 239.95 17.35 51.07
4/9/10 241.79 17.52 51.86
4/12/10 242.29 17.64 52.02
4/13/10 242.43 18.18 52.27
4/14/10 245.69 18.38 52.2
4/15/10 248.92 18.97 51.54
4/16/10 247.4 18.17 52.22
4/19/10 247.07 18.39 52.55
4/20/10 244.59 18.38 51.74
4/21/10 259.22 17.45 51.48
4/22/10 266.47 17.72 51.59
4/23/10 270.83 17.64 51.18
4/26/10 269.5 17.39 51.04
4/27/10 262.04 16.92 50.22
4/28/10 261.6 16.75 50.69
4/29/10 268.64 16.97 51.05
4/30/10 261.09 16.53 50.78
5/3/10 266.35 16.95 51.07
5/4/10 258.68 16.32 50.52
5/5/10 255.99 16.49 50.98
5/6/10 246.25 15.92 49.68
5/7/10 235.86 15.29 50.03
5/10/10 253.99 16.33 51.34
5/11/10 256.52 16.41 50.93
5/12/10 262.09 16.47 51.32
5/13/10 258.36 16.14 50.81
5/14/10 253.82 16.39 50.67
5/17/10 254.22 16.27 50.74
5/18/10 252.36 16.03 50.44
5/19/10 248.34 15.79 50.46
5/20/10 237.76 15.1 48.96
5/21/10 242.32 15.48 49.01
5/24/10 246.76 15.54 48.89
5/25/10 245.22 15.31 48.09
5/26/10 244.11 15.45 47.57
5/27/10 253.35 15.69 48.67
5/28/10 256.88 15.34 48.83
6/1/10 260.83 15.02 48.72
6/2/10 263.95 15.18 49.79
6/3/10 263.12 15.43 50.11
6/4/10 255.96 15 48.7
6/7/10 250.94 14.94 48.26
6/8/10 249.33 14.79 48.98
6/9/10 243.2 14.69 48.78
6/10/10 250.51 15.1 49.83
6/11/10 253.51 15.29 49.48
6/14/10 254.28 15.17 49.35
6/15/10 259.69 15.65 49.99
6/16/10 267.25 15.49 50.2
6/17/10 271.87 15.6 50.16
6/18/10 274.07 15.54 50.11
6/21/10 270.17 15.21 50.28
6/22/10 273.85 15.09 50.26
6/23/10 270.97 15.23 50.05
6/24/10 269 14.83 49.62
6/25/10 266.7 14.81 48.15
6/28/10 268.3 14.73 48.93
6/29/10 256.17 14.04 48.22
6/30/10 251.53 13.84 48.01
7/1/10 248.48 14.09 47.93
7/2/10 246.94 14.07 47.95
7/6/10 248.63 14.13 48.31
7/7/10 258.67 14.4 49.31
7/8/10 258.09 14.6 50.22
7/9/10 259.62 14.89 50.2
7/12/10 257.29 14.94 50.25
7/13/10 251.8 15.52 50.51
7/14/10 252.73 15.37 50.6
7/15/10 251.45 15.37 50.63
7/16/10 249.9 14.9 50.17
7/19/10 245.58 15.1 50.07
7/20/10 251.89 15.2 51
7/21/10 254.24 13.91 51.81
7/22/10 259.02 13.88 51.98
7/23/10 259.94 13.99 52.45
7/26/10 259.28 14.15 52.63
7/27/10 264.08 13.95 52.74
7/28/10 260.96 13.87 52.58
7/29/10 258.11 13.76 52.68
7/30/10 257.25 13.88 52.8
8/2/10 261.85 14 54.04
8/3/10 261.93 13.94 54.01
8/4/10 262.98 14.18 54.17
8/5/10 261.7 14.16 54
8/6/10 260.09 14.34 54.37
8/9/10 261.75 14.4 54.71
8/10/10 259.41 14.35 54.59
8/11/10 250.19 13.87 53.69
8/12/10 251.79 13.85 53.35
8/13/10 249.1 13.83 53.39
8/16/10 247.64 13.79 53.53
8/17/10 251.97 13.94 53.76
8/18/10 253.07 13.99 53.49
8/19/10 249.88 13.85 52.96
8/20/10 249.64 13.79 52.98
8/23/10 245.8 13.65 53.26
8/24/10 239.93 13.4 53.32
8/25/10 242.89 13.26 53.06
8/26/10 240.28 13.21 52.84
8/27/10 241.62 13.43 53.8
8/30/10 242.5 13.18 53.22
8/31/10 243.1 13.11 53.53
9/1/10 250.33 13.37 54.9
9/2/10 252.17 13.51 54.97
9/3/10 258.77 13.62 55.14
9/7/10 257.81 13.53 55.21
9/8/10 262.92 13.75 55.4
9/9/10 263.07 13.65 55.84
9/10/10 263.41 13.68 56.06
9/13/10 267.04 13.73 56.01
9/14/10 268.06 13.63 55.59
9/15/10 270.22 14.27 55.43
9/16/10 276.57 14.19 55.51
9/17/10 275.37 13.89 55.56
9/20/10 283.23 13.86 55.96
9/21/10 283.77 14.18 56.09
9/22/10 287.75 14.04 56.21
9/23/10 288.92 14.17 55.86
9/24/10 292.32 14.5 56.58
9/27/10 291.16 14.28 56.56
9/28/10 286.86 14.39 57.05
9/29/10 287.37 14.34 56.72
9/30/10 283.75 14.17 56.49
10/1/10 282.52 14.27 57.07
10/4/10 278.64 14.28 56.83
10/5/10 288.94 14.61 57.59
10/6/10 289.19 14.52 57.72
10/7/10 289.22 14.23 57.37
10/8/10 294.07 14.49 57.35
10/11/10 295.36 14.41 57.49
10/12/10 298.54 14.43 57.53
10/13/10 300.14 15.25 57.86
10/14/10 302.31 15.93 57.83
10/15/10 314.74 16.25 57.86
10/18/10 318 15.93 57.92
10/19/10 309.49 15.49 58.24
10/20/10 310.53 15.8 59.03
10/21/10 309.52 15.97 59.33
10/22/10 307.47 16.31 59.47
10/25/10 308.84 16.4 58.89
10/26/10 308.05 16.46 59.11
10/27/10 307.83 16.42 59.06
10/28/10 305.24 16.4 59.08
10/29/10 300.98 16.49 59.19
11/1/10 304.18 16.15 59.57
11/2/10 309.36 16.19 59.71
11/3/10 312.8 16.17 59.8
11/4/10 318.27 16.2 60.28
11/5/10 317.13 16.27 60.41
11/8/10 318.62 16.44 60.29
11/9/10 316.08 16.97 60.46
11/10/10 318.03 16.94 60.38
11/11/10 316.65 16.8 60.62
11/12/10 308.03 16.55 60.73
11/15/10 307.04 16.6 60.77
11/16/10 301.59 16.24 59.92
11/17/10 300.5 16.15 60.35
11/18/10 308.43 16.99 61.62
11/19/10 306.73 16.57 62.09
11/22/10 313.36 16.56 62.04
11/23/10 308.73 16.19 61.41
11/24/10 314.8 16.41 62.37
11/26/10 315 16.22 61.88
11/29/10 316.87 16.38 61.61
11/30/10 311.15 15.82 61.4
12/1/10 316.4 16.15 62.88
12/2/10 318.15 16.33 63.08
12/3/10 317.44 16.35 62.69
12/6/10 320.15 16.33 62.19
12/7/10 318.21 16.94 62.34
12/8/10 321.01 17.02 62.58
12/9/10 319.76 16.95 63.01
12/10/10 320.56 17.01 62.84
12/13/10 321.67 16.7 63.03
12/14/10 320.29 16.63 62.11
12/15/10 320.36 16.45 62.92
12/16/10 321.25 16.51 63.51
12/17/10 320.61 16.38 63.86
12/20/10 322.21 16.28 63.48
12/21/10 324.2 16.6 63.65
12/22/10 325.16 16.63 63.75
12/23/10 323.6 16.72 63.74
12/27/10 324.68 16.48 63.24
12/28/10 325.47 16.43 63.53
12/29/10 325.29 16.61 63.61
12/30/10 323.66 16.76 63.66
12/31/10 322.56 16.63 63.92
1/3/11 329.57 16.75 63.39
1/4/11 331.29 16.59 62.08
1/5/11 334 16.91 61.71
1/6/11 333.73 17.06 61.26
1/7/11 336.12 16.9 61.15
1/10/11 342.45 16.6 61.29
1/11/11 341.64 16.58 60.93
1/12/11 344.42 16.65 61.27
1/13/11 345.68 16.75 61.62
1/14/11 348.48 16.81 61.36
1/18/11 340.65 16.5 61.7
1/19/11 338.84 16.31 61.64
1/20/11 332.68 16.23 61.16
1/21/11 326.72 15.97 61.01
1/24/11 337.45 16.09 61.47

,1

Var

n

ijij

ij

ww

s

=

=

å

,1Varnijijijww

-1Ax=yx=Ay

21121211212112221222112211221010 1LwwrwLwwrwLwrwrrLww