Construct a portfolio and analyze it's Risk & Return in a Nutshell
Hello All!
I have a
new method to construct a portfolio for risky assets. It has five assets and I’m
going to concise my lecture as you can see from the videos that it is in fact
easy to construct it by yourself.
Few points
to remember:
1. Construct the model in the same
cells i.e., Headings, Stocks, Weights, Risk & Return Etc. which are there
in the excel sheet and only then the VBA codes will work.
2. I left an excel spreadsheet for your
practice and I took some time to create VBA codes to auto construct this portfolio.
Let’s
Understand what’s in it:
A2:E127- Values
consists of daily returns data for 5 stocks. I picked these stocks out of other
blogs and they belong to famous companies like Google, twitter, Microsoft
etc.
G2:G6- Values
relate to equal Weights. This means I started with an equal weighted portfolio.
H2:H6- Values
are Weight squares which will be later used in Portfolio Variance formula.
I2:I6- Values pertinent to Mean of individual
columns A, B, C, D & E.
J2:J6- Values derive Excess returns and expressed
as Rt-R.mean.
K2:K6- Values form the numerator of variance formula
and expressed as (Rt-R.mean)^2 .
L2:L6- Values of daily Variance and defined as K2/127
and basically the denominator part.
M2:M6-
Values of Covariance results.
N2:N6-
Values of Correlation results.
O2:O6- Values
of Expected Returns which is simply Mean x 252 (Annual)
R2- Value is daily R2 and cleverly
choreographed/modelled into R2 cell.
R2- Value is daily R2 x 127 days which is know as
semi annualized variance.
R3- Value is daily R2 x 252 days which is renown as
Annualized variance.
S2:S4- Values belong to Standard Deviation (SD) which
we all know it as Square-roots of Variances.
T4- Value defines Portfolio Expected Returns
on the whole.
U4- Value gives out the Sharpe ratio which is
nothing but Exp. Returns / SD.
In the
Risk-Return tradeoff, you will observe the 1st row and 3rd
row highlighted in Red because it gives us a picture that, Returns on normal Variance
(Risk) is lower than Returns based on optimized weights and for same level/amounts of Risk-Variance.
Important:
First, Minimize
Variance (Daily, Semi or Annual).
Second, Maximize
Expected Portfolio Returns and choose Variance as a constraint (Input your
desired levels of Risk). Variance=Risk!
Finally, when
your done with optimizing for desired levels of risk, set 100% as Variance in
the constraint value and it gives you the Maximum risk and Return (My last row highlighted
in yellow returned highest as 0.57% risk or so)
Note: Solver works on VBA coded results. I have updated codes for Dynamic update after I made the videos. I didn't do additional statistics to reduce any variance if any such thing exists. I have done my math on daily returns only. If you do data analysis using built-in tools like Covariance or Correl, I observed, it's done on raw data i.e., the closing prices directly. Then, I took averages for the whole column and not the latest ones.
One more thing I'd like to point out is, .... Wait a minute.... Ooops! I forgot. Yes I remember it now just at the nick of the moment..... Ooops I forgot it again.... OK! now I remember after the third time, the second video suggests that all VBA codes yield results equal to manual calculation results in Excel. I didn't panic if they show 0.002 - 0.005% difference in results. So, you don't have to panic as well.
All the
best!
Sub CalculateSquares()
Dim i As Integer
For i = 2 To 7
Cells(i, 8).Formula = "=G" & i & "^2"
Next i
End Sub
Sub CalculateAverages()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1") ' Change the sheet name as needed
' Calculate averages for each column
ws.Range("I2").Formula = "=AVERAGE(A2:A127)"
ws.Range("I3").Formula = "=AVERAGE(B2:B127)"
ws.Range("I4").Formula = "=AVERAGE(C2:C127)"
ws.Range("I5").Formula = "=AVERAGE(D2:D127)"
ws.Range("I6").Formula = "=AVERAGE(E2:E127)"
' Display formulas in cells
ws.Range("I2:I6").FormulaHidden = False
End Sub
Sub CalculateExcessReturns()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1") ' Replace "Sheet1" with your actual sheet name
' Calculate the results and output them in cells J2:J6
ws.Range("J2").Formula = "=SUMPRODUCT($A$2:$A$127-$I$2)"
ws.Range("J3").Formula = "=SUMPRODUCT($B$2:$B$127-$I$3)"
ws.Range("J4").Formula = "=SUMPRODUCT($C$2:$C$127-$I$4)"
ws.Range("J5").Formula = "=SUMPRODUCT($D$2:$D$127-$I$5)"
ws.Range("J6").Formula = "=SUMPRODUCT($E$2:$E$127-$I$6)"
End Sub
Sub DisplaySumSQ()
Dim i As Long
Dim lastRow As Long
Dim ws As Worksheet
Dim diffRange As Range
Dim referenceValue As Double
Dim formulaString As String
Dim cell As Range
' Set the worksheet where your data is located
Set ws = ThisWorkbook.Sheets("Sheet1") ' Change "Sheet1" to your actual sheet name
' Finds the last row with data in column A (assuming all columns have the same number of rows)
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
' Loop through each column (A to E)
For i = 1 To 5
' Calculate the squares of difference for the current column
Set diffRange = ws.Range(ws.Cells(2, i), ws.Cells(lastRow, i))
referenceValue = ws.Cells(2, 9).Value ' Corrected row index to 2
formulaString = "=SUMSQ(" & diffRange.Address & "-" & ws.Cells(2, 9).Address & ")"
' Store the formula in the corresponding cell (K2:K6)
ws.Cells(1 + i, 11).Formula2 = formulaString ' Column 11 corresponds to K
Next i
End Sub
Sub CalculateDailyVariance()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1") ' Change the sheet name as needed
' Calculate results
ws.Range("L2").Formula = "=K2 / 127"
ws.Range("L3").Formula = "=K3 / 127"
ws.Range("L4").Formula = "=K4 / 127"
ws.Range("L5").Formula = "=K5 / 127"
ws.Range("L6").Formula = "=K6 / 127"
' Display formulas in cells
ws.Range("L2:L6").FormulaHidden = False
End Sub
Sub CalculateDailySD()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1") ' Change the sheet name as needed
' Calculate square roots and display formulas
ws.Range("M2").Formula = "=SQRT(L2)"
ws.Range("M3").Formula = "=SQRT(L3)"
ws.Range("M4").Formula = "=SQRT(L4)"
ws.Range("M5").Formula = "=SQRT(L5)"
ws.Range("M6").Formula = "=SQRT(L6)"
' Display formulas in cells
ws.Range("M2:M6").FormulaHidden = False
End Sub
Sub CalculateCovariance()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1") ' Change the sheet name as needed
' Calculate products and display formulas
ws.Range("N2").Formula = "=J2*J3"
ws.Range("N3").Formula = "=J2*J4"
ws.Range("N4").Formula = "=J2*J5"
ws.Range("N5").Formula = "=J2*J6"
ws.Range("N6").Formula = "=J3*J4"
ws.Range("N7").Formula = "=J3*J5"
ws.Range("N8").Formula = "=J3*J6"
ws.Range("N9").Formula = "=J4*J5"
ws.Range("N10").Formula = "=J4*J6"
ws.Range("N11").Formula = "=J5*J6"
' Display formulas in cells
ws.Range("N2:N11").FormulaHidden = False
End Sub
Sub CalculateCorrel()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1") ' Change the sheet name as needed
' Calculate products and display formulas
ws.Range("O2").Formula = "=N2 / (M2 * M3)"
ws.Range("O3").Formula = "=N3 / (M2 * M4)"
ws.Range("O4").Formula = "=N4 / (M2 * M5)"
ws.Range("O5").Formula = "=N5 / (M2 * M6)"
ws.Range("O6").Formula = "=N6 / (M3 * M4)"
ws.Range("O7").Formula = "=N7 / (M3 * M5)"
ws.Range("O8").Formula = "=N8 / (M3 * M6)"
ws.Range("O9").Formula = "=N9 / (M4 * M5)"
ws.Range("O10").Formula = "=N10 / (M4 * M6)"
ws.Range("O11").Formula = "=N11 / (M5 * M6)"
' Display formulas in cells
ws.Range("O2:O11").FormulaHidden = False
End Sub
Sub CalculateDailyExpReturns()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1") ' Change the sheet name as needed
' Calculate square roots and display formulas
ws.Range("P2").Formula = "=(I2*252)"
ws.Range("P3").Formula = "=(I3*252)"
ws.Range("P4").Formula = "=(I4*252)"
ws.Range("P5").Formula = "=(I5*252)"
ws.Range("P6").Formula = "=(I6*252)"
' Display formulas in cells
ws.Range("P2:P6").FormulaHidden = False
End Sub
Sub CalculatePortfolioVariance()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1") ' Replace "Sheet1" with your actual sheet name
' Calculate the formula
ws.Range("R2").Formula = "=SUMPRODUCT(H2:H6, L2:L6) + (2 * G2 * G3 * M2 * M3 * N2) + (2 * G2 * G4 * M2 * M4 * N3) + (2 * G2 * G5 * M2 * M5 * N4) + (2 * G2 * G6 * M2 * M6 * N5) + (2 * G3 * G4 * M3 * M4 * N6) + (2 * G3 * G5 * M3 * M5 * N7) + (2 * G3 * G6 * M3 * M6 * N8) + (2 * G4 * G5 * M4 * M5 * N9) + (2 * G4 * G6 * M4 * M6 * N10) + (2 * G5 * G6 * M5 * M6 * N11)"
' Display the formula in cell R2
ws.Range("R2").FormulaHidden = False
End Sub
Sub CalculateAndDisplayR3()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1") ' Replace "Sheet1" with your actual sheet name
' Calculate the formula
ws.Range("R3").Formula = "=R2*SQRT(127)"
' Display the formula in cell R3
ws.Range("R3").FormulaHidden = False
End Sub
Sub CalculateAndDisplayR4()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1") ' Replace "Sheet1" with your actual sheet name
' Calculate the formula
ws.Range("R4").Formula = "=R2*SQRT(252)"
' Display the formula in cell R4
ws.Range("R4").FormulaHidden = False
End Sub
Sub CalculatePortfolioSD()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1") ' Replace "Sheet1" with your actual sheet name
' Calculate square roots and display formulas
ws.Range("S2").Formula = "=SQRT(R2)"
ws.Range("S3").Formula = "=SQRT(R3)"
ws.Range("S4").Formula = "=SQRT(R4)"
' Display formulas in cells S2:S4
ws.Range("S2:S4").FormulaHidden = False
End Sub
Sub CalculatePortfolioReturn()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1") ' Replace "Sheet1" with your actual sheet name
' Calculate the formula
ws.Range("T4").Formula = "=SUMPRODUCT(G2:G6, P2:P6)"
' Display the formula in cell T4
ws.Range("T4").FormulaHidden = False
End Sub
Sub CalculateSharpe()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1") ' Replace "Sheet1" with your actual sheet name
' Calculate the formula
ws.Range("U4").Formula = "=T4/S4"
' Display the formula in cell U4
ws.Range("U4").FormulaHidden = False
End Sub
Sub OutputAllMacros()
Call CalculateSquares
Call CalculateAverages
Call CalculateExcessReturns
Call DisplaySumSQ
Call CalculateDailyVariance
Call CalculateDailySD
Call CalculateCovariance
Call CalculateCorrel
Call CalculateDailyExpReturns
Call CalculatePortfolioVariance
Call CalculateAndDisplayR3
Call CalculateAndDisplayR4
Call CalculatePortfolioSD
Call CalculatePortfolioReturn
Call CalculateSharpe
End Sub
Comments
Post a Comment