Portfolio balancing

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
2,483
Office Version
  1. 365
Platform
  1. Windows
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:

2New Investment $1,000
C/RCDEFGHI
4Funds
Fund A
Fund B
Fund C
Fund D
Sum
Formulas
5Target %10%20%30%40%100%
6Current Balance$700$2,000$2,800$4,500$10,000
7Target Current Balance$1,000$2,000$3,000$4,000$10,000=BalanceBegTotal*PCTgt
8Difference-$300$0-$200+$500$0=BalOld-BalOldTgt
9Current %s7.0%20.0%28.0%45.0%100%=BalOld/BalanceBegTotal
10Difference-3.00%0.00%-2.00%+5.00%0%=PCOld-PCTgt
11Current % New Balance6.4%18.2%25.5%40.9%91%=BalOld/BalNewTot
12Difference-3.64%-1.82%-4.55%+0.91%-9%=BalOldBalNewPC-PCTgt
13Target New Balance$1,100$2,200$3,300$4,400$11,000=BalNewTotTgt*PCTgt
14Difference$400$200$500-$100$1,000=BalNewTgt-BalOld
15Investment$384$167$449$0$1,000
16New Balance$1,084$2,167$3,249$4,500$11,000=BalOld+InvestmentEach
17New %s9.9%19.7%29.5%40.9%100%=BalNew/BalNewTot
18Difference98.5%98.5%98.5%102.3%100.0%=PCNew/PCTgt

<tbody>
</tbody>

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
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
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?

1. In my opinion, yes. You've brought your three underweight funds up to the same % of target.

2. Depending on how much new money you have to invest, there may be sufficient to allocate to one, two, three or all four funds, e.g. the first $233.33 will be allocated to Fund A, the next tranche to Funds A and C etc etc. A formula could test and adjust for all four possibilities, but it won't be straightforward.

Perhaps a simple Goal Seek instead:

B5: =MAX(B3,$A6*B2*$A7)
B6: =B5/(B2*$F5)

Code:
Range("F5").GoalSeek Goal:=Range("A6").Value, ChangingCell:=Range("A7")


Excel 2010
ABCDEF
1FundsFund AFund BFund CFund DSum
2Target %10%20%30%40%100%
3Current Balance$700$2,000$2,800$4,500$10,000
4
5Rebalance$1,083.33$2,166.67$3,250.00$4,500.00$11,000
6$11,00098.48%98.48%98.48%102.27%
798.48%
Sheet1
 
Upvote 0
Stephen,

Thanks for the suggestion. I'll have to study your solution some as I have not used Goal Seek before.
 
Upvote 0
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.

IMHO, you are overthinking it. The following table demonstrates how to calculate the reallocation objectively; that is, without further considerations.


A
B
C
D
E
F
1
New
Balance




$11,000.00
2
Allocation



$11,000.00
3

Fund A
Fund BFund CFund D
4
Target %
10.00%
20.00%30.00%40.00%100.00%
5
Current
Balance
$700.00$2,000.00$2,800.00$4,500.00$10,000.00
6
Current %7.00%20.00%28.00%45.00%
7
Target
Balance
$1,100.00$2,200.00$3,300.00$4,400.00$11,000.00
8
Change$400.00
$200.00$500.00
-$100.00$1,000.00
9
New %10.00%20.00%30.00%40.00%
10
Diff %0.00%0.00%0.00%0.00%

<tbody>
</tbody>
Code:
F2: =F1
F5: =SUM(B5:E5)    copy into F6:F7
B6: =B5/$F5        copy into C6:E6
B7: =$F2*B4        copy into C7:E7
B8: =B7-B5         copy into C8:E8
B9: =B7/$F7        copy into C9:E9
B10: =B9-B4        copy into C10:E10

Note the balance of Fund D is decreased, not increased, and it is not overweighted after reallocation.

Of course, you might need to make adjustments to the amounts to take subaccount (fund) minimum and unit reinvestments into consideration.

Also, IMHO, it is usually unwise to reallocate completely objectively, especially at the fund level. Other factors to consider are: tax implications of selling a position to fund another; and ideally, the medium- and long-term market for the fund.

For example, now might not be a good time to increase investment in a REIT fund that invests primarily in malls.

With that in mind, suppose you want to leave $4500 in Fund D for good reason....


how to I allocate the $1,000 among the other 3 funds to bring them as close to being in balance as possible?

The following table demonstrates one way. The design is highly simplified ("a hack") for demonstration purposes. There are more flexible designs.

Copy the table above into A13. All of the formulas should update appropriately. The formulas below are just the necessary changes.


A
B
C
D
E
F
13
New
Balance




$11,000.00
14
Allocation



$6,500.00
15

Fund A
Fund BFund CFund D
16
Target %10.00%
20.00%30.00%40.00%100.00%
17
Current
Balance
$700.00$2,000.00$2,800.00$4,500.00$10,000.00
18
Current %7.00%20.00%28.00%45.00%
19
Target
Balance
$1,083.33$2,166.67$3,250.00$4,500.00$11,000.00
20
Change
$383.33$166.67$450.00$0.00$1,000.00
21
New %9.85%19.70%29.55%40.91%
22
Diff %-0.15%-0.30%-0.45%0.91%

<tbody>
</tbody>
Code:
F14: =F13-E17
B19: =$F14*B16/SUM($B16:$D16)    copy into C19:D19
E19: =E17

F14 calculates the remainder of the target $11,000, keeping $4500 in Fund D.

B19 prorates the remainder in the ratios of 10/60, 20/60 and 30/60, the target% of the funds to reallocate divided by the sum of their target%. That retains their relative target%.

Note that diff% is a simple subtraction of new% minus target%. It demonstrates that the new% allocations are very close to target%.
 
Last edited:
Upvote 0
Thanks joeu2004. My assumption from the OP was that we're not going to sell down any overweight position.

So looking at your second solution, how are you going to generalise?

For example, what happens if New Balance in F13 is $10,200 instead of $11,000?
 
Upvote 0
My assumption from the OP was that we're not going to sell down any overweight position.

I don't believe Jennifer said that. But only she can tell us what she meant.

My interpretation....

She did write: "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". One reasonable translation, maybe yours: "leave the investment as it was before rebalance".

But I don't believe that was her intent. Instead, I think it is part of her overthinking. It does not matter how the funds are allocated before rebalancing -- at least, not for an objective rebalance driven purely by the numbers.

More importantly, then she wrote: "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".

My translation: Fund D was overweighted before rebalance; and through some miscalculation or confusion, she concluded that rebalancing called for adding even more, still making Fund D overweighted (albeit much less so). I don't see that in her calculations; but then again, I don't follow her calculations because they seem unnecessarily contorted.

With my first table, I demonstrated that she can attain the target 10/20/30/40% exactly. And in fact, the investment in Fund D is reduced by 100, not increased.

(I also see -100 in Jennifer's table. So I don't see how she concluded that "more money" would be added.)

But even if the investment in Fund D were increased, that does not matter as long as it is at the target 40%. For example, if Jennifer added $2000 instead of $1000, the investment in Fund D would indeed increase from $4500 (overweighted) to $4800 (on target). That is why I say the overweight before rebalance does not matter. All that matters is meeting the target weights after rebalance -- if that is the only goal.

In my earlier comments, I already said that might not or should not be the only goal to look at. I will cover that point in more detail in a follow-up posting later -- probably much later -- if time permits and it is still warranted.

Nevertheless, I would start with a purely numbers-driven (pro rata) allocation. Then I would make subjective and sometimes necessary adjustment. I think it would be wise to do that one step at a time; it is a very subjective process. Of course, any non-pro-rata adjustments will compromise the "on target" goal. That's our choice.


So looking at your second solution, how are you going to generalise?

Of course, we could always incorporate a MAX expression if we did not want to increase particular investments for any reason. But eventually, the percentage must sum to 100%. So the more restrictions that we incorporate at the outset, the less likely it is that we will come "close" to targets for individual funds.

Perhaps your point is: my second table is not generalized enough to cover all the possible restrictions that we might want to incorporate in our rebalancing strategy. (There is an myriad of them, really.) Well, I said that myself. I wrote: "a hack for demonstration purposes".

I thought it is more instructive for Jennifer to understand how to prorate the remaining relative targets. And I thought it spoke directly to her question, misguided as it might be, to wit: "how to I allocate the $1,000 among the other 3 funds [....] without [....] iterations?

For example, what happens if New Balance in F13 is $10,200 instead of $11,000?

Oops: outta time! I might address this later, if it is still relevant.
 
Last edited:
Upvote 0
In my earlier comments, I already said that might not or should not be the only goal to look at. I will cover that point in more detail in a follow-up posting later -- probably much later -- if time permits and it is still warranted.
[....]
[In response to Stephen's last question....]
Oops: outta time! I might address this later, if it is still relevant.

No, I will not.

Jennifer, this thread is about your question. I might return to Stephen's questions, but not until after we hear from you. Hopefully, you have not lost sight of my response to you. And I hope it is helpful, if not dispositive.

I'm sorry for such a long distracting response to Stephen. I should have let it go for now. On the other hand, perhaps some part of my response to him might be help to you as well, Jennifer.
 
Upvote 0
All,

Thanks for all of the effort. I'm sorry that my original explanation was not clear. I really tried to be explicit. Let me try again.

I have a portfolio containing 4 mutual funds. I have target allocations for each fund (10-20-30-40). From time to time I have some money to invest. When I do, I want to use that new investment to bring all of the funds as close to in balance as possible by just by distributing the new money to one or more of the funds.

I do not want to sell any of the funds to rebalance. That is a trivial exercise. All that takes is calculating the target allocation for the new balance (Row 7) and then taking the difference from the current allocation (Row 8). If any of the differences are negative (G8), sell those amounts in those funds. Then invest the amounts indicated in the other funds. Here it is for the data I originally posted.

2New Investment$1,000
C/RCDEFGHI
4Funds Fund AFund BFund CFund DSumFormulas
5Target % 10%20%30%40%100%
6Old Balance $700$2,000$2,800$4,500$10,000
7New Balance Target$1,100$2,200$3,300$4,400$11,000=H7*D5
8Difference+$400+$200+$500-$100$1,000=D7-D6

<tbody>
</tbody>

I simply sell $100 from Fund D then invest $400 in Am $200 in B, and $500 in C. All funds are now perfectly in balance.

Coming as close as possible to the target allocations without selling any shares is also a trivial exercise if the current balance in all of the funds is less then the target allocation of the new total. But if any of them would still be over-weighted, then it gets tricky. I was looking for a general solution and was hoping for one that is not iterative.

This started out as a practical exercise as I actually did have money to invest and one of the funds was way over weighted. I ended up dong it manually, but then got to wondering if there might be a soltion I could use in the future.

Is that any clearer? :confused:
 
Upvote 0
I do not want to sell any of the funds to rebalance. [....] I was looking for a general solution and was hoping for one that is not iterative.

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

What you describe is a linear programming problem. I don't believe Goal Seek can be used for that, in general. I believe the Solver LP Simplex method would do the best job. I think that is available in Excel 2010 and later, which I do not have.

Of course, the Solver tool is iterative. But you can bury its iterative nature in a (VBA) user-defined function, just like the IRR iterative calculation is buried in Excel IRR.

However, be wary of using MAX in any formulas that Solver (or Goal Seek) uses directly or indirectly. These methods assume a set of formulas that converge toward a solution in a "continuous" manner. MAX and other similar functions create "discontinuities"; that is, changes in inputs might not change outputs, or the changes might not converge in the way that Solver expects.

Sometimes, we get lucky, and Solver does not discover the discontinuities. But that is a not a general solution. Instead, you build those constraints with Solver options.

Ciao!
 
Upvote 0
Jennifer, thanks for clarifying. I think my comments in Post #2 stand:

- My Goal Seek method provides the general solution for the "no sell" constraint.

- It could be done with closed formula(e), but it would be messy to take account of the various possibilities. If I went this route, I'd be inclined to build it up with lots of helper rows.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,205
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top