Portfolio balancing

[JenniferMurphy]

Is there a simple formula for calculating how to distribute a new investment among several funds in a portfolio to bring them all as close into balance as is possible? If all of the funds are more or less in balance, then the job is easy. But if any of them would be over-valued even if none of the new funds are invested in that fund, then the task becomes more complicated.

Consider this scenario:

[TABLE="class: grid, width: 632"]
<tbody>[TR]
[TD]2[/TD]
[TD="align: right"]New Investment [/TD]
[TD="align: center"]$1,000[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD]C/R[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD="align: right"]Funds
[/TD]
[TD="align: right"]Fund A
[/TD]
[TD="align: right"]Fund B
[/TD]
[TD="align: right"]Fund C
[/TD]
[TD="align: right"]Fund D
[/TD]
[TD="align: right"]Sum
[/TD]
[TD="align: center"]Formulas[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD="align: right"]Target %[/TD]
[TD="align: right"]10%[/TD]
[TD="align: right"]20%[/TD]
[TD="align: right"]30%[/TD]
[TD="align: right"]40%[/TD]
[TD="align: right"]100%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD="align: right"]Current Balance[/TD]
[TD="align: right"]$700[/TD]
[TD="align: right"]$2,000[/TD]
[TD="align: right"]$2,800[/TD]
[TD="align: right"]$4,500[/TD]
[TD="align: right"]$10,000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD="align: right"]Target Current Balance[/TD]
[TD="align: right"]$1,000[/TD]
[TD="align: right"]$2,000[/TD]
[TD="align: right"]$3,000[/TD]
[TD="align: right"]$4,000[/TD]
[TD="align: right"]$10,000[/TD]
[TD]=BalanceBegTotal*PCTgt[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD="align: right"]Difference[/TD]
[TD="align: right"]-$300[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]-$200[/TD]
[TD="align: right"]+$500[/TD]
[TD="align: right"]$0[/TD]
[TD]=BalOld-BalOldTgt[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD="align: right"]Current %s[/TD]
[TD="align: right"]7.0%[/TD]
[TD="align: right"]20.0%[/TD]
[TD="align: right"]28.0%[/TD]
[TD="align: right"]45.0%[/TD]
[TD="align: right"]100%[/TD]
[TD]=BalOld/BalanceBegTotal[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD="align: right"]Difference[/TD]
[TD="align: right"]-3.00%[/TD]
[TD="align: right"]0.00%[/TD]
[TD="align: right"]-2.00%[/TD]
[TD="align: right"]+5.00%[/TD]
[TD="align: right"]0%[/TD]
[TD]=PCOld-PCTgt[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD="align: right"]Current % New Balance[/TD]
[TD="align: right"]6.4%[/TD]
[TD="align: right"]18.2%[/TD]
[TD="align: right"]25.5%[/TD]
[TD="align: right"]40.9%[/TD]
[TD="align: right"]91%[/TD]
[TD]=BalOld/BalNewTot[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD="align: right"]Difference[/TD]
[TD="align: right"]-3.64%[/TD]
[TD="align: right"]-1.82%[/TD]
[TD="align: right"]-4.55%[/TD]
[TD="align: right"]+0.91%[/TD]
[TD="align: right"]-9%[/TD]
[TD]=BalOldBalNewPC-PCTgt[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD="align: right"]Target New Balance[/TD]
[TD="align: right"]$1,100[/TD]
[TD="align: right"]$2,200[/TD]
[TD="align: right"]$3,300[/TD]
[TD="align: right"]$4,400[/TD]
[TD="align: right"]$11,000[/TD]
[TD]=BalNewTotTgt*PCTgt[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD="align: right"]Difference[/TD]
[TD="align: right"]$400[/TD]
[TD="align: right"]$200[/TD]
[TD="align: right"]$500[/TD]
[TD="align: right"]-$100[/TD]
[TD="align: right"]$1,000[/TD]
[TD]=BalNewTgt-BalOld[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD="align: right"]Investment[/TD]
[TD="align: right"]$384[/TD]
[TD="align: right"]$167[/TD]
[TD="align: right"]$449[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]$1,000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD="align: right"]New Balance[/TD]
[TD="align: right"]$1,084[/TD]
[TD="align: right"]$2,167[/TD]
[TD="align: right"]$3,249[/TD]
[TD="align: right"]$4,500[/TD]
[TD="align: right"]$11,000[/TD]
[TD]=BalOld+InvestmentEach[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD="align: right"]New %s[/TD]
[TD="align: right"]9.9%[/TD]
[TD="align: right"]19.7%[/TD]
[TD="align: right"]29.5%[/TD]
[TD="align: right"]40.9%[/TD]
[TD="align: right"]100%[/TD]
[TD]=BalNew/BalNewTot[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD="align: right"]Difference[/TD]
[TD="align: right"]98.5%[/TD]
[TD="align: right"]98.5%[/TD]
[TD="align: right"]98.5%[/TD]
[TD="align: right"]102.3%[/TD]
[TD="align: right"]100.0%[/TD]
[TD]=PCNew/PCTgt[/TD]
[/TR]
</tbody>[/TABLE]

I own 4 funds (A, B, C, & D) and my target allocation for them is 10%, 20%, 30%, & 40%. I have $1,000 to invest. I see that the funds are slightly out of balance. Fund A is down $300 (D8) or 3% (D9). Fund B is right on target. Fund C is down $200 or 2%. And Fund D is up $500 or 5%.

In Row 11, I calculate the % the current balances are of the new total balance and in Row 12 the difference. I see that Fund D will be over valued even after the new total investment balance of %1,100 (G11 & G12). So, clearly, I don't want to put any more money into that fund. But how to I allocate the $1,000 among the other 3 funds to bring them as close to being in balance as possible?

I ended up using a trial and error method to arrive at the investment allocations in Row 15. I fiddled around with the numbers until the % differences (Row 18) were equal.

A couple of questions:

1. Is this allocation the most balanced?
2. Is there a closed form formula for calculating each one without trial and error or iterations?

Thanks

 

[StephenCrump]

I'm sorry for such a long distracting response to Stephen. I should have let it go for now.

Sorry, my post #5 was too curt. Absolutely no disrespect or dismissiveness intended!

Nor was I intending to imply that my interpretation was necessarily correct - I have been wrong plenty of times on this forum.

 

[joeu2004]

Sorry that I was not helpful. And I will not be able to help you further.

Oh well, I'll give it one more shot.

First, let me say that the across-the-board "no sells" restriction runs contrary to the goals of rebalancing for long-term portfolio management; exceptions are reasonable. But as Investopedia defines it: "Rebalancing involves periodically buying or selling assets in a portfolio to maintain an original desired level of asset allocation". There are specific reasons for doing that.

Be that as it may, the following solution is one way to implement that strategy. And perhaps my example will provide an "aha!" moment regarding the "no sell" restriction. The solution works for 4 funds. I would use a VBA implementation to generalize it for n funds. The solution does not necessarily provide an optimal solution. As I said, that is a linear programming problem; perhaps the Solver LP Simplex method in Excel 2010 (I think) and later.

Basically, the solution "iterates" until the solution has no negative changes (sells). That should require at most 4 iterations for 4 funds. So the "iterations" are evaluated in separate tables. The formulas are designed so that you only have to look at the last table.

[TABLE="class: grid, width: 700"]
<tbody>[TR]
[TD]
[/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]D
[/TD]
[TD="align: center"]E
[/TD]
[TD="align: center"]F
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD="align: center"]1
[/TD]
[TD]
[/TD]
[TD="align: right"]Fund A[/TD]
[TD="align: right"]Fund B[/TD]
[TD="align: right"]Fund C[/TD]
[TD="align: right"]Fund D[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD]Target %[/TD]
[TD="align: right"]10.00%[/TD]
[TD="align: right"]20.00%[/TD]
[TD="align: right"]30.00%[/TD]
[TD="align: right"]40.00%[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[/TR]
[TR]
[TD="align: center"]3
[/TD]
[TD]Current
Balance[/TD]
[TD="align: right"]$4,154.00[/TD]
[TD="align: right"]$1,777.00[/TD]
[TD="align: right"]$2,251.00[/TD]
[TD="align: right"]$1,818.00[/TD]
[TD="align: right"]$10,000.00[/TD]
[TD="align: right"]
[/TD]
[/TR]
[TR]
[TD="align: center"]4
[/TD]
[TD]Current %[/TD]
[TD="align: right"]41.54%[/TD]
[TD="align: right"]17.77%[/TD]
[TD="align: right"]22.51%[/TD]
[TD="align: right"]18.18%[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[/TR]
[TR]
[TD="align: center"]5
[/TD]
[TD]Diff %[/TD]
[TD="align: right"]31.54%[/TD]
[TD="align: right"]-2.23%[/TD]
[TD="align: right"]-7.49%[/TD]
[TD="align: right"]-21.82%[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[/TR]
[TR]
[TD="align: center"]6
[/TD]
[TD]Additional
Funds[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]$1,000.00[/TD]
[TD="align: right"]
[/TD]
[/TR]
[TR]
[TD="align: center"]7
[/TD]
[TD]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[/TR]
[TR]
[TD="align: center"]8
[/TD]
[TD]Allocate[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]$11,000.00[/TD]
[TD="align: right"]
[/TD]
[/TR]
[TR]
[TD="align: center"]9
[/TD]
[TD]Target
Balance[/TD]
[TD="align: right"]$1,100.00[/TD]
[TD="align: right"]$2,200.00[/TD]
[TD="align: right"]$3,300.00[/TD]
[TD="align: right"]$4,400.00[/TD]
[TD="align: right"]$11,000.00[/TD]
[TD="align: right"]
[/TD]
[/TR]
[TR]
[TD="align: center"]10
[/TD]
[TD]Change[/TD]
[TD="align: right"]-$3,054.00[/TD]
[TD="align: right"]$423.00[/TD]
[TD="align: right"]$1,049.00[/TD]
[TD="align: right"]$2,582.00[/TD]
[TD="align: right"]$1,000.00[/TD]
[TD="align: right"]0-3 might be negative[/TD]
[/TR]
[TR]
[TD="align: center"]11
[/TD]
[TD]New %[/TD]
[TD="align: right"]10.00%[/TD]
[TD="align: right"]20.00%[/TD]
[TD="align: right"]30.00%[/TD]
[TD="align: right"]40.00%[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[/TR]
[TR]
[TD="align: center"]12
[/TD]
[TD]Diff %[/TD]
[TD="align: right"]0.00%[/TD]
[TD="align: right"]0.00%[/TD]
[TD="align: right"]0.00%[/TD]
[TD="align: right"]0.00%[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[/TR]
[TR]
[TD="align: center"]13
[/TD]
[TD]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[/TR]
[TR]
[TD="align: center"]14
[/TD]
[TD]Allocate[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]$6,846.00[/TD]
[TD="align: right"]
[/TD]
[/TR]
[TR]
[TD="align: center"]15
[/TD]
[TD]Target
Balance[/TD]
[TD="align: right"]$4,154.00[/TD]
[TD="align: right"]$1,521.33[/TD]
[TD="align: right"]$2,282.00[/TD]
[TD="align: right"]$3,042.67[/TD]
[TD="align: right"]$11,000.00[/TD]
[TD="align: right"]
[/TD]
[/TR]
[TR]
[TD="align: center"]16
[/TD]
[TD]Change[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]-$255.67[/TD]
[TD="align: right"]$31.00[/TD]
[TD="align: right"]$1,224.67[/TD]
[TD="align: right"]$1,000.00[/TD]
[TD="align: right"]0-2 might be negative[/TD]
[/TR]
[TR]
[TD="align: center"]17
[/TD]
[TD]New %[/TD]
[TD="align: right"]37.76%[/TD]
[TD="align: right"]13.83%[/TD]
[TD="align: right"]20.75%[/TD]
[TD="align: right"]27.66%[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[/TR]
[TR]
[TD="align: center"]18
[/TD]
[TD]Diff %[/TD]
[TD="align: right"]27.76%[/TD]
[TD="align: right"]-6.17%[/TD]
[TD="align: right"]-9.25%[/TD]
[TD="align: right"]-12.34%[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[/TR]
[TR]
[TD="align: center"]19
[/TD]
[TD]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[/TR]
[TR]
[TD="align: center"]20
[/TD]
[TD]Allocate[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]$5,069.00[/TD]
[TD="align: right"]
[/TD]
[/TR]
[TR]
[TD="align: center"]21
[/TD]
[TD]Target
Balance[/TD]
[TD="align: right"]$4,154.00[/TD]
[TD="align: right"]$1,777.00[/TD]
[TD="align: right"]$2,172.43[/TD]
[TD="align: right"]$2,896.57[/TD]
[TD="align: right"]$11,000.00[/TD]
[TD="align: right"]
[/TD]
[/TR]
[TR]
[TD="align: center"]22
[/TD]
[TD]Change[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]-$78.57[/TD]
[TD="align: right"]$1,078.57[/TD]
[TD="align: right"]$1,000.00[/TD]
[TD="align: right"]0-1 might be negative[/TD]
[/TR]
[TR]
[TD="align: center"]23
[/TD]
[TD]New %[/TD]
[TD="align: right"]37.76%[/TD]
[TD="align: right"]16.15%[/TD]
[TD="align: right"]19.75%[/TD]
[TD="align: right"]26.33%[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[/TR]
[TR]
[TD="align: center"]24
[/TD]
[TD]Diff %[/TD]
[TD="align: right"]27.76%[/TD]
[TD="align: right"]-3.85%[/TD]
[TD="align: right"]-10.25%[/TD]
[TD="align: right"]-13.67%[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[/TR]
[TR]
[TD="align: center"]25
[/TD]
[TD]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[/TR]
[TR]
[TD="align: center"]26
[/TD]
[TD]Allocate[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]$2,818.00[/TD]
[TD="align: right"]
[/TD]
[/TR]
[TR]
[TD="align: center"]27
[/TD]
[TD]Target
Balance
[/TD]
[TD="align: right"]$4,154.00[/TD]
[TD="align: right"]$1,777.00[/TD]
[TD="align: right"]$2,251.00[/TD]
[TD="align: right"]$2,818.00[/TD]
[TD="align: right"]$11,000.00[/TD]
[TD="align: right"]
[/TD]
[/TR]
[TR]
[TD="align: center"]28
[/TD]
[TD]Change[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$1,000.00[/TD]
[TD="align: right"]$1,000.00[/TD]
[TD="align: right"]0 might be negative[/TD]
[/TR]
[TR]
[TD="align: center"]29
[/TD]
[TD]New %[/TD]
[TD="align: right"]37.76%[/TD]
[TD="align: right"]16.15%[/TD]
[TD="align: right"]20.46%[/TD]
[TD="align: right"]25.62%[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[/TR]
[TR]
[TD="align: center"]30
[/TD]
[TD]Diff %[/TD]
[TD="align: right"]27.76%[/TD]
[TD="align: right"]-3.85%[/TD]
[TD="align: right"]-9.54%[/TD]
[TD="align: right"]-14.38%[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[/TR]
</tbody>[/TABLE]

F3:  =SUM(B3:E3)
B4:  =B3/$F3      copy into C4:E4
B5:  =B4-B$2      copy into C5:E5

F8:  =F3+F6
F9:  =SUM(B9:E9)
F10: =SUM(B10:E10)
B9:  =$F8*B2     copy into C9:E9
B10: =B9-B$3     copy into C10:E10
B11: =B9/$F9     copy into C11:E11
B12: =B11-B$2    copy into C12:E12

Copy A8:F12 into A14; change:
F14: =IF(COUNTIF(B10:E10,"<0")=0, 0, $F$8-SUMIF($B$10:$E$10,"<0",$B$3:$E$3))
B15: =IF($F14=0, B9, IF(B$10<0,B$3,$F14*B$2/SUMIF($B$10:$E$10,">=0",$B$2:$E$2)))
Copy B15 into C15:E15

Copy A14:F18 into A20; change:
F20: =IF(COUNTIF(B16:E16,"<0")=0, 0, $F$8-SUMPRODUCT(--(($B$10:$E$10<0)+($B$16:$E$16<0)>0),$B$3:$E$3))
B21: =IF($F20=0, B15, IF(OR(B$10<0,B$16<0),B$3,$F20*B$2/SUMPRODUCT(($B$10:$E$10>=0)*($B$16:$E$16>=0),$B$2:$E$2)))
Copy B21 into C21:E21

Copy A20:F24 into A26; change:
F26: =IF(COUNTIF(B22:E22,"<0")=0, 0, $F$8-SUMPRODUCT(--(($B$10:$E$10<0)+($B$16:$E$16<0)+($B$22:$E$22<0)>0),$B$3:$E$3))
B27: =IF($F26=0, B21, IF(OR(B$10<0,B$16<0,B$22<0),B$3,$F26*B$2/SUMPRODUCT(($B$10:$E$10>=0)*($B$16:$E$16>=0)*($B$22:$E$22>=0),$B$2:$E$2)))
Copy B27 into C27:E27