sbRebalancedReturn
Portfolio rebalancing is the process of bringing the different asset classes back into proper relationship following a significant change in one or more assets. You return your portfolio to the proper mix of stocks, bonds, cash or other assets when they no longer conform to your plan/limits. An example: 1
Const CMaxDouble = 1.79769313486231E+308
2
Function sbRebalancedReturn(rARM As Range, _
3
rIWV As Range, _
4
Optional ByVal lRF As Long = 0, _
5
Optional dDT As Double = CMaxDouble) As Variant
6
'RebalancedReturn calculates balanced returns for a
7
'portfolio with given
8
'rARM - asset return matrix (columns show different
9
' assets, rows show returns per asset over time)
10
'rIWV - initial weight vector for the assets
11
'lRF - rebalancing frequency (in time steps = rows)
12
' If lRF > 0 then each lRF time step rebalancing
13
' will take place
14
' If lRF = 0 then no rebalancing will take place
15
' If lRF < 0 then each -lRF time step after last
16
' rebalance portfolio will be rebalanced again
17
'dDT - drift tolerance %, if any asset has drifted by
18
' by more than dDT (relative measure) then the
19
' portfolio will be rebalanced AND the internal
20
' rebalancing frequency count will be reset
21
'The output matrix shows portfolio returns % in first
22
'column, then end-of-period asset weights and finally
23
'boolean output values in last column, showing whether
24
'a rebalance happened.
25
'This function has been inspired by Andreas Steiner's
26
'similar function.
27
'Reverse("moc.LiborPlus.www") PB 19-Mar-2011 V0.2
28
Dim i As Long, j As Long, k As Long, n As Long, m As Long
29
Dim bDrifted As Boolean, bForceRB As Boolean
30
31
n = rARM.Rows.Count 'Number of observations
32
m = rARM.Columns.Count 'Number of assets
33
34
If m <> rIWV.Columns.Count Or _
35
rIWV.Rows.Count <> 1 Then
36
sbRebalancedReturn = CVErr(xlErrValue)
37
Exit Function
38
End If
39
40
ReDim w0(1 To m) As Double, x(1 To m) As Double
41
ReDim r(1 To n, 1 To m) As Double
42
43
If lRF = 0 Then lRF = n
44
If lRF < 0 Then
45
lRF = -lRF
46
bForceRB = True
47
Else
48
bForceRB = False
49
End If
50
51
ReDim vR(1 To n, 1 To m + 2)
52
For i = 1 To m
53
x(i) = rIWV(i)
54
w0(i) = x(i)
55
For j = 1 To n
56
r(j, i) = rARM(j, i)
57
Next j
58
Next i
59
60
k = 1
61
'Model rebalancing tolerance
62
For i = 1 To n
63
If bDrifted And bForceRB Then k = i
64
'Calculate period start weights
65
vR(i, m + 2) = (i - k) Mod lRF = 0 Or bDrifted
66
If vR(i, m + 2) Then
67
For j = 1 To m
68
x(j) = w0(j)
69
Next j
70
Else
71
For j = 1 To m
72
x(j) = vR(i - 1, 1 + j)
73
Next j
74
End If
75
'Calculate portfolio return
76
For j = 1 To m
77
vR(i, 1) = vR(i, 1) + x(j) * r(i, j)
78
Next j
79
'Calculate period end weights & check for drift
80
bDrifted = False
81
For j = 1 To m
82
vR(i, 1 + j) = x(j) * (1# + r(i, j)) / (1# + vR(i, 1))
83
bDrifted = bDrifted Or Abs(vR(i, 1 + j) - w0(j)) > dDT
84
Next j
85
Next i
86
sbRebalancedReturn = vR
87
End Function
Copied!
sbRebalancedReturn.xlsm
23KB
Binary
sbRebalancedReturn.xlsm