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

 

[Macropod]

The formulae I'd be inclined to use are along the lines of:

G7: =C$5*B7: H7: =G7-C7: I7: =IF(H7<0,H$10/SUMIF(H$7:H$9,"<0",H$7:H$9)*H7,0): Withdrawn: -$36,990.32: New Bal: $94,941.14: New %: 54.5
G8: =C$5*B8: H8: =G8-C8: I8: =IF(H8<0,H$10/SUMIF(H$7:H$9,"<0",H$7:H$9)*H8,0): Withdrawn:  $     0.00: New Bal: $59,130.00: New %: 33.9
G9: =C$5*B9: H9: =G9-C9: I9: =IF(H9<0,H$10/SUMIF(H$7:H$9,"<0",H$7:H$9)*H9,0): Withdrawn: -$13,009.68: New Bal: $20,174.32: New %: 11.6

The 'balanced' figures are different from yours because the out-of-balance figures are reduced by the same proportions.

 

[JenniferMurphy]

The formulae I'd be inclined to use are along the lines of:

G7: =C$5*B7: H7: =G7-C7: I7: =IF(H7<0,H$10/SUMIF(H$7:H$9,"<0",H$7:H$9)*H7,0): Withdrawn: -$36,990.32: New Bal: $94,941.14: New %: 54.5
G8: =C$5*B8: H8: =G8-C8: I8: =IF(H8<0,H$10/SUMIF(H$7:H$9,"<0",H$7:H$9)*H8,0): Withdrawn:  $     0.00: New Bal: $59,130.00: New %: 33.9
G9: =C$5*B9: H9: =G9-C9: I9: =IF(H9<0,H$10/SUMIF(H$7:H$9,"<0",H$7:H$9)*H9,0): Withdrawn: -$13,009.68: New Bal: $20,174.32: New %: 11.6

The 'balanced' figures are different from yours because the out-of-balance figures are reduced by the same proportions.

My objective is for the rebalanced %s to all differ from the target %s by an equal amount. In my example, the %△s both equal 3.02% (last column). I think that guarantees that they will both be equally close to their target %s. I think that is the definition of "balanced" (or more closely balanced). First table below.

If I implemented your solution correctly in the second table below, you withdraw more from Fund C and less from Fund A, so C is closer to its target % (+1.58% vs +4.49%). Let me know if I did that wrong.

Lacking a closed form expression, I think the only solution is a macro that implements my manual solution. It will test different values for the withdrawal amounts until the %△s are equal. Let me know if you see a closed form solution.

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	Trial and error		$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.00%
11
12	Solution by Macropod on Mr Excel							Table Name: TblRebalMrEx
13	Fund	Target %	Old Balances	Old %	Old % - Tgt %	Target Balances	Target Rebalance △	Withdrawal Amount	Rebalanced Balance	Target - Actual	Rebalanced %	Rebalanced% - Target%
14	A	50%	$131,931.46	58.83%	+8.83%	$87,122.93	-$44,808.53	-$36,990.06	$94,941.40	-$7,818.47	54.49%	+4.48703%
15	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%
16	C	10%	$33,184.40	14.80%	+4.80%	$17,424.59	-$15,759.81	-$13,009.94	$20,174.46	-$2,749.87	11.58%	+1.57816%
17	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.00%
Rebalance

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]]
F14:F16,F7:F9	F7	=[@[Old %]] - [@[Target %]]
G14:G16,G7:G9	G7	=TotAdj * [@[Target %]]
H14:H16,H7:H9	H7	=[@[Target Balances]] - [@[Old Balances]]
J7:J9	J7	=[@[Old Balances]] - [@[Withdrawal Amount]]
K14:K16,K7:K9	K7	=[@[Target Balances]] - [@[Rebalanced Balance]]
L14:L16,L7:L9	L7	=[@[Rebalanced Balance]] / TblRebalMan[[#Totals],[Rebalanced Balance]]
M14:M16,M7:M9	M7	=[@[Rebalanced %]] - [@[Target %]]
I9	I9	=WDAmt-TAKE([Withdrawal Amount],1)
C10,C17	C10	=SUBTOTAL(109,[Target %])
D10,D17	D10	=SUBTOTAL(109,[Old Balances])
E10,E17	E10	=SUBTOTAL(109,[Old %])
F10,F17	F10	=SUBTOTAL(109,[Old % - Tgt %])
G10,G17	G10	=SUBTOTAL(109,[Target Balances])
H10,H17	H10	=SUBTOTAL(109,[Target Rebalance △])
I10,I17	I10	=SUBTOTAL(109,[Withdrawal Amount])
J10,J17	J10	=SUBTOTAL(109,[Rebalanced Balance])
K10,K17	K10	=SUBTOTAL(109,[Target - Actual])
L10,L17	L10	=SUBTOTAL(109,[Rebalanced %])
M10,M17	M10	=SUBTOTAL(109,[Rebalanced% - Target%])
I12	I12	=+GetTableName(TblRebalMrEx[[#Headers],[Fund]])
B14:D16	C14	=C7
E14:E16	E14	=[@[Old Balances]] / TblRebalMrEx[[#Totals],[Old Balances]]
I14:I16	I14	=IF([@[Target Rebalance △]]<0,TblRebalMrEx[[#Totals],[Target Rebalance △]]/SUMIF([Target Rebalance △],"<0",[Target Rebalance △])*[@[Target Rebalance △]],0)
J14:J16	J14	=[@[Old Balances]] + [@[Withdrawal Amount]]

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

 

[Macropod]

Hi Jennifer,

Yes, you implemented my 'solution' correctly and the results were as expected - it reduces each of the out-of-balance amounts by 17.45% of the imbalance.

 

[JenniferMurphy]

Hi Jennifer,

Yes, you implemented my 'solution' correctly and the results were as expected - it reduces each of the out-of-balance amounts by 17.45% of the imbalance.

Two questions:

1. What is the logic behind that solution? Where did the 17.45% come from?
2. Do you disagree with my objective that the % differences in the adjusted balances be equal?

 

[Macropod]

Hi Jennifer,

Re:
1. If you add up the target rebalance amounts for A & C, you'll see they come to -$60,568.18. Dividing -$60,568.18 by the target withdrawal of -$50,000 = 0.8255, or 82.55%. Then 100%-82.55% = 17.45%
2. Not at all. I simply worked with a different definition of what rebalancing might require - reducing the out-of-balance amounts for A & C by the same proportion. Another definition might require getting the $ imbalance for A & C equal. For your target % figures to be as near as practical the same, you'd need to use something like:
=(D7>B7)*((((SUMIF(H$7:H$9,"<0",C$7:C$9)+C$4)/C$5-SUMIF(H$7:H$9,"<0",B$7:B$9))/2+B7)*G$10-C7)
in I7 and copy down to I9. This will give -$39,524.64 for A and -$10,475.36 for C. These are the correct amounts for the equal % differences.

 

[JenniferMurphy]

Hi Jennifer,

For your target % figures to be as near as practical the same, you'd need to use something like:
=(D7>B7)*((((SUMIF(H$7:H$9,"<0",C$7:C$9)+C$4)/C$5-SUMIF(H$7:H$9,"<0",B$7:B$9))/2+B7)*G$10-C7)
in I7 and copy down to I9. This will give -$39,524.64 for A and -$10,475.36 for C. These are the correct amounts for the equal % differences.

Wow!!! That seems to work, sorta. A couple of glitches:

1. I had to change two of your column letters (D to E & B to C).
2. I replaced cell addresses with named ranges (D4(C4) is WDAmt, D5(C5) is TotAdj, & G10 is also TotAdj).
3. In my implementation, it gets the two withdrawal amounts reversed. I can correct that by subtracting the amounts that are >0 from WDAmt, but I would like to understand why they are reversed.

I can't get my head around it. Can you explain what it is doing? And why the values are reversed?

Thanks

Rebalance.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O
3			$224,245.86	Current Total (TotOld)
4			$50,000.00	Withdrawal Amount (WDAmt)
5	Trial and error		$174,245.86	Adjusted Total (TotAdj)				Table Name: TblRebalMrEx
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	$10,475.64	$121,455.82	-$34,332.89	69.70%	+19.70370%		$10,475.64
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%		$0.00
9	C	10%	$33,184.40	14.80%	+4.80%	$17,424.59	-$15,759.81	$39,524.36	-$6,339.96	+$23,764.55	-3.64%	-13.63851%		$39,524.36
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.00%
Rebalance

Cell Formulas
Range	Formula
D3	D3	=TblRebalMrEx[[#Totals],[Old Balances]]
E3	E3	="Current Total (" & GetRangeName(TotOld) & ")"
E4	E4	="Withdrawal Amount (" & GetRangeName(WDAmt) & ")"
D5	D5	=TblRebalMrEx[[#Totals],[Old Balances]]-WDAmt
E5	E5	="Adjusted Total (" & GetRangeName(TotAdj) & ")"
I5	I5	=+GetTableName(TblRebalMrEx[[#Headers],[Fund]])
E7:E9	E7	=[@[Old Balances]] / TblRebalMrEx[[#Totals],[Old Balances]]
F7:F9	F7	=[@[Old %]] - [@[Target %]]
G7:G9	G7	=TotAdj * [@[Target %]]
H7:H9	H7	=[@[Target Balances]] - [@[Old Balances]]
I7:I9	I7	=([@[Old %]]>[@[Target %]])*((((SUMIF([Target Rebalance △],"<0",[Old Balances])+WDAmt)/TotAdj-SUMIF([Target Rebalance △],"<0",[Target %]))/2+[@[Target %]])*TotAdj-[@[Old Balances]])
J7:J9	J7	=[@[Old Balances]] - [@[Withdrawal Amount]]
K7:K9	K7	=[@[Target Balances]] - [@[Rebalanced Balance]]
L7:L9	L7	=[@[Rebalanced Balance]] / TblRebalMrEx[[#Totals],[Rebalanced Balance]]
M7:M9	M7	=[@[Rebalanced %]] - [@[Target %]]
O7:O9	O7	=(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)
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
TotAdj	=Rebalance!$D$5	E5, G7:G9, O7:O9, I7:I9
TotOld	=Rebalance!$D$3	E3
WDAmt	=Rebalance!$D$4	E4, D5, O7:O9, I7:I9

 

[Macropod]

OK, I missed that your screenshot starts at column B. The formula should have been:
=(E7>C7)*((((SUMIF(H$7:H$9,"<0",D$7:D$9)+D$4)/D$5-SUMIF(H$7:H$9,"<0",C$7:C$9))/2+C7)*G$10-D7)

I'm not sure what you've done but, not only are the withdrawal amounts for A & C swapped, they're also expressed as +ve values instead of -ve.
When I input:
=(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)
into O7, I get the correct values.

A more flexible version of the formula would be:
=MAX((E7>C7)*((((SUMIF(H$7:H$9,"<0",D$7:D$9)+D$4)/D$5-SUMIF(H$7:H$9,"<0",C$7:C$9))/COUNTIF(H$7:H$9,"<0")+C7)*G$10-D7),D$4)
This allows for more (or less) than three index funds (by expanding the row 7-9 range) and, in the event there's only one that's eligible for drawdown, limits the nominated amount to the withdrawal value.

Edit: I just figured it out. You have a +ve value for the WDAmt, whereas I used a -ve value. make that change and make D5 =TotOld-WDAmt and everything will be fine.

 

[JenniferMurphy]

OK, I missed that your screenshot starts at column B. The formula should have been:
=(E7>C7)*((((SUMIF(H$7:H$9,"<0",D$7:D$9)+D$4)/D$5-SUMIF(H$7:H$9,"<0",C$7:C$9))/2+C7)*G$10-D7)

I'm not sure what you've done but, not only are the withdrawal amounts for A & C swapped, they're also expressed as +ve values instead of -ve.
When I input:
=(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)
into O7, I get the correct values.

A more flexible version of the formula would be:
=MAX((E7>C7)*((((SUMIF(H$7:H$9,"<0",D$7:D$9)+D$4)/D$5-SUMIF(H$7:H$9,"<0",C$7:C$9))/COUNTIF(H$7:H$9,"<0")+C7)*G$10-D7),D$4)
This allows for more (or less) than three index funds (by expanding the row 7-9 range) and, in the event there's only one that's eligible for drawdown, limits the nominated amount to the withdrawal value.

Can I bother you to express your more flexible version in words? Or, better yet, in code? I would really like to get my head around the logic and I'll probably put it in a macro.

 

[Macropod]

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...