The objective of this project is to learn how the
Nobel Prize winning, Optimal Portfolio Theory
(by Harry Markowitz), works in practice. Three stocks are used for this
project. The efficient frontier for the
three-stock portfolio is plotted on Figure
1.
The three-stock portfolio possibilities space is derived by
assigning different weights for each stock using a random number
generator. The random number generator generated random numbers from 0
to 1. To ensure the sum of the three weights equal 1 and all three
weights are positive numbers between 0 to 1, the following procedure is
followed:
1. Generate 3 random numbers.
2. Assign the ith random number divided by the sum of the three random
numbers as the weight for stock i.
The procedure above ensure each weight will be fairly distributed.
Once the portfolio possibilities space is plotted (in purple) , the
optimal portfolio could be found by graphically determining the
tangency portfolio consistent with the riskless interest rate. The
riskless rate (the U.S. T-Bill rate can be used as a proxy) in this
case was assumed to be 4%. The expected return and the standard
deviation corresponding to the tangency were 12.5% and 20%,
respectively.
To let the computer select the optimal portfolio, the Sharpe Ratio is used. In this case, the
portfolio corresponding to the largest Sharpe Ratio is the optimal
portfolio. Four thousands (4,000) combinations are generated. The
largest Sharpe Ratio is found to be 41.315%. The weights corresponding
to this ratio are 17.30%, 42.81% and 39.88% for stocks 1, 2 and 3,
respectively. The portfolio's expected return and standard deviation
were 12.27% and 20.017%, respectively. Note that, for a 3 stock
portfolio, 500 combinations would be enough to provide a very good
estimate. To set the number of combinations, place the number on cells
"E4" of the sheet "Input Sheet". To use this program, the user needs to
create the following sheets: "Input Sheet" & "Output Sheet". The
user may not need to set up the format as shown in Figures 2 and 3. For
those sheets, however, it is very important that the inputs, stock
variances, covariances, expected returns, risk free rate, and the
number of iterations, be place in the same cell references as in Figure
2.
Option Explicit
'***********************************************************************************
'*
Compute the
portfolio standard
deviation
*
'***********************************************************************************
Sub PortfSD()
Dim riskFreeRate As Single
riskFreeRate = Sheets("Input
Sheet").Range("E3").Value
Dim i As Integer, j As Integer, k As Integer, nLoop
As Long
Dim n As Variant
Cells(5, 10) = "=count(b7:b16)"
nLoop = Sheets("Input Sheet").Range("E4").Value
n = Sheets("Input Sheet").Range("J5").Value
ReDim Matrix(n, n)
ReDim rand(n) As Single
Dim randSum As Single, varSum As Single, meanSum As
Single, wSum As Single
ReDim stkmean(nLoop) As Single, portfMean(nLoop) As
Single
ReDim W(nLoop, n) As Single, sharpeRatio(nLoop) As
Single
ReDim SD(nLoop) As Single, Hold(nLoop) As Single
Call startSetup
For k = 1 To
nLoop
'start iteration
varSum = 0
randSum = 0
meanSum = 0
wSum = 0
For i = 1 To
n
'generate random weight
rand(i) = Rnd
randSum = randSum + rand(i)
Next i
For i = 1 To n
W(k,
i) = rand(i) / randSum
stkmean(i) = Sheets("Input Sheet").Cells(i + 6, 2).Value
meanSum = meanSum + W(k, i) * stkmean(i)
Next i
portfMean(k) = meanSum
For i = 1 To
n
'read in covariance matrix(row,column)
For
j = i To n
Matrix(i, j) = Sheets("Input Sheet").Cells(i + 6, j + 2)
Next
j
Next i
For i = 1 To
n
'compute the diagonal sum = Wi x
Wi x Vari
varSum = varSum + W(k, i) ^ 2 * Matrix(i, i)
Next i
For i = 1 To
n
'compute the other sum = 2 x Wi x
Wj x Varij
For
j = i + 1 To n
varSum = varSum + 2 * W(k, i) * W(k, j) * Matrix(i, j)
Next
j
Next i
SD(k) = Sqr(varSum)
sharpeRatio(k) =
(portfMean(k) - riskFreeRate) / SD(k)
Hold(k) = k
Sheets("Output
Sheet").Cells(k + 4, 10) = SD(k)
Sheets("Output
Sheet").Cells(k + 4, 11) = portfMean(k)
Sheets("Output
Sheet").Cells(k + 4, 12) = sharpeRatio(k)
Next k
Call DoubleSort(nLoop,
sharpeRatio, Hold)
Sheets("Output
Sheet").Cells(4, 3) = nLoop
Sheets("Output
Sheet").Cells(4, 4) = sharpeRatio(nLoop)
Sheets("Output
Sheet").Cells(4, 5) = portfMean(Hold(nLoop))
Sheets("Output
Sheet").Cells(4, 6) = SD(Hold(nLoop))
For i = 1 To n
wSum
= wSum + W(Hold(nLoop), i)
Sheets("Output Sheet").Cells(i + 4, 1) = i
Sheets("Output Sheet").Cells(n + 5, 1) = "Sum Check"
Sheets("Output Sheet").Cells(i + 4, 2) = W(Hold(nLoop), i)
Sheets("Output Sheet").Cells(n + 5, 2) = wSum
Next i
Call endSetup
End Sub
'***********************************************************************************
'*
Sort array y based on array
x
*
'***********************************************************************************
Sub DoubleSort(n As Long, x() As Single, y() As Single)
Dim xTemp As Double
Dim yTemp As Double
Dim i As Long
Dim j As Long
For j = 2 To n
xTemp = x(j)
yTemp = y(j)
For i = j - 1 To 1 Step -1
If
(x(i) <= xTemp) Then GoTo 10
x(i
+ 1) = x(i)
y(i
+ 1) = y(i)
Next i
i = 0
10 x(i + 1) = xTemp
y(i + 1) = yTemp
Next j
End Sub
'***********************************************************************************
'*
Clear the output
area
*
'***********************************************************************************
Sub startSetup()
Sheets("Output Sheet").Select
Range("C4:F4").Select
Selection.ClearContents
Range("A5:B100").Select
Selection.ClearContents
Range("J5:L60000").Select
Selection.ClearContents
Sheets("Input Sheet").Select
Range("A1").Select
End Sub
'***********************************************************************************
'*
Go to Output Page
*
'***********************************************************************************
Sub endSetup()
Sheets("Output Sheet").Select
Range("A1").Select
End Sub