MARKOWITS EFFICIENT PORTFOLIO              
 
   
(HUANG LITZENBERGER APPROACH)          
             
             
Huang-Litzenberger approach allows us to find mathematically efficient set of portfolios
Assumptions
There are no limitations on the positions' volumes
You can have both long (buy stocks) and short (sell stocks) positions in your portfolio
We use weekly data, so the expected return is a weekly return and risk is a weekly risk
Step 1. Download stocks' pricing data   STEP 1  
First, we download pricing data for the stocks
For my model I've chosen four stocks from different sectors (to create a diversified portfolio)
We will construct an effective portfolio on a weekly basis. That means that we will try to find the portfolio with required weekly return and minimal weekly risks (minimal standard deviation)
APPLE CITI GROUP GENERAL ELECTRIC EXXON MOBIL
26.03.2010 230,9 26.03.2010 4,31 26.03.2010 18,34 26.03.2010 66,54
02.04.2010 235,97 2,2% 02.04.2010 4,18 -3,0% 02.04.2010 18,33 -0,1% 02.04.2010 67,61 1,6%
09.04.2010 241,79 2,5% 09.04.2010 4,55 8,9% 09.04.2010 18,52 1,0% 09.04.2010 68,76 1,7%
16.04.2010 247,4 2,3% 16.04.2010 4,56 0,2% 16.04.2010 18,97 2,4% 16.04.2010 67,93 -1,2%
23.04.2010 270,83 9,5% 23.04.2010 4,86 6,6% 23.04.2010 19,07 0,5% 23.04.2010 69,24 1,9%
30.04.2010 261,09 -3,6% 30.04.2010 4,37 -10,1% 30.04.2010 18,86 -1,1% 30.04.2010 67,77 -2,1%
07.05.2010 235,86 -9,7% 07.05.2010 4 -8,5% 07.05.2010 16,88 -10,5% 07.05.2010 63,7 -6,0%
14.05.2010 253,82 7,6% 14.05.2010 3,98 -0,5% 14.05.2010 17,64 4,5% 14.05.2010 63,6 -0,2%
21.05.2010 242,32 -4,5% 21.05.2010 3,75 -5,8% 21.05.2010 16,42 -6,9% 21.05.2010 60,88 -4,3%
28.05.2010 256,88 6,0% 28.05.2010 3,96 5,6% 28.05.2010 16,35 -0,4% 28.05.2010 60,46 -0,7%
04.06.2010 255,965 -0,4% 04.06.2010 3,79 -4,3% 04.06.2010 15,71 -3,9% 04.06.2010 59,525 -1,5%
11.06.2010 253,51 -1,0% 11.06.2010 3,88 2,4% 11.06.2010 15,56 -1,0% 11.06.2010 61,86 3,9%
18.06.2010 274,074 8,1% 18.06.2010 4,01 3,4% 18.06.2010 15,95 2,5% 18.06.2010 63,1 2,0%
25.06.2010 266,7 -2,7% 25.06.2010 3,94 -1,7% 25.06.2010 14,91 -6,5% 25.06.2010 59,1 -6,3%
02.07.2010 246,94 -7,4% 02.07.2010 3,79 -3,8% 02.07.2010 13,88 -6,9% 02.07.2010 56,57 -4,3%
09.07.2010 259,62 5,1% 09.07.2010 4,04 6,6% 09.07.2010 14,95 7,7% 09.07.2010 58,78 3,9%
16.07.2010 249,9 -3,7% 16.07.2010 3,9 -3,5% 16.07.2010 14,55 -2,7% 16.07.2010 57,96 -1,4%
23.07.2010 259,94 4,0% 23.07.2010 4,02 3,1% 23.07.2010 15,71 8,0% 23.07.2010 59,72 3,0%
30.07.2010 257,25 -1,0% 30.07.2010 4,1 2,0% 30.07.2010 16,12 2,6% 30.07.2010 59,68 -0,1%
06.08.2010 260,091 1,1% 06.08.2010 4,06 -1,0% 06.08.2010 16,45 2,0% 06.08.2010 61,97 3,8%
13.08.2010 249,1 -4,2% 13.08.2010 3,88 -4,4% 13.08.2010 15,38 -6,5% 13.08.2010 59,91 -3,3%
20.08.2010 249,64 0,2% 20.08.2010 3,75 -3,4% 20.08.2010 15,03 -2,3% 20.08.2010 58,89 -1,7%
27.08.2010 241,62 -3,2% 27.08.2010 3,76 0,3% 27.08.2010 14,71 -2,1% 27.08.2010 59,8 1,5%
03.09.2010 258,77 7,1% 03.09.2010 3,91 4,0% 03.09.2010 15,3925 4,6% 03.09.2010 61,32 2,5%
10.09.2010 263,41 1,8% 10.09.2010 3,91 0,0% 10.09.2010 15,98 3,8% 10.09.2010 61,2 -0,2%
17.09.2010 275,37 4,5% 17.09.2010 3,95 1,0% 17.09.2010 16,29 1,9% 17.09.2010 60,78 -0,7%
24.09.2010 292,32 6,2% 24.09.2010 3,904 -1,2% 24.09.2010 16,66 2,3% 24.09.2010 61,75 1,6%
01.10.2010 282,52 -3,4% 01.10.2010 4,09 4,8% 01.10.2010 16,36 -1,8% 01.10.2010 62,54 1,3%
08.10.2010 294,07 4,1% 08.10.2010 4,19 2,4% 08.10.2010 17,12 4,6% 08.10.2010 64,38 2,9%
15.10.2010 314,74 7,0% 15.10.2010 3,95 -5,7% 15.10.2010 16,3 -4,8% 15.10.2010 65,19 1,3%
22.10.2010 307,47 -2,3% 22.10.2010 4,11 4,1% 22.10.2010 16,055 -1,5% 22.10.2010 66,34 1,8%
Step 2. Find expected return and standard deviation (risk) for each stock   STEP 2  
Below see the table with basic inputs for the model
Table 1. Basic inputs
  expected return standard deviation weights
  e d w
APPLE 1,1% 4,9% 114,34%
CITI GROUP -0,1% 4,6% -9,76%
GENERAL ELECTRIC -0,3% 4,5% -124,80%
EXXON MOBIL 0,0% 2,8% 120,21%
w-column should be empty at first; we will link it to values in Step 
e - expected return of the stock. Equal to an average weekly return for a chosen period
d - standard deviation of the stock, which is a measure of risk for the stock. For calculation I use STDEV function (the details of calculation you can find in Excel's help)
w - stock's weight (share) in the portfolio
When constructing the model for the first time, leave the w-column empty (later it will be linked to the formula)
Step 3. Draw two unit-vectors   STEP 3  
We need them for interim calculations
First: number of columns = 1; number of rows = 4 (same as the number of stocks)
Second: number of columns = 4; number of rows = 1
All the values in vectors equal to 1
Unity vector
u uT
1 1 1 1 1
1
1
1
Step 4. Draw two transposed matrices for expected returns and weights    STEP 4  
In table 1 (Step 2) with basic inputs you can see two columns: e (expected return) and w (weights)
Transpose simply means that you should turn columns into rows. 
Make links from this matrix to Table 1 values
We add "T" latter in the names of transposed matrices, thus we get wT and eT
Transposed Matrix
APPLE CITI GROUP GENERAL ELECTRIC EXXON MOBIL
wT 114,34% -9,76% -124,80% 120,21%
APPLE CITI GROUP GENERAL ELECTRIC EXXON MOBIL
eT 1,1% -0,1% -0,3% 0,0%
Step 5. Create covariation matrix   STEP 5  
Covariation defines the dependence of one stock from the other
In covariation matrix we calculate covariation between all stocks
We use COVAR excel function (details on that function and on covariation are available in Excel help)
We call this covariation matrix V
,
V Covariation matrix
APPLE CITI GROUP GENERAL ELECTRIC EXXON MOBIL
APPLE 0,00234 0,00119 0,00152 0,00084
CITI GROUP 0,00119 0,00206 0,00125 0,00081
GENERAL ELECTRIC 0,00152 0,00125 0,00195 0,00092
EXXON MOBIL 0,00084 0,00081 0,00092 0,00077
Step 6. Find the risk (standard deviation) for our portfolio   STEP 6  
Here is the formula of for portfolio dispersion (standard deviation squared): 
(taken from http://en.wikipedia.org/wiki/Modern_portfolio_theory) http://en.wikipedia.org/wiki/Modern_portfolio_theory
In matrix form this formula would look like  =wT x V x w
In excel you can write this formula as  =MMULT(MMULT( wT , V ), w )
Here is the calculation -> 0,00227547
Step 7. Calculate the inverse matrix   STEP 7  
Next we create inverse V matrix (or V (-1) matrix)
We use MINVERSE excel function for that
Highlight the field 4x4 (this is your future inverse matrix)
Start entering the formula (the cells remain highlighted): =MINVERSE( Covariation V-matrix 4x4)
Press Ctrl+Shift+Enter (this is important that you should press this combination of button and NOT simply Enter)
Here is what you get:
V(-1)
910,94 -112,04 -525,27 -241,54
-112,04 907,61 -230,82 -555,64
-525,27 -230,82 1579,25 -1071,87
-241,54 -555,64 -1071,87 3412,62
 
Step 8. Define 4 scalar values   STEP 8  
To define efficient portfolios Huang and Litzenberger determine 4 scalar values: A, B, C and D
 
Action 1. A calculation
A=uT x V(-1) x e
First, we multiply matrix uT (unity matrix) and V(-1) (inverse covariation matrix)
We need to highlight four cells and write the formula:   =MMULT( V(-1)-matrix, uT-matrix)
Then press Ctrl+Shift+Enter
You get this:
uT x V(-1)
32,09 9,11 -248,72 1543,57
Second, we multiply the result for e-vector (expected returns)
In a single cell insert the formula:  =MMULT(the result from previous calculation (uT x V(-1), e-vector(expected returns))
Then press Enter
Here it is:
A= 1,6461
Action 2. B calculation
B=eT x V(-1) x e
First, we multiply vector eT by matrix V(-1)
Highlight 4 cells and enter the formula:  =MMULT( V(-1) , eT ) and press Ctrl+Shift+Enter
Here is what you get:
eT x V(-1)
11,60 -1,07 -11,27 2,39
Second, we multiply the result (eT*V(-1) by e-vector (expected return)
Choose a single cell and enter the formula:  =MMULT(the result matrix eT x V(-1), e-vector (expected returns) ) and press Enter
Here it is:
B= 0,1649
Action 3. C calculation
C=uT x V(-1) x u
First, we multiply V(-1) matrix for uT vector (transposed unit vector)
Highlight 4 cells and enter the formula:  =MMULT( V(-1), uT) and press Ctrl+Shift+Enter
Here is the result:
uT x V(-1)
32,09 9,11 -248,72 1543,57
Second, multiply the result ( uT x V(-1) ) by u-vector (unit vector)
Choose a single cell and enter the formula:  =MMULT( the result (uT x V(-1)) , u-vector) and press Enter
Here is what you get:
C= 1336,0388
Action 4. D calculation
D=B x C-A x A
Choose a single cell and enter the formula with the final values of A, B, C:    =B x C-A x A
D 217,5371
Step 9. Calculation of interim coefficients m and l   STEP 9  
Action 1. m calculation m
m=V(-1) x u 32,09
9,11
We multiply V(-1) matrix for u-vector --------------> -248,72
Highlight 4 cells in a column and enter the formula:  =MMULT( V(-1), u ) and press Ctrl+Shift+Enter 1543,57
Action 2. l calculation l
l=V(-1) x e 11,599
-1,067
We multiply V(-1) matrix for e-vector --------------> -11,271
Highlight 4 cells in a column and enter the formula:  =MMULT( V(-1), e ) and press Ctrl+Shift+Enter 2,385
 
Step 10. Calculation of portfolio coordinates   STEP 10  
g and h are the two dots of the efficient frontier
g - is the portfolio with minimal expected return 
h - is the portfolio with max expected return
g= (B x m - A x l) / D 1 2 3 4
Calculate B x m Calculate A x l B x m - A x l g e g*e
Here is the calculation 5,289 19,093 -13,803 APPLE -6,35% 1,1% -0,07%
Step by step 1,501 -1,756 3,257 CITI GROUP 1,50% -0,1% 0,00%
-41,001 -18,553 -22,448 GENERAL ELECTRIC -10,32% -0,3% 0,04%
254,457 3,926 250,531 EXXON MOBIL 115,17% 0,0% 0,03%
Portfolio return= 0,00%
1 2 3 4
h = (C x l - A x m) / D C x l Am C x l-A x m h e h*e
15496,682 52,816 15443,866 APPLE 70,99 1,1% 0,764
Here is the calculation -1425,126 14,989 -1440,115 CITI GROUP -6,62 -0,1% 0,004
Step by step -15058,901 -409,409 -14649,492 GENERAL ELECTRIC -67,34 -0,3% 0,232
3186,568 2540,827 645,741 EXXON MOBIL 2,97 0,0% 0,001
Portfolio return= 100%
             
Step 11. Find the effective portfolio for a given return   STEP 11  
Enter an expected return for the portfolio
Advise: let this number be not really large, because otherwise you'll have to increase leverage significantly
Portfolio return= 1,7%
Here is the transposed w matrix. Just make column from the raw
Portfolio return g h h*T g + hT = w wT      
1,70% -6,3% 7099,4% 120,7% 114,3% 114,3% -9,8% -124,8% 120,2%
1,70% 1,5% -662,0% -11,3% -9,8%
1,70% -10,3% -6734,3% -114,5%
-124,8%
1,70% 115,2% 296,8% 5,0% 120,2%
      100,0%
Here are the weights of the stocks in the efficient portfolio VERY IMPORTANT! 
Make links from column g + h x T = w to the column w (weights) in table 1, Step 2
Portfolio risk calculation
to find portfolio risk we should multiply three matrices:  V, w and wT
Choose a cell and enter the formula:  =MMULT(MMULT(wT, V), w) and press enter
Portfolio risk= 0,23%
EFFICIENT PORTFOLIO  
APPLE   114,3%
CITI GROUP -9,8%
GENERAL ELECTRIC -124,8%
EXXON MOBIL 120,2%
Portfolio return= 1,70%
Portfolio risk= 0,23%
Step 12. Calculation of efficient portfolio structure with a given amount of money   STEP 12  
Enter the amount of money for your portfolio
Money= 150 000,00 $
Company Share in portfolio (%) Share in portfolio ($) Last price ($) Number of shares Position
APPLE 114,3% 171 517 307,47 558 long
CITI GROUP -9,8% -14 635 4,11 -3 561 short
GENERAL ELECTRIC -124,8% -187 202 16,055 -11 660 short
EXXON MOBIL 120,2% 180 320 66,34 2 718 long
Step 13. Drawing efficient frontier   STEP 13  
Drawing of efficient frontier using Huang Litzenberger approach in excel is easy. We just have to make several iterations to find dots on the line
To set several dots (coordinates) we take our given portfolio return, divide it by 10 and multiply by 1, 2, 3 etc
In fact you can take any value for portfolio return. We just apply this particular mechanics for automatization of this process
Dots   1 2 3 4 5 6 7 8 9 10
Expected portfolio return 0,2% 0,3% 0,5% 0,7% 0,9% 1,0% 1,2% 1,4% 1,5% 1,7%
Then we do calculations from the Step 11 for all the dots (all the portfolio returns)
Expected return Risk
T g h h*T g + hT wT
0,17% -6,3% 7099,4% 12,1% 5,7% 5,7% 0,4% -21,8% 115,7% 0,0750%
0,17% 1,5% -662,0% -1,1% 0,4%
0,17% -10,3% -6734,3% -11,4% -21,8%
0,17% 115,2% 296,8% 0,5% 115,7%
          100,0%        
g h h*T g + hT wT
0,34% -6,3% 7099,4% 24,1% 17,8% 17,8% -0,8% -33,2% 116,2% 0,0777%
0,34% 1,5% -662,0% -2,3% -0,8%
0,34% -10,3% -6734,3% -22,9% -33,2%
0,34% 115,2% 296,8% 1,0% 116,2%
      100,0%
g h h*T g + hT wT
0,51% -6,3% 7099,4% 36,2% 29,9% 29,9% -1,9% -44,7% 116,7% 0,0840%
0,51% 1,5% -662,0% -3,4% -1,9%
0,51% -10,3% -6734,3% -34,3% -44,7%
0,51% 115,2% 296,8% 1,5% 116,7%
      100,0%
g h h*T g + hT wT
0,68% -6,3% 7099,4% 48,3% 41,9% 41,9% -3,0% -56,1% 117,2% 0,0939%
0,68% 1,5% -662,0% -4,5% -3,0%
0,68% -10,3% -6734,3% -45,8% -56,1%
0,68% 115,2% 296,8% 2,0% 117,2%
      100,0%
g h h*T g + hT wT
0,85% -6,3% 7099,4% 60,3% 54,0% 54,0% -4,1% -67,6% 117,7% 0,1073%
0,85% 1,5% -662,0% -5,6% -4,1%
0,85% -10,3% -6734,3% -57,2% -67,6%
0,85% 115,2% 296,8% 2,5% 117,7%
      100,0%
g h h*T g + hT wT
1,020% -6,3% 7099,4% 72,4% 66,1% 66,1% -5,3% -79,0% 118,2% 0,1242%
1,020% 1,5% -662,0% -6,8% -5,3%
1,020% -10,3% -6734,3% -68,7% -79,0%
1,020% 115,2% 296,8% 3,0% 118,2%
      100,0%
g h h*T g + hT wT
1,19% -6,3% 7099,4% 84,5% 78,1% 78,1% -6,4% -90,5% 118,7% 0,1447%
1,19% 1,5% -662,0% -7,9% -6,4%
1,19% -10,3% -6734,3% -80,1% -90,5%
1,19% 115,2% 296,8% 3,5% 118,7%
      100,0%
g h h*T g + hT wT
1,36% -6,3% 7099,4% 96,6% 90,2% 90,2% -7,5% -101,9% 119,2% 0,1688%
1,36% 1,5% -662,0% -9,0% -7,5%
1,36% -10,3% -6734,3% -91,6% -101,9%
1,36% 115,2% 296,8% 4,0% 119,2%
      100,0%
g h h*T g + hT wT
1,53% -6,3% 7099,4% 108,6% 102,3% 102,3% -8,6% -113,4% 119,7% 0,1964%
1,53% 1,5% -662,0% -10,1% -8,6%
1,53% -10,3% -6734,3% -103,0% -113,4%
1,53% 115,2% 296,8% 4,5% 119,7%
      100,0%
g h h*T g + hT wT
1,70% -6,3% 7099,4% 120,7% 114,3% 114,3% -9,8% -124,8% 120,2% 0,2275%
1,70% 1,5% -662,0% -11,3% -9,8%
1,70% -10,3% -6734,3% -114,5% -124,8%
1,70% 115,2% 296,8% 5,0% 120,2%
      100,0%
Here is our efficient frontier
x-axis y-axis
Return Risk
1 0,17% 0,0750%
2 0,34% 0,0777%
3 0,51% 0,0840%
4 0,68% 0,0939%
5 0,85% 0,1073%
6 1,02% 0,1242%
7 1,19% 0,1447%
8 1,36% 0,1688%
9 1,53% 0,1964%
10 1,70% 0,2275%
And here are all the results of the model
Portfolio return= 3,00%
Portfolio risk= 0,23%
Amount of money= 150 000
Efficient portfolio
Company Share (%) Share ($) Number of shares Position
APPLE 114,3% 171 517 558 long
CITI GROUP -9,8% -14 635 -3 561 short
GENERAL ELECTRIC -124,8% -187 202 -11 660 short
EXXON MOBIL 120,2% 180 320 2 718 long