Portfolio Optimization Models
1
Basics
• Are we clear about risk return trade offs?
• How do you compute individual
stock/asset returns and risk?
• How will you compute portfolio returns?
• How will you compute portfolio risk?
2
Portfolio theory
• Assuming that share returns are normally distributed, we
can say that the return and risk of a combination P of two
shares A and B in proportions WA and WB are:
E(RP) = WAE(RA) + WBE(RB)
2 2 2 2
SP W S W S 2 WA WBSASBCorrAB
A A B B
3
Portfolio Optimization
• How do we find the portfolio that has the
lowest risk and yields an acceptable
expected return?
• Asset allocation models are used to
determine the percentage of assets to
invest in stocks, gold and treasury bills
4
Matrix operations in Excel
A B C D E F G
1 Matrix multiplication in Excel
2
3 Typical multiplication of two matrices
4 Matrix 1 1 2 3
5 2 4 5
6
7 Matrix 2 1 2
8 3 4
9 5 6
10
11 Matrix 1 times matrix 2, with formula =MMULT(Mat1,Mat2)
12 Select range with 2 rows, 2 columns, enter formula, press Ctrl-Shift-Enter.
13 22 28
14 39 50
15
5
Portfolio optimization
• Perlman & Brothers, an investment
company, can invest in three stocks. From
past data, the means and standard
deviations of annual returns have been
estimated and also the correlation matrix.
The company wants to find a minimum-
variance portfolio that yields an expected
annual return of 12 percent.
6
Returns and Risk
A B C D
3 Stock input data
4 Stock 1 Stock 2 Stock 3
5 Mean return 0.14 0.11 0.1
6 StDev of return 0.2 0.15 0.08
7
Correlation matrix
A B C D
8 Correlations Stock 1 Stock 2 Stock 3
9 Stock 1 1 0.6 0.4
10 Stock 2 0.6 1 0.7
11 Stock 3 0.4 0.7 1
8
Full data set
A B C D
1 Portfolio selection model
2
3 Stock input data
4 Stock 1 Stock 2 Stock 3
5 Mean return 0.14 0.11 0.1
6 StDev of return 0.2 0.15 0.08
7
8 Correlations Stock 1 Stock 2 Stock 3
9 Stock 1 1 0.6 0.4
10 Stock 2 0.6 1 0.7
11 Stock 3 0.4 0.7 1
9
Co-variances
G H
7
8 Covariances Stock 1
9 Stock 1 =HLOOKUP($G9,LTable,3)*B9*HLOOKUP(H$8,LTable,3)
10 Stock 2 =HLOOKUP($G10,LTable,3)*B10*HLOOKUP(H$8,LTable,3)
11 Stock 3 =HLOOKUP($G11,LTable,3)*B11*HLOOKUP(H$8,LTable,3)
10
Portfolio returns
A B C D E F G
13 Investment decisions
14 Stock 1 Stock 2 Stock 3 Total Required
15 Fractions to invest 0.5 0 0 1 = 1
16
17 Constraint on expected portfolio return
18 Actual Required
19 0.12 >= 0
11
Portfolio variance
A B
21 Portfolio variance =MMULT(Invested,MMULT(CovarMat,TRANSPOSE(Invested)))
22 Portfolio stdev =SQRT(PortVar)
12
Solver macro
13
' Calling Simple Solver
Sub CallSolverBasic()
SolverReset
SolverOk SetCell:="$B$21", MaxMinVal:=2, ByChange:="$B$15:$D$15"
SolverAdd CellRef:="$B$19", Relation:=3, FormulaText:="0.12"
SolverAdd CellRef:="$E$15", Relation:=2, FormulaText:="1"
SolverOptions AssumeNonNeg:=True
SolverSolve True
End Sub
14
' Solver Table
Sub CallSolver()
Set rng = Range("A27:A31") ' Range of expected returns
Range("B27").Select ' Select the starting cell at which results have to be
set
For Each cell In rng
SolverReset
SolverOk SetCell:="$B$21", MaxMinVal:=2,
ByChange:="$B$15:$D$15"
SolverAdd CellRef:="$B$19", Relation:=3, FormulaText:=[Link]
SolverAdd CellRef:="$E$15", Relation:=2, FormulaText:="1"
SolverOptions AssumeNonNeg:=True
SolverSolve True
[Link] = Range("$B$22").Value ' Set the active cell to the
value returned by solver
[Link](1, 0).Select ' Move the active cell by one row
Next
End Sub
15
