Financial Engineering 3

References: Villalobos, Luenberger, Faerber

Lecture 9

Stock Portfolio Analysis

Lecture Topics • Quick Markowitz review • Seven stock portfolio analysis example. • Observations • Log-normal distribution in Financial Engineering

Markowitz from Lecture 08 • To find a minimum variance portfolio, we can fix the mean value

at some arbitrary point r. • Then we can find the feasible portfolio of minimum variance

that has this mean. • The formulation of the problem is:

• This problem is solved with Lagrangian Multipliers. • We solve a series of equations to find the weights (amount to

invest) of the assets in a portfolio.

, 1

1

1

1 Minimize

2 Subject to:

1

n i j iji j

n i ii

n ii

w w

w r r

w

σ =

=

=

=

=

∑

∑ ∑

• The Markowitz’ selection portfolio has a quadratic objective function that is subject to linear constraints.

– This kind of problems can be solved by using Lagrangian multipliers as follows:

• L is known as the Lagrangian of the original problem. • By differentiating L with respect to the decision variables, in

this case the weights of the assets in the portfolio, and setting these equations to zero and using the original linear constraints, results in a series of linear equations whose solution gives the optimal portfolio.

• If short selling is not allowed (the weights are not allowed to be negative), quadratic programming needs to be used.

• However, for simple cases Excel can be used.

( ) ( )1 2 1

L 12111,

−−−−= ∑∑∑ === n

i ii n

i iijj n

ji i wrrwww λλσ

Markowitz from Lecture 8

• We showed that for a two asset portfolio, we would have the following equations to solve:

• We can use gaussian elimination or other methods to solve.

Markowitz from Lecture 8

( ) ( ) ( )2 2 2 21 1 1 2 12 2 1 12 2 2 1 1 1 2 2 2 1 2 1

L 1 2

w w w w w w w r w r r w wσ σ σ σ λ λ= + + + − + − − + −

2 1 1 2 12 1 1 2

1

2 2 2 1 12 1 2 2

2

1 1 2 2 1

1 2 2

1

L w w r

w L

w w r w L

w r w r r

L w w

σ σ λ λ

σ σ λ λ

λ

λ

∂ = + − −

∂ ∂

= + − − ∂ ∂

= + = ∂ ∂

= + = ∂

2 1 1 2 12 1 1 2

1

2 1 12 2 2 1 2 2

2

1 1 2 2 1

1 2 2

1 0

1 0

1

L w w r

w L

w w r w L

w r w r r

L w w

σ σ λ λ

σ σ λ λ

λ

λ

∂ = + − − =

∂ ∂

= + − − = ∂ ∂

= + = ∂ ∂

= + = ∂

Two Asset Example • Find the right amount to invest between the stocks of Intel (r1 =

0.16103, σ1 = 0.4151) and IBM (r2 = 0.11791, σ2 = 0.2388, σ12 = 0.1032) if you have $100,000 to invest and the targeted return is a semi-annual 13% (the return and standard deviations are also in semi-annual basis).

( ) ( ) ( )( ) ( ) ( )( ) ( )

2 22 2 1 1 2 2

1 1 2 2 1 2

1 L 0.4151 2 0.1032 0.2388

2 0.16103 0.1179 0.13 1

w w w w

w w w wλ λ

= + + −

+ − − + −

( ) ( ) ( ) 212 2

1 1

16103.01032.04151.0 L

λλ −−+= ∂ ∂

ww w

( ) ( ) ( ) 211 2

2 2

1179.01032.02388.0 L

λλ −−+= ∂ ∂

ww w

( ) ( ) 13.01179.016103.0L 21 1

+−−= ∂ ∂

ww λ

1 L

21 2

+−−= ∂ ∂

ww λ

• Our four equations to solve simultaneously are:

Two Asset Example

( ) ( ) ( )2 1 2 1 2 1

L 0.4151 0.1032 0.16103 1 0w w

w λ λ

∂ = + − − =

∂

( ) ( ) ( )21 2 1 2 2

L 0.1032 0.2388 0.1179 1 0w w

w λ λ

∂ = + − − =

∂

( ) ( )1 2 1 2 1

L 0.16103 0.1179 0 0 0.13w w λ λ

λ ∂

= + + + = ∂

1 2 1 1 2

L 1 1 0 0 1w w λ λ

λ ∂

= + + + = ∂

In Matrix Form The coefficients in the yellow correspond to the covariance matrix of the stocks.

The coefficients in pink corresponds to the expected returns

0.07383- 1.21965 0.719453 0.280547

2

1

2

1

=

   





   





λ λ w w

→ -1Ax = y x = A y

   





   





=

   





   





   





   





1.00000 0.13000 0.00000 0.00000

0.000000.000001.000001.00000 0.000000.000000.117900.16103 1.00000-0.11790-0.057030.10320 1.00000-0.16103-0.103200.17231

2

1

2

1

λ λ w w

In Matrix Form • Fortunately, Excel, and several other software packages can

perform this for us.

• I suggest students take the IEE 320 Extreme Excel class.

• Other sources for Excel training or help is readily available online.

• Learn how to: – Perform short cuts for copy and paste – we use large

datasets! – Data analysis tool packs to create covariance matrices – Statistical functions for sample standard deviation and

variance – Linear algebra including matrix inverting, matrix transpose,

and matrix multiplication

Let’s work a small three stock example in class in Excel!

Example • Consider the following average returns and standard deviations

of several stocks. – See Excel file “Lecture 09 Old Examples” tab “First

Allocation”:

• So, we can use the Markowitz Model to find an optimal portfolio with a desired return of 13% (semi-annual).

Stock Avg ROR StDev A 0.00075 0.01608 B 0.00178 0.02079 C 0.00233 0.02370 D 0.00117 0.01594 E 0.00054 0.01409 F 0.00430 0.04768 G 0.00146 0.02094

Solution • If we use the data for average daily return, StDev, covariance,

and the desired return of 13% semi-annual, we can solve the system of equations that these values produced to get the weights, or relative amounts of each stock to acquire.

• We have two years of daily stock closing prices for seven stocks.

– We will split the data into two allocations, the first year of data and second year of data.

• We will show the solution in Excel.

• “Lecture 09 Old Examples” Excel file can be found on the Blackboard.

Stock Closing Prices Various Stock Closing Prices

Date A B C D E F G 12-Apr-01 12.14 25.18 7.12 18.28 21.76 2.75 13.48 13-Apr-01 12.1 25.78 7.24 18.51 21.81 2.69 13.65 14-Apr-01 12.03 26.09 7.75 18.79 21.87 2.4 13.82 15-Apr-01 11.93 23.77 7.67 18.69 21.14 2.25 13.57 16-Apr-01 12 23.83 7.85 18.9 21.03 2.44 13.82 17-Apr-01 11.82 23.8 7.79 18.74 20.91 2.41 13.57 18-Apr-01 11.75 24.22 8.09 18.56 21.36 2.35 13.57 19-Apr-01 11.72 24.17 8.13 18.67 21.19 2.33 13.98 20-Apr-01 11.63 24.11 8.39 18.51 21.14 2.42 14.15 21-Apr-01 11.58 24.45 8.31 18.59 21.31 2.6 13.98 22-Apr-01 11.65 24.34 8.27 18.69 21.59 2.65 13.82 23-Apr-01 11.75 24.53 8.17 18.95 21.7 2.58 13.65 24-Apr-01 11.61 24.34 8.13 19.08 21.53 2.48 13.65 25-Apr-01 11.71 24.36 8.14 19.13 21.98 2.63 13.69 26-Apr-01 11.61 24.36 8.16 18.46 21.31 2.74 12.98 27-Apr-01 11.85 24.42 8.24 18.46 20.97 2.67 12.9 28-Apr-01 12.46 24.17 8.25 18.51 21.42 2.51 12.24 29-Apr-01 12.27 23.91 8.14 18.51 21.25 2.53 13.07 30-Apr-01 12.27 24.23 8.25 18.95 21.53 2.52 12.65 1-May-01 12.2 24.49 8.23 18.74 21.19 2.56 12.73 2-May-01 12.44 24.35 8.29 18.9 21.31 2.6 13.32 3-May-01 12.51 24.63 8.61 19.1 21.48 2.52 13.48

Daily Rate of Returns Daily Rate of Return

A B C D E F G -0.003294893 0.023828435 0.016853933 0.012582057 0.002297794 -0.021818182 0.012611276 -0.005785124 0.012024825 0.070441989 0.015126958 0.002751032 -0.107806691 0.012454212 -0.008312552 -0.088922959 -0.010322581 -0.00532198 -0.033379058 -0.0625 -0.018089725 0.005867561 0.00252419 0.023468057 0.011235955 -0.005203406 0.084444444 0.018422992

-0.015 -0.001258917 -0.007643312 -0.008465608 -0.005706134 -0.012295082 -0.018089725 -0.005922166 0.017647059 0.038510911 -0.009605123 0.021520803 -0.024896266 0 -0.002553191 -0.00206441 0.004944376 0.005926724 -0.007958801 -0.008510638 0.030213707 -0.007679181 -0.002482416 0.03198032 -0.008569898 -0.002359604 0.038626609 0.012160229 -0.004299226 0.014102032 -0.009535161 0.004321988 0.008041627 0.074380165 -0.012014134 0.006044905 -0.004498978 -0.004813478 0.005379236 0.013139371 0.019230769 -0.011444921 0.008583691 0.007806081 -0.012091898 0.013911182 0.005094951 -0.026415094 -0.012301013 -0.011914894 -0.007745618 -0.004895961 0.006860158 -0.007834101 -0.03875969 0 0.008613264 0.000821693 0.001230012 0.002620545 0.020901068 0.060483871 0.002930403 -0.00853971 0 0.002457002 -0.035023523 -0.030482257 0.041825095 -0.051862673 0.020671835 0.002463054 0.009803922 0 -0.015954951 -0.025547445 -0.006163328 0.051476793 -0.01023751 0.001213592 0.002708559 0.021459227 -0.059925094 -0.051162791

-0.015248796 -0.010757137 -0.013333333 0 -0.007936508 0.007968127 0.067810458 0 0.013383522 0.013513514 0.023770935 0.013176471 -0.003952569 -0.03213466

-0.005704971 0.010730499 -0.002424242 -0.011081794 -0.015791918 0.015873016 0.006324111 0.019672131 -0.005716619 0.007290401 0.008537887 0.005663049 0.015625 0.046347211 0.00562701 0.011498973 0.038600724 0.010582011 0.007977475 -0.030769231 0.012012012

-0.007993605 0.009338205 0.015098722 -0.00104712 0 -0.023809524 -0.005934718

RoR Summary & Covariance Matrix Summary

A B C D E F G Average Daily Return 0.000748 0.001785 0.002334 0.001172 0.000542 0.004302 0.001462 Std. Dev. 0.016084 0.020788 0.023699 0.01594 0.014091 0.047676 0.020936 Variance 0.000259 0.000432 0.000562 0.000254 0.000199 0.002273 0.000438 Semiannual Return 0.097203 0.232038 0.303362 0.15239 0.070491 0.559295 0.190059 Semiannual Std Dev 0.183391 0.237014 0.270213 0.181739 0.160664 0.543586 0.238712

Covariance Matrix A B C D E F G

A 0.000258 9.54E-05 6.56E-05 6.49E-05 5.04E-05 5.75E-05 8.37E-05 B 9.54E-05 0.000431 0.000221 0.000104 8.91E-05 0.000177 9.04E-05 C 6.56E-05 0.000221 0.000561 0.000126 8.78E-05 0.000185 0.000103 D 6.49E-05 0.000104 0.000126 0.000254 7.8E-05 0.000112 9.7E-05 E 5.04E-05 8.91E-05 8.78E-05 7.8E-05 0.000198 0.00015 7.27E-05 F 5.75E-05 0.000177 0.000185 0.000112 0.00015 0.002269 0.000137 G 8.37E-05 9.04E-05 0.000103 9.7E-05 7.27E-05 0.000137 0.000437

• Must load Analysis Tool Pack to create Covariance Matrix

Matrix of Equations and Inverse Markowitz' Equations

Average Semi-annual Return 0.13

1 2 3 4 5 6 7 8 9 1 0.000258 9.54E-05 6.56E-05 6.49E-05 5.04E-05 5.75E-05 8.37E-05 -0.00075 -1 2 9.54E-05 0.000431 0.000221 0.000104 8.91E-05 0.000177 9.04E-05 -0.00178 -1 3 6.56E-05 0.000221 0.000561 0.000126 8.78E-05 0.000185 0.000103 -0.00233 -1 4 6.49E-05 0.000104 0.000126 0.000254 7.8E-05 0.000112 9.7E-05 -0.00117 -1 5 5.04E-05 8.91E-05 8.78E-05 7.8E-05 0.000198 0.00015 7.27E-05 -0.00054 -1 6 5.75E-05 0.000177 0.000185 0.000112 0.00015 0.002269 0.000137 -0.0043 -1 7 8.37E-05 9.04E-05 0.000103 9.7E-05 7.27E-05 0.000137 0.000437 -0.00146 -1 8 0.000748 0.001785 0.002334 0.001172 0.000542 0.004302 0.001462 0 0 9 1 1 1 1 1 1 1 0 0

Inverse 3702.689 -625.375 219.2267 -1008.98 -1890.64 219.8145 -616.737 -126.23 0.383397 -625.375 3079.167 -1225.93 -509.056 -222.016 -254.986 -241.803 110.855 -0.05554 219.2267 -1225.93 1979.249 -717.47 462.2056 -336.36 -380.92 185.3308 -0.13359 -1008.98 -509.056 -717.47 4824.76 -1768.8 -56.272 -764.184 10.43987 0.191698 -1890.64 -222.016 462.2056 -1768.8 3410.942 332.0478 -323.736 -391.023 0.722213 219.8145 -254.986 -336.36 -56.272 332.0478 289.2685 -193.512 125.3025 -0.11758 -616.737 -241.803 -380.92 -764.184 -323.736 -193.512 2520.893 85.32522 0.009408 126.2302 -110.855 -185.331 -10.4399 391.0231 -125.303 -85.3252 84.28721 -0.07757

-0.3834 0.055541 0.133591 -0.1917 -0.72221 0.117583 -0.00941 -0.07757 0.000186

First Allocation Results w1 0.257167 w2 0.055314 w3 0.05174 w4 0.202138 w5 0.33119 w6 0.007719 w7 0.094733 λ1 0.006715 λ2 0.000108

Second Allocation Results

w1 0.203083 w2 0.190351 w3 0.071021 w4 0.311239 w5 0.148525 w6 -0.00506 w7 0.080838 λ1 0.011874 λ2 0.000179

• This Negative value corresponds to “selling short a stock”.

• If we do not want to get negative values we need to use a technique called “Quadratic Programming”.

• Or, for this case, we could set this weight to zero and rebalance the remaining weights.

Second Example with Recent Data • See Excel file “Lecture 09 More Examples” file on the

Blackboard.

• As with the prior example, a 13% semiannual expected return was used.

• I selected the last three years of the data and grouped it into thee one year allocations.

• I have solved the first two years, and suggest you create your own spreadsheets to verify my results, and then calculate the third year allocation.

AAPL Daily Adjusted Closing Price

0

20

40

60

80

100

120

140

AAPL Daily Adjusted Closing Price

0

1000

2000

3000

4000

5000

6000

7000

0 10 20 30 40 50 60 70 80 90 100 110 120 130 140 150 160

Returns • It is more common to review a stock’s return rather than the

stock price. • As we discussed in an earlier lecture, there are two ways to

compute the return:

( )closing price at day - closing price at day Rate of Return

closing price at day Return 1

t n t t

+ =

= −

( )closing price at day Return

closing price at day t n

t +

=

APPL Daily Rate of Return %

-0.6000

-0.4000

-0.2000

0.0000

0.2000

0.4000

0.6000

0.8000

1.0000

1.2000

APPL Rate of Return Histogram

0

500

1000

1500

2000

2500

3000

3500 -0

.2

-0 .1

8

-0 .1

6

-0 .1

4

-0 .1

2

-0 .1

-0 .0

8

-0 .0

6

-0 .0

4

-0 .0

2 0

0. 02

0. 04

0. 06

0. 08 0.

1

0. 12

0. 14

0. 16

0. 18 0.

2

APPL Daily Total Return

0.6000

0.8000

1.0000

1.2000

1.4000

1.6000

1.8000

2.0000

2.2000 4/

12 /1

99 6

4/ 12

/1 99

7

4/ 12

/1 99

8

4/ 12

/1 99

9

4/ 12

/2 00

0

4/ 12

/2 00

1

4/ 12

/2 00

2

4/ 12

/2 00

3

4/ 12

/2 00

4

4/ 12

/2 00

5

4/ 12

/2 00

6

4/ 12

/2 00

7

4/ 12

/2 00

8

4/ 12

/2 00

9

4/ 12

/2 01

0

4/ 12

/2 01

1

4/ 12

/2 01

2

4/ 12

/2 01

3

4/ 12

/2 01

4

4/ 12

/2 01

5

4/ 12

/2 01

6

AAPL Daily Total Return

Average = 0.99984 StDev = 0.03126

0 500

1000 1500 2000 2500 3000 3500 4000 4500 5000

0.75 0.80 0.85 0.90 0.95 1.00 1.05 1.10 1.15 1.20 1.25 1.30

Observations • The mean of the total returns is approximately one and that of the rate of

return is approximately zero. • The smallest theoretical total return is zero and the largest is unbounded. • Using the average daily rate of return to calculate a final stock price might

not be correct! • For example, if the initial stock price is $100 and we observe an increase of

10% followed by a decrease of 10% the average daily rate of return. – We might conclude that the stock price is the same as the original

price. – However, it is not! The actual final price would be $99 (do the math).

• This problem is corrected if we use the product of the Total Return to get the final price.

– Note that we would not use the sums of the Total Returns. – In this case we would have (1.1)(0.9) = 0.99 when multiplied for the

original stock price we would get the correct final price. – Thus, to get the average change in stock price we need to use the

geometric mean

Observations • In general we will use the Total Return to analyze the performance of

stock prices. • In the previous example suppose that we have 5 consecutive increases of

10% of the stock price. – The final price of the stock would be 100(1.1)5 = 161.05; a change of

$61.05. • Now assume that the original price suffers 5 consecutive decreases of

10% of the stock price. – The final price of the stock would be 100(0.9) 5 = $59.04; a change of

$40.95. • This supports the idea that even if we observe the same number of

increases and decreases in a symmetric return, as suggested by the normal distribution, the distribution of the total return will be asymmetric; biased towards the positive side.

• This suggests that the distribution of the Total Returns is asymmetric, biased towards the right and close to the normal distribution.

– Hypothesis: The returns follow the lognormal distribution.

Lognormal Distribution • In probability and statistics, the log-normal distribution is the

probability distribution of any random variable whose logarithm is normally distributed.

– If X is a random variable with a normal distribution, then exp(X) has a log-normal distribution.

– Likewise, if Y is log-normally distributed, then ln(Y) is normally distributed.

• A variable might be modeled as log-normal if it can be thought of as the multiplicative product of many small independent factors.

• A typical example is the long-term return rate on a stock investment.

– It can be considered as the product of the daily return rates.

Lognormal Distribution • The log-normal distribution has

the probability density function (pdf) for x > 0, where μ and σ are the mean and standard deviation of the variable's logarithm.

• The expected value is:

• and the variance is:

• Equivalent relationships may be written to obtain μ and σ given the expected value and standard deviation: ( )2 2 ln E Xσ µ= −  

( ) ( ) ( )2 2ln 2

; , 2

x e

f x x

µ σ

µ σ σ π

− −

=

( ) 2 2E X eµ σ+=

( ) ( )2 22var 1X e eσ µ σ+= −

( )( ) ( ) ( )2

var1 ln E ln 1

2 E X

X X

µ  

= − +     

Lognormal Distribution

• We are trying to show that the ln of the Total Returns (st+n / st) is distributed as a normal distribution.

• Try using a goodness of fit technique to test the hypothesis that the APPL Total Return data follows a lognormal distribution and that ln(st+n / st) follows a normal distribution.

Other Observations • If ( ) ( )1 0ln Normal ,s s µ σ⇒

( ) ( ) ( )1 0ln ln Normal ,s s µ σ⇒ + Constant

( ) ( ) ( )( )20ln ln Normal ,ns s f n oµ σ⇒ + −

• Then

• Later we will show that:

Useful Statistical Results

• If the xi are independent we have:

( )xnx n

i i VarVar

1 =



  

 ∑ =

( )2 1 1

Var Var n n

i i i i i

a x a x = =

  = 

  ∑ ∑

( )xnx n

i i Stdev Stdev

1 =



  

 ∑ =

( ) ( )2 1 1 , :

Var Var 2 Cov , n n

i i i i i j i j i i i j i j

a X a X a a X X = = <

  = + 

  ∑ ∑ ∑

• If they are identical we have:

Assignments • Create your own Excel spreadsheets for the Markowitz model.