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.

 

[NdNoviceHlp]

Haven't determined what to do with additional trades. Messing around with the least trades thing. Trial this. It is for the Tax Deferred accts only. Be aware that it clears F7:H27 and uses G25:H27. "G" is the amount remaining untraded from each acct and "H" is the percent untraded. It allocates from the acct with the largest total to the largest asset amount, deducts that amount from that acct and repeats until all trades completed. I don't like the end results with different accounts having different percent untraded in "H". Dave

Sub FillF2()
Dim LargeTemp2 As Double, Cnt2 As Integer
Dim LargeTemp As Double, Cnt As Integer, TotDef As Double
'this stuff for test only
Sheets("Sheet1").Range("F7:H27").ClearContents
For Cnt2 = 25 To 27 'transfer acct total to "G"
Sheets("Sheet1").Range("G" & Cnt2).Value = Sheets("Sheet1").Range("C" & Cnt2).Value
Next Cnt2

'net tax deferred amount
TotDef = Application.WorksheetFunction.SumIf(Worksheets("Sheet1").Range("C7:C22"), _
    Worksheets("Sheet2").Range("A31"), Worksheets("Sheet1").Range("E7:E22"))
    
Do While TotDef > 0
LargeTemp2 = 0

For Cnt2 = 25 To 27
If Sheets("Sheet1").Range("B" & Cnt2).Value = "Tax Deferred" Then
If LargeTemp2 < Sheets("Sheet1").Range("G" & Cnt2).Value Then
LargeTemp2 = Sheets("Sheet1").Range("G" & Cnt2).Value
Cnt2temp = Cnt2
End If
End If
Next Cnt2

LargeTemp = 0
For Cnt = 7 To 22 'loop "C"
If Sheets("Sheet1").Range("F" & Cnt).Value = "" And _
Sheets("Sheet1").Range("C" & Cnt).Value = "Tax Deferred" Then
If LargeTemp < Sheets("Sheet1").Range("E" & Cnt).Value And _
LargeTemp2 > LargeTemp Then
LargeTemp = Sheets("Sheet1").Range("E" & Cnt).Value
Cnttemp = Cnt
End If
End If
Next Cnt

Sheets("Sheet1").Range("F" & Cnttemp).Value = Sheets("Sheet1").Range("A" & Cnt2temp).Value
Sheets("Sheet1").Range("G" & Cnt2temp).Value = LargeTemp2 - LargeTemp
TotDef = TotDef - LargeTemp
Loop
'for test only
' "G" has untraded amount "H" has % of total account amount
For Cnt2 = 25 To 27
Sheets("Sheet1").Range("H" & Cnt2).Value = Sheets("Sheet1").Range("G" & Cnt2).Value _
 / Sheets("Sheet1").Range("C" & Cnt2).Value * 100
Next Cnt2

End Sub

 

[luckyajr]

Thanks again for your hard work with this. Forgive me for being so ignorant when it comes to the coding, but I'm not so sure that I know what to do with the code you gave me. When I ran the code, it kind of "froze up" my spreadsheet and I have to restart it. Can you help me with this? Literally, I have zero experience with Excel (let alone coding) prior to this spreadsheet. Thanks.

 

[NdNoviceHlp]

Place the code in sheet1 sheet code (alt+F11, right click sheet1, and select view code. Paste the code) Run the macro. The code works for the ws you described. It sounds like the Do loop never ended re. freeze. Trial changing this...

 Do While TotDef > 0

to this...

MsgBox TotDef
Do While TotDef > 0
MsgBox TotDef

Dave

 

[luckyajr]

Still not working from what I can tell. I changed the code as you described. The message box will pop up and after hitting 'ok' several times it fills in the Tax Deferred Accounts. After the last one though it wont close out. Not quite sure what I'm missing if it's working for you, but sorry im not getting it right yet. Look forward to hearing back.

 

[NdNoviceHlp]

The Do loop is ending... I don't get it. Trial this. The last msgbox should be zero. Dave

Sub FillF2()
Dim LargeTemp2 As Double, Cnt2 As Integer
Dim LargeTemp As Double, Cnt As Integer, TotDef As Double
'this stuff for test only
Sheets("Sheet1").Range("F7:H27").ClearContents
For Cnt2 = 25 To 27 'transfer acct total to "G"
Sheets("Sheet1").Range("G" & Cnt2).Value = Sheets("Sheet1").Range("C" & Cnt2).Value
Next Cnt2

'net tax deferred amount
TotDef = Application.WorksheetFunction.SumIf(Worksheets("Sheet1").Range("C7:C22"), _
    Worksheets("Sheet2").Range("A31"), Worksheets("Sheet1").Range("E7:E22"))
MsgBox "Total Deferred" & TotDef
Do While TotDef > 0
'MsgBox TotDef
LargeTemp2 = 0

For Cnt2 = 25 To 27
If Sheets("Sheet1").Range("B" & Cnt2).Value = "Tax Deferred" Then
If LargeTemp2 < Sheets("Sheet1").Range("G" & Cnt2).Value Then
LargeTemp2 = Sheets("Sheet1").Range("G" & Cnt2).Value
Cnt2temp = Cnt2
End If
End If
Next Cnt2

LargeTemp = 0
For Cnt = 7 To 22 'loop "C"
If Sheets("Sheet1").Range("F" & Cnt).Value = "" And _
Sheets("Sheet1").Range("C" & Cnt).Value = "Tax Deferred" Then
If LargeTemp < Sheets("Sheet1").Range("E" & Cnt).Value And _
LargeTemp2 >= LargeTemp Then
LargeTemp = Sheets("Sheet1").Range("E" & Cnt).Value
Cnttemp = Cnt
End If
End If
Next Cnt

Sheets("Sheet1").Range("F" & Cnttemp).Value = Sheets("Sheet1").Range("A" & Cnt2temp).Value
Sheets("Sheet1").Range("G" & Cnt2temp).Value = LargeTemp2 - LargeTemp
TotDef = TotDef - LargeTemp
MsgBox TotDef
Loop
'for test only
' "G" has untraded amount "H" has % of total account amount
For Cnt2 = 25 To 27
Sheets("Sheet1").Range("H" & Cnt2).Value = Sheets("Sheet1").Range("G" & Cnt2).Value _
 / Sheets("Sheet1").Range("C" & Cnt2).Value * 100
Next Cnt2

End Sub

 

[luckyajr]

I think I know what the issue is now. You are correct, in that the last message box will display zero under the given account balances that I referenced in previous posts (i.e. when A25 = $355,000, A26 = $425,000, and A27 = $157,000). I used rounded amounts here for purposes of simplicity, however in real life situations most account balances will not be that smooth. For instance, in real life there will be account balances such as "$355,245.76" as opposed to just $355,000. When I input more specific account balances in the spreadsheet, that is when I do not get the zero for the last box.
In addition, after spending more time with the spreadsheet and thinking more about it, it may make more sense to deal with more specific numbers and percentages instead of dealing with rounded numbers. For instance, C2 and C3 formulas use the ROUND function, and E7:E22 rounds off the decimal (cents) as well. I am thinking now that it would be more efficient to have at least 2 decimal places (and cents) for the cells instead of rounding or formatting with no decimal places.
What do you think? Thanks!

 

[NdNoviceHlp]

That's different. Change this line of code...

Do While TotDef > 1

I think you should get rid of all the rounds. Dave

 

[luckyajr]

K now we are making progress. Appreciate the work! Couple of questions for you:
1. Do you have any ideas on what to do with the multiple trades?
2. How will the taxable portion be accounted for?
3. It seems as though this is only coded as if there will be 3 accounts, when realistically there could be anywhere from 2 to 6 accounts (between A25:A30), and any combination of Taxable vs. Tax Deferred. So, one household may have only 2 Tax Deferred accounts while another household may have 1 Tax Deferred and 4 Taxable accounts, and another could have 3 Tax Deferred and 3 Taxable, and so on.
Thanks again!

 

[NdNoviceHlp]

Did you look at G25:H27? What about the remainder in G25:G26? (The E7:E22 are net values and C25:C27 are gross values.) What about the % values in H25:H27? It indicates that the accounts weren't traded evenly. I think that having the total net values for C25:C27 would resolve both of these issues (or having the gross values for E7:E22). How did you determine the net values? This code is only to nail down the logic. The taxable trades and more accounts is easily managed. The multi trade thing might be abit more difficult but the logic comes first. Dave

 

[luckyajr]

Perhaps we can assign another column as "Amount to be Allocated" or something like that to where we have a "gross" amount for each asset class fund, and then we can have another column showing the exact amount to place the trade to. Or we could show the gross amount on Sheet2! or something like that (as it may not be relevant for someone to need to see it, but rather just used for the code).
It is difficult to represent the account values (C25:C30) as "net" numbers, because we do not know how many trades will be placed in the account yet.
Essentially, it is easiest to think of each transaction costing $35 (or $35 per trade). The exception to this is row 22 which does not incur a transaction charge. And please note that if there are multiple trades for an asset class, then that will incur multiple $35 trades. So it can't be viewed as "each asset class will have one $35 charge", it has to be viewed as "each trade placed (with the exception of row 22) will have a $35 transaction charge.
My thinking is not to show only a "gross" trade amount column, because someone using the spreadsheet then would be inputing the wrong trade amount into the brokerage platform, which could result in overtrading the amount of money and leaving a debit balance in the account (due to the unaccounted for $35 transaction charge).
Again, I want this spreadsheet to be a step-by-step calculation for allocating a portfolio, where virtually all of the guesswork is taken out of it.
And there is a residual balance for the Tax Deferred amounts in G25:H27 probably because there will be a "Tax Deferred" trade in the "Allocate to Both*" Asset Class, which has not been accounted for yet. So, once that is accounted for (and we we figure out the "net" numbers and multiple trade situation) it should balance itself out.
Also remember that the "multi trade" issue could reflect multiple accounts with the same "tax classification" (Taxable/Tax Deferred) as opposed to just the "Allocate to Both*" issue. So even though an asset class is assigned to either Taxable or Tax Deferred, does not mean that the result will only be one account (and thus one trade).