Last active
August 27, 2019 19:47
-
-
Save Doggie52/b6f139579ccc886194f9bfac43d20eaa to your computer and use it in GitHub Desktop.
Microsoft Excel UDF macro to calculate Internal Rate of Return, Annualised Volatility, Annualised Downside Volatility and Maximum Drawdown of timeseries
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Function DIRR(first_price_cell As Range, first_date_cell As Range) As Double | |
' Gets various useful numbers | |
Dim first_row As Long, last_row As Long, price_column As Long, date_column As Long, sheet As Worksheet | |
first_row = first_price_cell.Row ' first row | |
price_column = first_price_cell.Column ' price column | |
date_column = first_date_cell.Column ' date column | |
Set sheet = first_price_cell.Worksheet ' sheet for price | |
last_row = sheet.Cells(sheet.Rows.Count, price_column).End(xlUp).Row ' get the last row | |
' Find last numeric value | |
Do Until IsNumeric(sheet.Cells(last_row, price_column).Value2) Or last_row = 1 | |
last_row = last_row - 1 | |
Loop | |
DIRR = (sheet.Cells(last_row, price_column).Value2 / sheet.Cells(first_row, price_column).Value2) ^ (1 / WorksheetFunction.YearFrac(sheet.Cells(first_row, date_column).Value, sheet.Cells(last_row, date_column).Value, 1)) - 1 | |
End Function | |
Function DVOL(first_price_cell As Range) As Double | |
' Gets various useful numbers | |
Dim first_row As Long, last_row As Long, price_column As Long, sheet As Worksheet | |
first_row = first_price_cell.Row ' first row | |
price_column = first_price_cell.Column ' price column | |
Set sheet = first_price_cell.Worksheet ' sheet for price | |
last_row = sheet.Cells(sheet.Rows.Count, price_column).End(xlUp).Row ' get the last row | |
' Find last numeric value | |
Do Until IsNumeric(sheet.Cells(last_row, price_column).Value2) Or last_row = 1 | |
last_row = last_row - 1 | |
Loop | |
' Setup variables | |
Dim returns() As Double | |
ReDim returns(1 To (last_row - first_row)) | |
Dim prices() As Variant | |
prices = sheet.Range(sheet.Cells(first_row, price_column), sheet.Cells(last_row, price_column)).Value2 ' copy prices to new array so we don't recalc | |
For i = 1 To last_row - first_row | |
' Calculate return and store in array | |
returns(i) = Log(prices(i + 1, 1) / prices(i, 1)) ^ 2 | |
Next i | |
DVOL = Sqr(252 * WorksheetFunction.Average(returns)) | |
End Function | |
Function DDVOL(first_price_cell As Range) As Double | |
' Gets various useful numbers | |
Dim first_row As Long, last_row As Long, price_column As Long, sheet As Worksheet | |
first_row = first_price_cell.Row ' first row | |
price_column = first_price_cell.Column ' price column | |
Set sheet = first_price_cell.Worksheet ' sheet for price | |
last_row = sheet.Cells(sheet.Rows.Count, price_column).End(xlUp).Row ' get the last row | |
' Find last numeric value | |
Do Until IsNumeric(sheet.Cells(last_row, price_column).Value2) Or last_row = 1 | |
last_row = last_row - 1 | |
Loop | |
' Setup variables | |
Dim returns() As Double | |
ReDim returns(1 To (last_row - first_row)) | |
Dim prices() As Variant | |
prices = sheet.Range(sheet.Cells(first_row, price_column), sheet.Cells(last_row, price_column)).Value2 ' copy prices to new array so we don't recalc | |
' Calculate average of negative returns | |
Dim avg As Double, j As Long | |
j = 1 | |
avg = 0 | |
For i = 1 To last_row - first_row | |
If prices(i + 1, 1) / prices(i, 1) < 1 Then | |
avg = ((j - 1) * avg + (Log(prices(i + 1, 1) / prices(i, 1)) ^ 2)) / j | |
j = j + 1 | |
End If | |
Next i | |
DDVOL = Sqr(252 * avg) | |
End Function | |
Function DMDD(first_price_cell As Range) As Double | |
' Gets various useful numbers | |
Dim first_row As Long, last_row As Long, price_column As Long, sheet As Worksheet | |
first_row = first_price_cell.Row ' first row | |
price_column = first_price_cell.Column ' price column | |
Set sheet = first_price_cell.Worksheet ' sheet for price | |
last_row = sheet.Cells(sheet.Rows.Count, price_column).End(xlUp).Row ' get the last row | |
' Find last numeric value | |
Do Until IsNumeric(sheet.Cells(last_row, price_column).Value2) Or last_row = 1 | |
last_row = last_row - 1 | |
Loop | |
Dim max As Double | |
max = 0 | |
Dim prices() As Variant | |
prices = sheet.Range(sheet.Cells(first_row, price_column), sheet.Cells(last_row, price_column)).Value2 ' copy prices to new array so we don't recalc | |
For i = 1 To last_row - first_row | |
' have we reached a new high watermark? | |
If prices(i, 1) > max Then | |
max = prices(i, 1) | |
End If | |
' or a new lower mdd watermark? | |
If prices(i, 1) / max - 1 < DMDD Then | |
DMDD = prices(i, 1) / max - 1 | |
End If | |
Next i | |
End Function |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment