Is there a formula to rebalance investment funds?

[JenniferMurphy]

Suppose I have 3 index funds as shown in the minisheet below. My investment strategy asks that I try to maintain the 3 funds so that A is 50%, B is 40% and C is 10%. As you can see, A is at 59% or 9% too high, B is at 26% or 14% too low. C is at 15% or 5% too high.

Suppose I want to withdraw $50,000 from this account. I would like to do it so as to bring the funds as close to the target percentages as possible. In the Withdrawal Amount column, I found the correct allocation by trial and error.

Is there a formula that will calculate the best way to divide up a withdrawal from 3 index funds to achieve the best balance? Or do I need to write a macro that will do the trial and error for me?

Thanks

Rebalance.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M
3			$224,245.86	Current Total (TotOld)
4			$50,000.00	Withdrawal Amount (WDAmt)
5			$174,245.86	Adjusted Total (TotAdj)				Table Name: TblRebalMan
6	Fund	Target %	Old Balances	Old %	Old % - Tgt %	Target Balances	Target Rebalance △	Withdrawal Amount	Rebalanced Balance	Target - Actual	Rebalanced %	Rebalanced% - Target%
7	A	50%	$131,931.46	58.83%	+8.83%	$87,122.93	-$44,808.53	$39,524.36	$92,407.10	-$5,284.17	53.03%	+3.03259%
8	B	40%	$59,130.00	26.37%	-13.63%	$69,698.34	+$10,568.34	$0.00	$59,130.00	+$10,568.34	33.93%	-6.06519%
9	C	10%	$33,184.40	14.80%	+4.80%	$17,424.59	-$15,759.81	$10,475.64	$22,708.76	-$5,284.17	13.03%	+3.03260%
10	Total	100%	$224,245.86	100.00%	0.00%	$174,245.86	-$50,000.00	$50,000.00	$174,245.86	$0.00	100.00%	+0.00000%
Rebalance Manual

Cell Formulas
Range	Formula
D3	D3	=TblRebalMan[[#Totals],[Old Balances]]
E3	E3	="Current Total (" & GetRangeName(TotOld) & ")"
E4	E4	="Withdrawal Amount (" & GetRangeName(WDAmt) & ")"
D5	D5	=TblRebalMan[[#Totals],[Old Balances]]-WDAmt
E5	E5	="Adjusted Total (" & GetRangeName(TotAdj) & ")"
I5	I5	=+GetTableName(TblRebalMan[[#Headers],[Fund]])
E7:E9	E7	=[@[Old Balances]]/TblRebalMan[[#Totals],[Old Balances]]
F7:F9	F7	=[@[Old %]]-[@[Target %]]
G7:G9	G7	=[@[Target %]]*TotAdj
H7:H9	H7	=[@[Target Balances]]-[@[Old Balances]]
J7:J9	J7	=[@[Old Balances]]-[@[Withdrawal Amount]]
K7:K9	K7	=[@[Target Balances]]-[@[Rebalanced Balance]]
L7:L9	L7	=[@[Rebalanced Balance]]/TblRebalMan[[#Totals],[Rebalanced Balance]]
M7:M9	M7	=[@[Rebalanced %]]-[@[Target %]]
I9	I9	=WDAmt-TAKE([Withdrawal Amount],1)
C10	C10	=SUBTOTAL(109,[Target %])
D10	D10	=SUBTOTAL(109,[Old Balances])
E10	E10	=SUBTOTAL(109,[Old %])
F10	F10	=SUBTOTAL(109,[Old % - Tgt %])
G10	G10	=SUBTOTAL(109,[Target Balances])
H10	H10	=SUBTOTAL(109,[Target Rebalance △])
I10	I10	=SUBTOTAL(109,[Withdrawal Amount])
J10	J10	=SUBTOTAL(109,[Rebalanced Balance])
K10	K10	=SUBTOTAL(109,[Target - Actual])
L10	L10	=SUBTOTAL(109,[Rebalanced %])
M10	M10	=SUBTOTAL(109,[Rebalanced% - Target%])

Named Ranges
Name	Refers To	Cells
'Rebalance Manual'!TotAdj	='Rebalance Manual'!$D$5	E5, G7:G9
'Rebalance Manual'!TotOld	='Rebalance Manual'!$D$3	E3
'Rebalance Manual'!WDAmt	='Rebalance Manual'!$D$4	I9, E4, D5

 

[JenniferMurphy]

Here's the basic formula to work with your +ve withdrawal value:
=(E7>C7)*((((SUMIF(H$7:H$9,"<0",D$7:D$9)-WDAmt)/-TotAdj+SUMIF(H$7:H$9,"<0",C$7:C$9))/2-C7)*-TotAdj-D7)
and, for the more flexible version:
=MAX((E7>C7)*((((SUMIF(H$7:H$9,"<0",D$7:D$9)-WDAmt)/-TotAdj+SUMIF(H$7:H$9,"<0",C$7:C$9))/2-C7)*-TotAdj-D7),-WDAmt)
I'll work on the code/narrative version...

Thanks

 

[Macropod]

And here's a basic sub:

Sub Withdrawals()
Application.ScreenUpdating = False
Dim r As Long, i As Long, Val As Double, TgtPct As Double
With ActiveSheet
  For r = 7 To 9
    If .Range("H" & r).Value < 0 Then
      Val = Val + .Range("D" & r).Value
      TgtPct = TgtPct + .Range("C" & r).Value
      i = i + 1
    End If
  Next
  TgtPct = ((Val - .Range("D4").Value) / .Range("D5").Value - TgtPct) / i
  For r = 7 To 9
    If .Range("E" & r).Value > .Range("C" & r).Value Then
      Val = (.Range("C" & r).Value + TgtPct) * .Range("D5").Value - .Range("D" & r).Value
    Else
      Val = 0
    End If
    If Val < -.Range("D4").Value Then Val = -.Range("D4").Value
    .Range("I" & r).Value = Format(Val, "$#,##0.00")
  Next
End With
Application.ScreenUpdating = True
End Sub

 

[JenniferMurphy]

And here's a basic sub:

I will give it a try. It would help if you could add a few comments explaining what each section of code does.

Thank you very much.

 

[JenniferMurphy]

It works great for $50,000

And for $15,000

But fails for $14,000 and below

And for $77,000 and above

 

[Macropod]

Commented code, using your named ranges:

Sub Withdrawals()
Application.ScreenUpdating = False
Dim r As Long, i As Long, Val As Double, TgtPct As Double
With ActiveSheet
  'Collect data from the index fund rows in use
  For r = 7 To 9
    'Check each index fund for an excess balance
    If .Range("H" & r).Value < 0 Then
      'Sum the $ of each index fund with excess balance
      Val = Val + .Range("D" & r).Value
      'Sum the % of each index fund with excess balance
      TgtPct = TgtPct + .Range("C" & r).Value
      'Update counter for index funds with excess balance
      i = i + 1
    End If
  Next
  'Calculate the required target % variance to be applied to each of the original % figures
  'Basically, Val minus WDAmt, divided by TotAdj, then subtract TgtPct. This gives the
  'required overall % adjustment. Divide the result by the count of adjustment items to get
  'the required individual index fund % adjustment.
  TgtPct = ((Val - .Range("WDAmt").Value) / .Range("TotAdj").Value - TgtPct) / i
  'Update the index fund rows in use
  For r = 7 To 9
    'If the actual % is greater than the target %, calculate the withdrawal value
    If .Range("E" & r).Value > .Range("C" & r).Value Then
      'Sum the required target % variance and the original %,
      'then multiply by TotAdj and subtract the old index fund $
      Val = (.Range("C" & r).Value + TgtPct) * .Range("TotAdj").Value - .Range("D" & r).Value
    Else
      Val = 0
    End If
    'If the calculated withdrawal $ exceeds the total withdrawal $,
    'reduce the calculated withdrawal $ accordingly
    If Val < -.Range("WDAmt").Value Then Val = -.Range("WDAmt").Value
    'Output the result in column I
    .Range("I" & r).Value = Format(Val, "$#,##0.00")
  Next
End With
Application.ScreenUpdating = True
End Sub

The code doesn't work for withdrawal amounts below about $15,500 or over the $77,000 with the current fund size because it's not possible to balance the index fund % figure targets in those cases.

 

[Macropod]

FWIW, if you use the formula approach and you're trying to withdraw large or small amounts, for the current fund profile you could use Goal Seek to change the value in D4 until I9 reaches 0 (for small withdrawals), or H8 for large withdrawals), so as to find the upper & lower limits at which the percentages will balance.

Thus, with the existing index fund values the most you can withdraw without withdrawing further amounts proportionally from all three index funds is $76,420.46. Conversely, the least you can withdraw and minimise the percentage variance without withdrawing further amounts solely from the largest index fund is $15,082.13

 

[JenniferMurphy]

FWIW, if you use the formula approach and you're trying to withdraw large or small amounts, for the current fund profile you could use Goal Seek to change the value in D4 until I9 reaches 0 (for small withdrawals), or H8 for large withdrawals), so as to find the upper & lower limits at which the percentages will balance.

Thus, with the existing index fund values the most you can withdraw without withdrawing further amounts proportionally from all three index funds is $76,420.46. Conversely, the least you can withdraw and minimise the percentage variance without withdrawing further amounts solely from the largest index fund is $15,082.13

It seems to me that a fairly simple modification can make the code work all withdrawal amounts.

For withdrawal amounts < $15,082.13, the value of Val after the first iteration of the second loop will be equal to the withdrawal amount. The code can detect that and set the rest of the withdrawal amounts to $0. Or it can check for this condition before even starting the second loop.

For withdrawal amounts > $76,420.46, the value of i will be 3 (all of the funds will need to have withdrawals). So if i=3, simply set them all to their target withdrawal amounts and exit.

I don't understand the code well enough to make those changes myself.

This requires that the table be sorted largest to smallest on the Old Balances column. Or the code could load the values and sort them.

 

[Macropod]

For small withdrawals, the issue can be addressed by inserting:

    'Avoid potential implied credits arising out of small WDAmt
    If Val > 0 Then Val = 0

before:

    'Output the result in column I
    .Range("I" & r).Value = Format(Val, "$#,##0.00")

For large withdrawals, I believe the issue can be addressed by inserting:

  'Calculate the allocated $
  Val = 0
  For r = 7 To 9
    Val = Val + .Range("I" & r).Value
  Next
  Val = -(Val + .Range("WDAmt").Value)
  'If Val <> 0 then a withdrawal remainder needs to be allocated to the omitted index fund(s)
  If Val <> 0 Then
    For r = 7 To 9
      'Check each index fund omitted from first round
      If .Range("I" & r).Value = 0 Then TgtPct = TgtPct + .Range("C" & r).Value
    Next
    For r = 7 To 9
      'Apportion Val between index funds omitted from first round
      If .Range("I" & r).Value = 0 Then .Range("I" & r).Value = Format(Val * .Range("C" & r).Value / TgtPct, "$#,##0.00")
    Next
  End If

before:

End With
Application.ScreenUpdating = True
End Sub

.

 

[JenniferMurphy]

For small withdrawals, the issue can be addressed by inserting:
. . .

That is some amazing code. I did quite a bit of testing and only found two holes.

1. It will accept withdrawal amounts that exceed the fund total.
2. It will accept negative withdrawal amounts.

These should each generate errors.

I did make a few stylistic changes. Here's the current code. I'll post some test results next.

Sub Withdrawals()
Application.ScreenUpdating = False

'  Define table columns
Const ColTgtPC As String = "C"      'Target fund percentages
Const ColOldBal As String = "D"     'Old fund balances
Const ColOldPC As String = "E"      'Old fund percentages
Const ColTgtRebal As String = "H"   'Target rebalance delta
Const ColWDAmt As String = "I"      'Withdrawal amounts

Dim row As Long      'Row number
Dim i As Long        'Number of funds over the target %
Dim Val As Double    'The withdrawal value
Dim TgtPct As Double 'Sum of target %s

i = 0       'Zero the count
Val = 0     'Zero withdrawal
TgtPct = 0  'Zero the target %

With ActiveSheet
  'Collect data from the index fund rows in use
  For row = 7 To 9
    'Check each index fund for an excess balance
    If .Range(ColTgtRebal & row).Value < 0 Then
      'Sum the $ of each index fund with excess balance
      Val = Val + .Range(ColOldBal & row).Value
      'Sum the % of each index fund with excess balance
      TgtPct = TgtPct + .Range(ColTgtPC & row).Value
      'Update counter for index funds with excess balance
      i = i + 1
    End If
  Next row
  'Calculate the required target % variance to be applied to each of the original % figures
  'Basically, Val minus WDAmt, divided by TotAdj, then subtract TgtPct. This gives the
  'required overall % adjustment. Divide the result by the count of adjustment items to get
  'the required individual index fund % adjustment.
  TgtPct = ((Val - .Range("WDAmt").Value) / .Range("TotAdj").Value - TgtPct) / i
  'Update the index fund rows in use
  For row = 7 To 9
    'If the actual % is greater than the target %, calculate the withdrawal value
    If .Range(ColOldPC & row).Value > .Range(ColTgtPC & row).Value Then
      'Sum the required target % variance and the original %,
      'then multiply by TotAdj and subtract the old index fund $
      Val = (.Range(ColTgtPC & row).Value + TgtPct) * .Range("TotAdj").Value - .Range(ColOldBal & row).Value
    Else
      Val = 0
    End If
    'If the calculated withdrawal $ exceeds the total withdrawal $,
    'reduce the calculated withdrawal $ accordingly
    If Val < -.Range("WDAmt").Value Then Val = -.Range("WDAmt").Value
    'Avoid potential implied credits arising out of small WDAmt
    If Val > 0 Then Val = 0
    'Output the result in column I rounded to dollars and cents
    .Range(ColWDAmt & row).Value = Format(Val, "$#,##0.00")
  Next row
  'Calculate the allocated $
  Val = 0
  For row = 7 To 9
    Val = Val + .Range(ColWDAmt & row).Value
  Next row
  Val = -(Val + .Range("WDAmt").Value)
  'If Val <> 0 then a remainder of withdrawal needs to be allocated
  If Val <> 0 Then
    For row = 7 To 9
      'Check each index fund omitted from first round
      If .Range(ColWDAmt & row).Value = 0 Then TgtPct = TgtPct + .Range(ColTgtPC & row).Value
    Next row
    For row = 7 To 9
      'Apportion Val between index funds omitted from first round
      If .Range(ColWDAmt & row).Value = 0 Then
         .Range(ColWDAmt & row).Value = Format(Val * .Range(ColTgtPC & row).Value / TgtPct, "$#,##0.00")
      End If
    Next row
  End If
End With

Application.ScreenUpdating = True
End Sub

 

[JenniferMurphy]

Here are some test results.

My original condition.

A few smaller withdrawals

Smallest withdrawal that can be split between funds.

One penny less. Anything less comes all from one fund.

More in another post...