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]

How did you determine the net values in E7:E22? My concern is that the values E7:E22 do not add up to the total of C25:C27. Where's the missing money? The $35 trade fee and total number of trades and total trade fee for each account can be fairly easily included in the code. Dave

 

[luckyajr]

Because I currently have the spreadsheet set up to calculate just one trade per asset class, the formula can be shown as follows (note the minus $35 to represent the net amount).
Also please note that if the asset class is "Allocate to Both*" then the E Cell calculates only the "Taxable" portion of the trade, which is represented on Sheet2!. So since column E is currently only set up for one trade per asset class, then simply subtracting $35 from the trade amount will yield a net trade amount. We will need to reflect "the Additional Trades" somewhere else though to balance everything out.
HOWEVER, I am aware that in a case where a multi account trade will need to take place in the SAME tax classification (for instance when the asset class is assigned to "Tax Deferred" but 2 Tax Deferred accounts will need to be used to allocate that asset class.) then it is not currently formulated to reflect this. So I may need some guidance on this as well.

For Cell E7:
=IF(C7="Allocate to Both*",(C2*Sheet1!$B$1)-35,(A7*$B$1)-35)

For Cell E8:
=IF(C8="Allocate to Both*",(Sheet2!$L$18*Sheet1!$B$1)-35,(A8*$B$1)-35)
...(Copied down through Cell E21)

For Cell E22:
=A22*B1
...(For this cell there is no transaction charge)

 

[NdNoviceHlp]

What's in sheet2!L18? Anyways hardcoding the trade fee into your fomulas probably isn't that good in case the fee changes. The formulas also show me why things weren't adding up. This code erases your formulas in E7:E22 so you probably want to trial this in a wb copy. Place the trade fee (35) into B5. As can be seen, I don't know what to do with the allocate to both but the amount of Taxable and Tax deferred to be allocated into the allocate to both is known. Dave

Sub FillF3()
Dim LargeTemp2 As Double, Cnt2 As Integer, Cnt3 As Integer
Dim LargeTemp As Double, Cnt As Integer, TotDef As Double
Dim TotTax As Double
'fee in Sheets("Sheet1").Range("b" & 5).Value
'"G" has current balance of untraded amount
'"H" has untraded % of total account amount
'"I" has total trade fee
' "J" has # of trades

'clear previous results
'*******Caution clears E7:j27
Sheets("Sheet1").Range("E7:j27").ClearContents
'transfer acct total to "G" for current balance
For Cnt2 = 25 To 27
Sheets("Sheet1").Range("G" & Cnt2).Value = Sheets("Sheet1").Range("C" & Cnt2).Value
Next Cnt2
'gross amts
For Cnt3 = 7 To 22
Sheets("Sheet1").Range("E" & Cnt3).Value = Sheets("Sheet1").Range("A" & Cnt3).Value * _
                                            Sheets("Sheet1").Range("B" & 1).Value
Next Cnt3
'gross tot deferred
TotDef = Application.WorksheetFunction.SumIf(Worksheets("Sheet1").Range("C7:C22"), _
         Worksheets("Sheet2").Range("A31"), Worksheets("Sheet1").Range("e7:e22"))

MsgBox "Total Deferred " & TotDef
'Tax Deferred
Do While TotDef > 1
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
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
'insert acct #
Sheets("Sheet1").Range("F" & Cnttemp).Value = Sheets("Sheet1").Range("A" & Cnt2temp).Value
'total fee (fee amt in Sheets("Sheet1").Range("b" & 5).Value)
If Cnttemp <> 22 Then 'no fee for #22
Sheets("Sheet1").Range("I" & Cnt2temp).Value = _
Sheets("Sheet1").Range("I" & Cnt2temp).Value + Sheets("Sheet1").Range("b" & 5).Value
'net trade amt
Sheets("Sheet1").Range("e" & Cnttemp).Value = Sheets("Sheet1").Range("e" & Cnttemp).Value - _
                                               Sheets("Sheet1").Range("b" & 5).Value
End If
'current balnce
Sheets("Sheet1").Range("G" & Cnt2temp).Value = LargeTemp2 - LargeTemp
'trade cnter
Sheets("Sheet1").Range("j" & Cnt2temp).Value = _
Sheets("Sheet1").Range("j" & Cnt2temp).Value + 1
TotDef = TotDef - LargeTemp
MsgBox TotDef
Loop

' Taxable
TotTax = Application.WorksheetFunction.SumIf(Worksheets("Sheet1").Range("C7:C22"), _
    Worksheets("Sheet2").Range("A30"), Worksheets("Sheet1").Range("e7:e22"))
MsgBox "Total Taxable " & TotTax
Do While TotTax > 1
LargeTemp2 = 0
For Cnt2 = 25 To 27
If Sheets("Sheet1").Range("B" & Cnt2).Value = "Taxable" 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
If Sheets("Sheet1").Range("F" & Cnt).Value = "" And _
Sheets("Sheet1").Range("C" & Cnt).Value = "Taxable" 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
'insert acct #
Sheets("Sheet1").Range("F" & Cnttemp).Value = Sheets("Sheet1").Range("A" & Cnt2temp).Value
'total fee (fee amt in Sheets("Sheet1").Range("b" & 5).Value)
If Cnttemp <> 22 Then 'no fee for #22
Sheets("Sheet1").Range("I" & Cnt2temp).Value = _
Sheets("Sheet1").Range("I" & Cnt2temp).Value + Sheets("Sheet1").Range("b" & 5).Value
'net trade amt
Sheets("Sheet1").Range("e" & Cnttemp).Value = Sheets("Sheet1").Range("e" & Cnttemp).Value - _
                                                Sheets("Sheet1").Range("b" & 5).Value
End If
'current balnce
Sheets("Sheet1").Range("G" & Cnt2temp).Value = LargeTemp2 - LargeTemp
'trade cnt
Sheets("Sheet1").Range("j" & Cnt2temp).Value = _
Sheets("Sheet1").Range("j" & Cnt2temp).Value + 1
TotTax = TotTax - LargeTemp
MsgBox TotTax
Loop

'for test only
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

As far as having more accts is there anything below A25 other than accts?

 

[luckyajr]

Excellent! I'm liking how this is working. And even though I'm not anticipating much change in the trade fee, I completely agree with you it shouldn't be hardcoded.

sheet2!L18 displays the "Taxable" percentage that is left for the "Allocate to Both*" asset class.

The formula for the cell is:
=MIN(IF(M20:M34>0,M20:M34))

where M20 is...
=Sheet1!C2-Sheet1!A7

Formula is NOT copied down, but rather as follows for each cell:

M21:
=Sheet1!C2-(Sheet1!A7+Sheet1!A8)

M22:
=Sheet1!C2-(Sheet1!A7+Sheet1!A8+Sheet1!A9)

M23:
=Sheet1!C2-(Sheet1!A7+Sheet1!A8+Sheet1!A9+Sheet1!A12+Sheet1!A13+Sheet1!A14+Sheet1!A15+Sheet1!A16+Sheet1!A17+Sheet1!A10)

M24:
=Sheet1!C2-(Sheet1!A7+Sheet1!A8+Sheet1!A9+Sheet1!A12+Sheet1!A13+Sheet1!A14+Sheet1!A15+Sheet1!A16+Sheet1!A17+Sheet1!A10+Sheet1!A11)

M25:
=Sheet1!C2-(Sheet1!A7+Sheet1!A8+Sheet1!A9+Sheet1!A12)

M26:
=Sheet1!C2-(Sheet1!A7+Sheet1!A8+Sheet1!A9+Sheet1!A12+Sheet1!A13)

M27:
=Sheet1!C2-(Sheet1!A7+Sheet1!A8+Sheet1!A9+Sheet1!A12+Sheet1!A13+Sheet1!A14)

M28:
=Sheet1!C2-(Sheet1!A7+Sheet1!A8+Sheet1!A9+Sheet1!A12+Sheet1!A13+Sheet1!A14+Sheet1!A15)

M29:
=Sheet1!C2-(Sheet1!A7+Sheet1!A8+Sheet1!A9+Sheet1!A12+Sheet1!A13+Sheet1!A14+Sheet1!A15+Sheet1!A16)
M30:
=Sheet1!C2-(Sheet1!A7+Sheet1!A8+Sheet1!A9+Sheet1!A12+Sheet1!A13+Sheet1!A14+Sheet1!A15+Sheet1!A16+Sheet1!A17)
M31:
=Sheet1!C2-(Sheet1!A7+Sheet1!A8+Sheet1!A9+Sheet1!A10+Sheet1!A11+Sheet1!A12+Sheet1!A13+Sheet1!A14+Sheet1!A15+Sheet1!A16+Sheet1!A17+Sheet1!A21+Sheet1!A18)

M32:
=Sheet1!C2-(Sheet1!A7+Sheet1!A8+Sheet1!A9+Sheet1!A10+Sheet1!A11+Sheet1!A12+Sheet1!A13+Sheet1!A14+Sheet1!A15+Sheet1!A16+Sheet1!A17+Sheet1!A21+Sheet1!A18+Sheet1!A19)
M33:
=Sheet1!C2-(Sheet1!A7+Sheet1!A8+Sheet1!A9+Sheet1!A10+Sheet1!A11+Sheet1!A12+Sheet1!A13+Sheet1!A14+Sheet1!A15+Sheet1!A16+Sheet1!A17+Sheet1!A18+Sheet1!A19+Sheet1!A20+Sheet1!A21)

M34:
=Sheet1!C2-(Sheet1!A7+Sheet1!A8+Sheet1!A9+Sheet1!A10+Sheet1!A11+Sheet1!A12+Sheet1!A13+Sheet1!A14+Sheet1!A15+Sheet1!A16+Sheet1!A21)

Another side note, I actually have something in cell B5 (its one of the User Inputs that triggers the asset class percentages in A7:A22). So I assigned Sheet2!A1 as the trade fee (and I think I changed the coding to match, but not 100% sure on that).

Nothing necessarily is below A25 other than accounts. I would like to be able to fit everything on one page for purposes of aesthetics and design and the way I have the page set up now I don't have a whole lot of room to move further down. I will have some "footer" notes, but nothing below the accounts other than that.

I'm still open to ideas on "Allocate to Both*", as I feel you are much more experienced and talented in this region than I am.

THANKS! Look forward to hearing back.

 

[NdNoviceHlp]

With the last code provided, E12 (28110) contains the gross amount for allocate to both. G25(4562) and G26(7098) contain the amount of Tax Deferred within the allocate to both (sum of 11660). G27(16450) contains the Taxable amount for allocate to both. If you sum the total for Tax deferred (11660) and Taxable (16450) then you get the E12 (28110) value. This indicates that there would be 3 trades left (1 trade to clear each acct). All of those formulas don't seem necessary because there is no other way to distribute the accts based on the initial input. Your thoughts? Dave

 

[luckyajr]

I'm confused on what formulas you are referring to as being unnecessary. I still feel as though what you are doing with the coding is on the right track, so I'm hoping you are not referring to your coding as being unnecessary. I probably have done a TON of formulas that were not necessary, which is probably what you were referring to.

It seems as though we can integrate the leftover values (G25 and moving down) as an "Additional Trades" section (or a reference to an Additional Trades section). Applying a "trade fee" to the values would essentially yield the amount to input into the trading platform (in a real world scenario), and thus if designed efficiently, could be easy to understand, and would balance out (allocate the portfolio properly).
I always knew that I would be dealing with this "multiple trade" issue, I just wasn't sure how to go about doing it.
I don't mind that the total (aggregate) of the "Allocate to Both*" trade amount is referenced in the respective E7:E21 area, we just need to make sure that the spreadsheet will more specifically detail which account(s) to use, the net trade amount for each account, and the specific fund to use for the asset class.
The way it is currently formulated, D7:D22 calculates and displays the specific fund to use (ticker symbol) based upon the tax classification for the asset class (Taxable or Tax Deferred). And in some (not all, but some) asset classes, the ticker symbol will change based upon that Tax Classification (whether Taxable or Tax Deferred). So, in an "Allocate to Both*" situation, the respecitve D7:D21 cell is only formulated to display the "Taxable" ticker symbol to trade (or if just one possible ticker symbol, then that one is displayed).
This is why I also formulated the "Trade Amount" (E7:E22) to calculate only the "Taxable" portion of the "Allocate to Both*" situation, and then I was going to reference a separate area for the additional trades (at least that was my initial thought).
Now that I see the avenue you have taken with the coding, I like how you are structuring things. I think it is better to have the total "Allocate to Both*" trade amount in E7:E21 rather than just the "Taxable" amount.

SO, NOW ON WHAT TO DO FROM HERE??
I have some thoughts. Perhaps for the respective "Allocate to Both*" section, for the respective D7:D21, instead of formulating it to display the "Taxable" Ticker Symbol, we can have it say something in 'text' like "See Additional Trade Section" (but something more precise). And do the same or something similiar for the respective cell in F7:F22 (the account numbers to use).

Again, I'm just brainstorming here, but am curious on your thoughts. We're getting closer, THANKS AS ALWAYS!!

 

[NdNoviceHlp]

I was referring to the formulas which "displays the "Taxable" percentage that is left for the "Allocate to Both*" asset class" ...don't we know the actual values to be allocated for both Taxable and Tax deferred? Please see my last post... am I correct with my assumptions?
Is there anything in G7:G22 (or H,I etc)? Would formating multitrades in "F" like.... 111(net amt), 222(net amt), etc. work? OR...in "F"...111(net amt); "G"...222(net amt); etc work?
Is there always an allocate to both acct. Can there be more than one allocate to both accts? The plan is to have the allocate to both value in "E" as the net value in the end. Dave

 

[luckyajr]

Great questions again.

There is nothing in the 7:22 spots of columns G and on.

I am still open to your thoughts on this, but If we use the columns in G, H, etc. we are still going to need to display the specific fund (ticker symbols) somewhere else other than column D because that would be confusing on which ticker applied to which account. Does that make sense?

And after seeing how the outputs with work to display the remaining balances left in G25:27 (via your codes) I agree that it could be more efficient to not have just the "Taxable" portion shown (as I previously had), but rather the total aggregate amount for the "allocate to both*" and then a separate area for the additional trades.
There may not necessarily be an "allocate to both*" in every case, but if there is one, there will only be one; not more than one.
In cases where the "Taxable" amount can be evenly distributed to the asset classes without a "carryover" then that will not trigger an "allocate to both*".
Using the percentages shown in A7:A22, if the taxable amount was exactly 12% of the portfolio then there would be no "carryover" into the next asset class (It would be allocated to A7 and A8 at 6% each) Therefore, the next trade would come from a "Tax Deferred" account, leaving no Allocate to Both*" situation. Hopefully this logic makes sense.
Looking forward to hearing back.

 

[NdNoviceHlp]

Trial this. It's hard to test without real data but seems to work without the allocate to both account. The ticker symbols could be included with the multitrade info ie. acct#(net amt:ticker symbol). Don't know where or what the tickers are. Dave

 Sub FillF4()
Dim LargeTemp2 As Double, Cnt2 As Integer, Cnt3 As Integer
Dim LargeTemp As Double, Cnt As Integer, Temptot As Double
Dim TotTax As Double, Lastrow As Integer, Counter As Integer
Dim Cnt2Temp As Integer, CntTemp As Integer, RemTot As Double
Dim RemGrs As Double, Cnt4 As Integer, CellCnt As Integer
Dim Cnt5 As Integer
'fee in Sheets("Sheet2").Range("A" & 1).Value
'"G" has current balance of untraded amount
'"I" has total trade fee
' "J" has # of trades

'include all accts
With Sheets("Sheet1")
Lastrow = .Range("A" & .Rows.Count).End(xlUp).Row
End With
'clear previous results
'*******Caution clears E7:j22 and G25:J & lastrow
Sheets("Sheet1").Range("G25:J" & Lastrow).ClearContents
Sheets("Sheet1").Range("E7:J22").ClearContents

'transfer acct total to "G" for current balance
For Cnt2 = 25 To Lastrow
Sheets("Sheet1").Range("G" & Cnt2).Value = Sheets("Sheet1").Range("C" & Cnt2).Value
Next Cnt2
'gross amts
For Cnt3 = 7 To 22
Sheets("Sheet1").Range("E" & Cnt3).Value = Sheets("Sheet1").Range("A" & Cnt3).Value * _
                                            Sheets("Sheet1").Range("B" & 1).Value
Next Cnt3

For Counter = 1 To 2
Select Case Counter
'taxable/tax deferred
Case 1: Temptot = _
Application.WorksheetFunction.SumIf(Worksheets("Sheet1").Range("B25:B" & Lastrow), _
Worksheets("Sheet2").Range("A30"), Worksheets("Sheet1").Range("G25:G" & Lastrow)): _
Case 2: Temptot = _
Application.WorksheetFunction.SumIf(Worksheets("Sheet1").Range("B25:B" & Lastrow), _
Worksheets("Sheet2").Range("A31"), Worksheets("Sheet1").Range("G25:G" & Lastrow))
End Select
Do While Temptot > 1
LargeTemp = 0
LargeTemp2 = 0
If Counter = 1 Then
'current balance taxable
For Cnt2 = 25 To Lastrow
If Sheets("Sheet1").Range("B" & Cnt2).Value = "Taxable" 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
'grs amounts taxable
For Cnt = 7 To 22
If Sheets("Sheet1").Range("F" & Cnt).Value = "" And _
Sheets("Sheet1").Range("C" & Cnt).Value = "Taxable" 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
'current balance tax defer
Else
For Cnt2 = 25 To Lastrow
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
'grs amounts tax defer
For Cnt = 7 To 22
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
End If

If LargeTemp = 0 Or LargeTemp2 - LargeTemp <= 0 Then
Exit Do
End If

'insert acct #
Sheets("Sheet1").Range("F" & CntTemp).Value = Sheets("Sheet1").Range("A" & Cnt2Temp).Value
If CntTemp <> 22 Then 'no fee for #22
'total fee (fee amt in Sheets("Sheet2").Range("A" & 1).Value)
Sheets("Sheet1").Range("I" & Cnt2Temp).Value = _
Sheets("Sheet1").Range("I" & Cnt2Temp).Value + Sheets("Sheet2").Range("A" & 1).Value
'net trade amt
Sheets("Sheet1").Range("e" & CntTemp).Value = Sheets("Sheet1").Range("e" & CntTemp).Value - _
                                               Sheets("Sheet2").Range("A" & 1).Value
End If
'current balnce
Sheets("Sheet1").Range("G" & Cnt2Temp).Value = LargeTemp2 - LargeTemp
'trade cnter
Sheets("Sheet1").Range("j" & Cnt2Temp).Value = _
Sheets("Sheet1").Range("j" & Cnt2Temp).Value + 1
Temptot = Temptot - LargeTemp
MsgBox "View"
Loop
Next Counter

'********multi trade taxable/tax defer
For Counter = 1 To 2
Select Case Counter
Case 1: RemTot = _
Application.WorksheetFunction.SumIf(Worksheets("Sheet1").Range("B25:B" & Lastrow), _
Worksheets("Sheet2").Range("A30"), Worksheets("Sheet1").Range("G25:G" & Lastrow)): _
Case 2: RemTot = _
Application.WorksheetFunction.SumIf(Worksheets("Sheet1").Range("B25:B" & Lastrow), _
Worksheets("Sheet2").Range("A31"), Worksheets("Sheet1").Range("G25:G" & Lastrow))
End Select

Do While RemTot > 1
RemGrs = 0
For Cnt4 = 7 To 22
'tot grs taxable remaining
If Counter = 1 Then
If Sheets("Sheet1").Range("C" & Cnt4).Value = "Taxable" Then
If Sheets("Sheet1").Range("F" & Cnt4).Value = "" Then
RemGrs = Sheets("Sheet1").Range("E" & Cnt4).Value
Exit For
End If
End If
'tot grs tax defer remaining
Else
If Sheets("Sheet1").Range("C" & Cnt4).Value = "Tax Deferred" Then
If Sheets("Sheet1").Range("F" & Cnt4).Value = "" Then
RemGrs = Sheets("Sheet1").Range("E" & Cnt4).Value
Exit For
End If
End If
End If
Next Cnt4

If RemGrs = 0 Then
Exit Do
End If

CellCnt = 6
For Cnt5 = 25 To Lastrow
If Sheets("Sheet1").Range("G" & Cnt5).Value > 0 Then
If Sheets("Sheet1").Range("C" & Cnt4).Value = Sheets("Sheet1").Range("B" & Cnt5).Value Then
If Sheets("Sheet1").Range("G" & Cnt5).Value <= RemGrs Then

If Cnt4 <> 22 Then 'no fee for #22
'total fee
Sheets("Sheet1").Range("I" & Cnt5).Value = _
Sheets("Sheet1").Range("I" & Cnt5).Value + Sheets("Sheet2").Range("A" & 1).Value
If Sheets("Sheet1").Range("e" & Cnt4).Value >= Sheets("Sheet1").Range("G" & Cnt5).Value Then
'net remaining
Sheets("Sheet1").Range("e" & Cnt4).Value = Sheets("Sheet1").Range("e" & Cnt4).Value - _
                                           Sheets("Sheet1").Range("G" & Cnt5).Value
'insert acct #
Sheets("Sheet1").Cells(Cnt4, CellCnt) = Sheets("Sheet1").Range("A" & Cnt5).Value & _
       "(" & Sheets("Sheet1").Range("G" & Cnt5).Value - Sheets("Sheet2").Range("A" & 1).Value & ")"
RemTot = RemTot - Sheets("Sheet1").Range("g" & Cnt5).Value
Sheets("Sheet1").Range("G" & Cnt5).Value = 0
Else
'current balance remaining
Sheets("Sheet1").Range("G" & Cnt5).Value = Sheets("Sheet1").Range("G" & Cnt5).Value - _
                                           Sheets("Sheet1").Range("e" & Cnt4).Value
'insert acct #
Sheets("Sheet1").Cells(Cnt4, CellCnt) = Sheets("Sheet1").Range("A" & Cnt5).Value & _
        "(" & Sheets("Sheet1").Range("e" & Cnt4).Value - Sheets("Sheet2").Range("A" & 1).Value & ")"
RemTot = RemTot - Sheets("Sheet1").Range("e" & Cnt4).Value
Sheets("Sheet1").Range("e" & Cnt4).Value = 0
End If
Else
If Sheets("Sheet1").Range("e" & Cnt4).Value > Sheets("Sheet1").Range("G" & Cnt5).Value Then
'net remaining
Sheets("Sheet1").Range("e" & Cnt4).Value = Sheets("Sheet1").Range("e" & Cnt4).Value - _
                                             Sheets("Sheet1").Range("G" & Cnt5).Value
'insert acct #
Sheets("Sheet1").Cells(Cnt4, CellCnt) = Sheets("Sheet1").Range("A" & Cnt5).Value & _
                                            "(" & Sheets("Sheet1").Range("G" & Cnt5).Value & ")"
RemTot = RemTot - Sheets("Sheet1").Range("G" & Cnt5).Value
Sheets("Sheet1").Range("G" & Cnt5).Value = 0
Else
'current balance remaining
Sheets("Sheet1").Range("G" & Cnt5).Value = Sheets("Sheet1").Range("G" & Cnt5).Value - _
                                           Sheets("Sheet1").Range("e" & Cnt4).Value
'insert acct #
Sheets("Sheet1").Cells(Cnt4, CellCnt) = Sheets("Sheet1").Range("A" & Cnt5).Value & _
                                            "(" & Sheets("Sheet1").Range("e" & Cnt4).Value & ")"
RemTot = RemTot - Sheets("Sheet1").Range("e" & Cnt4).Value
Sheets("Sheet1").Range("e" & Cnt4).Value = 0
End If
End If
CellCnt = CellCnt + 1
MsgBox "View"
'trade cnt
Sheets("Sheet1").Range("j" & Cnt5).Value = _
Sheets("Sheet1").Range("j" & Cnt5).Value + 1
End If
End If
End If
Next Cnt5
If Sheets("Sheet1").Range("e" & Cnt4).Value = 0 Then
Sheets("Sheet1").Range("e" & Cnt4).Value = "MultiTrade"
End If
Loop
Next Counter

'multi allocate to both
For Cnt4 = 7 To 22
If Sheets("Sheet1").Range("F" & Cnt4).Value = "" Then
RemGrs = Sheets("Sheet1").Range("E" & Cnt4).Value
Exit For
End If
Next Cnt4
If Application.WorksheetFunction.Sum(Worksheets("Sheet1").Range("G25:G" & Lastrow)) <= _
                          Sheets("Sheet1").Range("E" & Cnt4).Value Then
CellCnt = 6
For Cnt5 = 25 To Lastrow
'trade cnt
Sheets("Sheet1").Range("j" & Cnt5).Value = _
              Sheets("Sheet1").Range("j" & Cnt5).Value + 1
'net remaining
Sheets("Sheet1").Range("e" & Cnt4).Value = Sheets("Sheet1").Range("e" & Cnt4).Value - _
                                           Sheets("Sheet1").Range("G" & Cnt5).Value
If Cnt4 <> 22 Then 'no fee for #22
'trade fee
Sheets("Sheet1").Range("I" & Cnt5).Value = _
Sheets("Sheet1").Range("I" & Cnt5).Value + Sheets("Sheet2").Range("A" & 1).Value
'insert acct #
Sheets("Sheet1").Cells(Cnt4, CellCnt) = Sheets("Sheet1").Range("A" & Cnt5).Value & _
    "(" & Sheets("Sheet1").Range("G" & Cnt5).Value - Sheets("Sheet2").Range("A" & 1).Value & ")"
Else
'insert acct #
Sheets("Sheet1").Cells(Cnt4, CellCnt) = Sheets("Sheet1").Range("A" & Cnt5).Value & _
                                         "(" & Sheets("Sheet1").Range("G" & Cnt5).Value & ")"
End If
Sheets("Sheet1").Range("G" & Cnt5).Value = 0
CellCnt = CellCnt + 1
MsgBox "View"
Next Cnt5
If Sheets("Sheet1").Range("e" & Cnt4).Value = 0 Then
Sheets("Sheet1").Range("e" & Cnt4).Value = "MultiTrade"
End If
End If
End Sub

 

[luckyajr]

I like the idea of how you are designing everything. I've been trialing the code and seems to be working.
The tickers could be subject to change at some point so I'd like to have them easily editable if possible (perhaps referrencing them in other cells..?). How do you suggest we accomplish the tickers?
Also, can we change the formatting in columns F and on to the right to display the net trade amounts as currency (e.g. $##,###.##) and the account numbers to be formatted as "###-######"? I'm sure you were going to finish formatting everything once the logic was figured out (and once we figured out what to do with the tickers as well), but just wanted to throw that out there.
Now, will the coding work for other combinations of accounts, or is it currently just set up for 2 tax deferred and 1 taxable? Because in real life remember I'd like it to be able to work for any combination of taxable and tax deferred accounts. I trialed one scenario with 5 accounts and it didn't seem to calculate correctly.
I can tell we are extremely close on this. Can't thank you enough for your help. Look forward to hearing back.