Investment Model Allocation based upon Percentage and Dollar Amounts

[luckyajr]

Hi,

I am working on a project in which I need to create an investment allocation spreadsheet. The idea is that there can be anywhere from 2 to 6 investment accounts, each with varying dollar amounts. Each account is then then defined as either a "taxable" or "tax deferred" account. From there, I want to apply those accounts to an investment model with targeted asset class percentages (e.g. 8% to Large Cap, 12% to Emerging Markets, etc. totalling to 100%). Each asset class will either be allocated to a "taxable" or a "tax deferred" account(s), and one asset class will be allocated to both registrations (due to percentage differences). Next, based upon the asset class percentage and the total portfolio amount, the trade amount to be placed for the asset class is then formulated.

This next part is where I am stuck. I want to create a formula (or macro) that will dictate which particular account(s) the asset class should be traded to, where everything ends up allocated to the investment model.

So, as an example, there could be a scenario where I have 3 accounts (1 "taxable" and 2 "tax deferred" accounts) that will all be considered "one bucket of money" and I want to allocate those accounts to the investment model which is based upon different asset classes. Each asset class has a defined $ trade amount and is also assigned to an account registration ("taxable"/"tax deferred" or "both").

I'm not sure if I explained this well or not. Apologies if I didn't. Thank you in advance, I sincerely appreciate it.

 

[luckyajr]

Yes, C12 should be "Allocate to Both*". I'm not sure how to answer the formula issue with C7:C21 because it is working for me. C7 is a slightly different formula than the others so that it doesn't return an error if the "Taxable" percentage is equal to the asset class percentage.

I tried to implement a formula that would not result in a "something else"; meaning that the only possible outcome would result in "Taxable", "Tax Deferred", or "Allocate to Both*". I have tested and tested, and it holds true in this sense. Again, I am extremely novice at Excel, so I may be wrong.

I may have an error in formatting, but I am missing the relevance of further dividing by 100, as I feel I have already set it up to display the respective value in percentage form.

Please advise, Thanks as always.

 

[NdNoviceHlp]

Apparently I previously failed to see the significance of the % cell formats. All is well now except this returns False to C7:C22 (because no result is returned IF the AND is False)
IF(AND(Sheet2!$L$17>0,Sheet2!N25=Sheet1!$C$2),"Allocate to Both*")
Maybe there's no chance of False and if so why bothering evaluating it? Trial putting any other number than 17 in Sheet2!N25 you get False with your code or "something else" with my adjustments (less the /100 part).
Is there only three options: Taxable; Tax Deffered or Allocate to Both?
I'm going to re-read your requirements and get started with a solution. Just want to make sure the guts are right first. Dave

 

[luckyajr]

Dave,

Yes, the idea is that there are only three options (Taxable, Tax Deferred, and Allocate to Both) and essentially only one of the responses between C7:C21 will be "Allocate to Both*". I think I have the formula created efficient enough to where that will literally be the only options to display in C7:C21, but then again I could be wrong.
I am not sure why you are not getting the same results as I am. One thought I can think of is to pay attention to the order in which the "taxable" accounts are allocated to first (I mentioned this earlier as a "priority ranking").
I can explain this further if need be, but for sake of real estate on this thread I will hold off unless I am instructed otherwise.
I feel I detailed my layout pretty explicitly if you review it again. Let me know how else I can help you. Can't thank you enough!!

 

[NdNoviceHlp]

OK if you're happy with that formula we'll move on. What happens to the allocate to both amounts. For eg trial these...
total F1=SUM(E7:E22)
tax F2=SUMIF($C$7:$C$22,Sheet2!A30,$E$7:$E$22)
tax defer F3=SUMIF($C$7:$C$22,Sheet2!A31,E7:E22)
tax % G2=ROUND(F2/F1,2)
tax defer % G3=ROUND(F3/F1,2)
The amounts in E7:E22 don't sum to your total amount (B1)?
The taxable amounts in E7:E22 don't sum to B2?
The tax deferred amounts E7:E22 don't sum to B3?
I'm guessing that the values in B2 and B3 include the amount from the allocate to both however I really don't understand why the sum of E7:E22 doesn't equal B1? Am I right thinking that acct # 111 would consist of .3776 times all the values in E7:E22? Dave

 

[NdNoviceHlp]

Trial this and see if it's close to what you want. It doesn't do anything with the allocate to both. Dave

Sub FillF()
Dim STR As String, Cnt As Integer, Cnt2 As Integer

For Cnt = 7 To 22 'loop "C"
STR = vbNullString
If Sheets("Sheet1").Range("C" & Cnt).Value = "Tax Deferred" Then

For Cnt2 = 25 To 27 'Loop "B"
If Sheets("Sheet1").Range("B" & Cnt2).Value = "Tax Deferred" Then
STR = STR & Sheets("Sheet1").Range("A" & Cnt2).Value & "(" _
& Format(Sheets("Sheet1").Range("D" & Cnt2).Value * _
Sheets("Sheet1").Range("E" & Cnt).Value, "###0") & ")"
End If
Next Cnt2
Sheets("Sheet1").Range("F" & Cnt).Value = STR

Else
If Sheets("Sheet1").Range("C" & Cnt).Value = "Taxable" Then
For Cnt2 = 25 To 27 'loop "B"
If Sheets("Sheet1").Range("B" & Cnt2).Value = "Taxable" Then
STR = STR & Sheets("Sheet1").Range("A" & Cnt2).Value & "(" _
& Format(Sheets("Sheet1").Range("D" & Cnt2).Value * _
Sheets("Sheet1").Range("E" & Cnt).Value, "###0") & ")"
End If
Next Cnt2
End If
Sheets("Sheet1").Range("F" & Cnt).Value = STR
End If

Next Cnt
End Sub

 

[luckyajr]

Apologies for what appears to be a late reply to you. I thought I had replied before the weekend, but I am not seeing it in the thread.
Anyway, the code you provided is definitely pretty close. But still not exactly what I am looking for. Thanks for staying with me on this.

It looks like its currently coded to distribute an equal portion to all of the Tax Deferred accounts evenly, as opposed to seeking to minimize the total trades they are being distributed to.
Let's go back to the example so I can be clear.
In the example we have 3 accounts, 2 Tax Deferred, 1 Taxable, as follows:
C25 has $355,000 (Tax Deferred)
C26 has $425,000 (Tax Deferred)
C27 has $157,000 (Taxable)
For the asset classes that are assigned to be allocated to the Tax Deferred accounts, the code currently makes it so that the asset class gets a portion of one Tax Deferred account, and a portion of the other.
For instance, in row 10, the code displays that the $28,075 trade value should be allocated to A25 (at $12,778) and A26 ($15,297), as opposed to just one of the accounts at the full $28,075. Remember, one of the goals is to minimize the amount of trades.
In addition, this is just an example of the many different combinations of accounts that could be used for this spreadsheet. When testing the code, it looks as though that if I add another "Taxable" account, it does not even count the new "Taxable" account for allocation.
Thanks!

 

[NdNoviceHlp]

Please refer to thread # 14... "The amounts in E7:E22 don't sum to your total amount (B1)?" etc. This stuff seems important. If you add more accounts, you need to update this line of code.... For Cnt2 = 25 To 27 My understanding was that you had 2 pools of cash (taxable and tax deferred) in 3 accts (C25 to C27). E7 to E22 lists the breakdown of the amounts allocated to each asset class.
In the code I posted, F7 to F22 indicates the weighted amount from each pool of cash that comprises the total value of the assett. You have already allocated/traded the asset and I thought you wanted to display which pools of cash (accts) the amount allocated came from. For the tax deferred example, do you want to simply go down the list (E7 to E22) and allocate acct# A25 for all tax deferred assetts in C7 to C22 until you get to 355K then change accts to # A26 for the remainder? Dave

 

[luckyajr]

Ah yes, I forgot to respond to thread #14. You are correct in that the totals do not add up to the total shown in B1. This is due to two different reasons:
1. My trade amount formula includes a discount for the "transaction charge" to place the trade. Therefore, the trade amount for each asset class (C7:C22) is a "net" trade amount.
2. The "Allocate to Both*" asset class only accounts for the "Taxable" portion of the "trade amount". I was planning to designate other cells as "additional trades" which would include the Tax Deferred "Allocate to Both*" portion. That is another reason that the totals do not add up to B1.

The reason I only formulated the "Allocate to Both*" trade amount to reflect the "Taxable" portion in C7:C21 is because it is easier to read through the spreadsheet when just one trade, one fund symbol, one account, etc. for each asset class is shown. I didn't want to "list" multiple outputs in a single cell, because then it could be difficult to place respect to each parameter.

If it's possible to code it so that changes don't have to be made to the code each time the spreadsheet is used (ie. when accounts change), that would be ideal. The whole purpose of this spreadsheet is to minimize the efforts that go into allocating a portfolio. And someone who is a novice at Excel, let alone coding, wouldn't want that burden of recoding, even as minimal as the changing of code is.
Just for clarity, which will hopefully dictate a better understanding of the logic behind the spreadsheet, I want to review a couple things.

In the previous post you wrote, "In the code I posted, F7 to F22 indicates the weighted amount from each pool of cash that comprises the total value of the assett. You have already allocated/traded the asset and I thought you wanted to display which pools of cash (accts) the amount allocated came from."

The logic is slightly different. Picture this example as a real-world situation. It is not that I have already allocated the asset classes prior to figuring out the trade amount, it is that I will allocate the portfolio ONCE the spreadsheet dictates which accounts to place the trades to. I'm hoping this makes sense. So the sequence is as follows:

-Once the USER INPUTS are placed, the spreadsheet lets me know the optimal structure of which asset classes to designate the "Taxable" portion to as well as the "Tax Deferred" portion (C7:C21). I still have not actually allocated anything yet.
-Now that I know how the asset classes are divided up, I want to know which specific funds will be used to "buy into" the asset class (I did not discuss this section much because it is not relevante to coding). Again, no trades have officiall been placed yet. This is displayed in D7:D22.
-E7:E22 displays the "NET" trade amount for each fund (D7:D22) that needs to be placed to purchase the correct weight for the asset class. Again, we don't know which specific account numbers to place the trades yet, so no official trades have taken place.
-F7:F22 should display not a weighted amount divided up between all of the "Taxable" accounts relative to the "Taxable" asset classes, etc. but should seek to assign 1 specific account number (A25:A30) for each asset class until all of the trades have been placed. It would be at this point that actual trades would be placed to allocate the portfolio. For it is inside the accounts themselves that the trading takes place.

You also posted...
"For the tax deferred example, do you want to simply go down the list (E7 to E22) and allocate acct# A25 for all tax deferred assetts in C7 to C22 until you get to 355K then change accts to # A26 for the remainder?"
In a sense, yes, this is more on the track to what I am wanting to accomplish here. Once we know whether the asset class should contain a "Taxable" or "Tax Deferred" account, I want to optimally distribute the accounts in the most efficient (and transaction minimal) way possible. I may be stretching my wishes here, but it seems like there could be a more efficient way then just moving down the list and finding a tax match (taxable/tax deferred) until that value is completely used up and then move to the next one down the list. Maybe that is efficient, but it seems like this may not ensure the minimal amount of trades possible will occur.
Hope this makes sense. Thanks!

 

[NdNoviceHlp]

Will you need account #'s and gross amounts in F7:F22 (or net amounts)? or just the account and/or number... what about the transistion when 1 account is empty and you start another... how to display a 2 account trade? What is the pecking order of the accounts if they are not evenly weighted in each trade? So to achieve the minimal trades would you start with the total amount (ie. Taxable) and then trade the largest to smallest "Taxable" values for E7:E22? Not quite sure of the logic. I'm also guessing that in the real world you want the minimum amount of trading for each seperate account as well? The VBA code can easily be adjusted to accomodate many accounts without the user changing the code. I'm still hoping for a spreadsheet solution in the end. Dave

 

[luckyajr]

Great questions. F7:F22 should just reflect the account numbers (from A25:A30).
Now, for displaying a multi account trade, I am open to suggestions. My thoughts tell me to have a separate section that displays the "Additional Trades". I am thinking this way because each row (from 7:22) reading left to right gives the user instructions on which single trade to place to the asset class to, including the amount, the specific fund, and which tax registration (taxable vs. tax deferred). If F7:F22 displays more than one account, it could then corrupt the instructions from the other columns, for the ticker symbol (D7:D22) for one of the accounts may be different then what is displayed if there is more than one account to trade to. I am open to suggestions however, and if you have something in mind that would read clearly on the spreadsheet, I would love to hear it.
I have been thinking about the best way to sequence the "pecking order" that you described, and I'm still not conclusive. The logic is that once the asset class is assigned to either a "Taxable" or "Tax Deferred" Account, then so long as the account is matched to the correct tax classification it does not matter which account is being used to trade, so long as the minimal amount of trades are being used. And yes, the minimal amount of trading for each separate account as well.
Perhaps starting from largest and moving to smallest is a good start (after matching up the tax classifications first of course). Your thoughts are welcome too. Thanks again!