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]

Thanks. Still have the issue with some trials where it displays "$0.01" and a negative trade amount ("-35") in the allocate to both row. Any thoughts?

 

[NdNoviceHlp]

Yes there is a fractionation error. E gross values are based on % whereas G values are directly calculated. I suspect that there was 35.01 left in E and in 0 left in G. This leaves .01 in E and -35 in F. Trial adding this directly below the 'multi allocate to both part of the code. Dave

'multi allocate to both
If Application.WorksheetFunction.Sum(Worksheets("Sheet1").Range("G25:G" & Lastrow)) < =_
Sheets("Sheet2").Range("A" & 1).Value Then
Exit Sub
End If

 

[luckyajr]

As always, what you say is very smart and makes sense. Should have realized that myself. Apologies as always for my ignorance, but I don't think im posting the code correctly, as it is giving me a syntax error or something like that. Would you mind sending the whole code sheet over?? Youre a life saver.

 

[NdNoviceHlp]

Whoops! Dave

'multi allocate to both
If Application.WorksheetFunction.Sum(Worksheets("Sheet1").Range("G25:G" & Lastrow)) <= _
Sheets("Sheet2").Range("A" & 1).Value Then
Exit Sub
End If

 

[luckyajr]

Dave, I'm sure I'm missing something due to my ignorance. I didn't delete anything under it, I just simply pasted it directly under the multi allocate to both section. Trialed a few, and still got the "$0.01" issue. Did i paste the code correctly? Thoughts? Thanks.

 

[NdNoviceHlp]

Remove that last trial. Insert this in the allocate to both part following the For Cnt5 = 25 To Lastrow....

For Cnt5 = 25 To Lastrow
If Application.WorksheetFunction.Sum(Worksheets("Sheet1").Range("G25:G" & Lastrow)) = 0 Then
Exit Sub
End If

Does E equal 35.01 before ending in .01? If so there's likely something wrong (ie. it should have ended with .01). Got to go now. Trial some more and report your findings (does it endlessly loop/ wrong calculations, etc.) Try to be specific with inputs and wrong outcomes. Have a nice day. Dave

 

[luckyajr]

Ah man, I am really starting to feel a little stuck with all of this. It's working at times, then other times it's not (ie. "$0.01" is shown). I feel its a little too overwhelming to dictate every scenario that isn't working.
I'm posting your code changes, but they don't seem to be doing anything differently (the same outcome happens).
Aside from the HTML maker you mentioned that I don't think I have access to, is there a way to just send you my spreadsheet or anything like that so you can just make the changes yourself or anything like that? If I'm crossing the line of ettiquete for this site I apologize. I just want to be efficient with time now that it's been a couple weeks of asking you questions and trying to get this to work. I know we are so close, but I think I've made some obvious and simple mistakes that are over my boundaries of knowledge that you would be able to fix. Look forward to hearing back. Thanks

 

[NdNoviceHlp]

We've pm'd and the file will make it much easier. Maybe just give me some input numbers that don't work and I'll try it out. Dave

 

[luckyajr]

pm'd? Trial this and see what you get...

C25 $355122.22 "Tax Deferred"
C26 $425111.67 "Tax Deferred"
C27 $85000.55 "Taxable"
C28 $89000.00 "Taxable"

Allocation Factor: "20" (B5)

Im not getting a "-.01" but I am getting a (-35) for one of the multitrades.

Thanks Dave, look forward to hearing back

 

[NdNoviceHlp]

Replace the allocate to both code with this. Dave

'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

CellCnt = 6
For Cnt5 = 25 To Lastrow
If Sheets("Sheet1").Range("G" & Cnt5).Value <> 0 Then
'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"
End If
Next Cnt5
If Sheets("Sheet1").Range("e" & Cnt4).Value = 0 Then
Sheets("Sheet1").Range("e" & Cnt4).Value = "MultiTrade"
End If