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]

Welcome to the Board! I sort of like the challenge but... it's likely been done before and seems sort of simplistic. U need the full project laid out B4 U start. The dollar value for each asset class across time is critical to success. Store the actual values and make future predictions based on current values. HTH. Dave

 

[luckyajr]

Thank you for your response! To be honest, however, I am a little confused with your guidance, and I think it is my fault for not clarifying what I would like to accomplish. So let me try to simplify my description.

I am not trying to create a spreadsheet which focuses on investment returns or predictions or anything else like that. I am trying to create a spreadsheet that will work as an allocation calculator for all new households. All of the accounts (ranging from 2 to 6 accounts) for each new client (or household) will be treated as one "bucket" of cash (in order to minimize trading costs) that will be allocated to a predetermined investment model (consisting of different asset classes). Each asset class has been assigned a priority ranking for which asset classes the "taxable" accounts should be allocated to first (if there are taxable accounts).
I will lay out how my spreadsheet currently appears:

USER INPUTS:
A24:A30- User enters the Account Number (may be anywhere from 2 to 6 accounts, so not all cells may necessarily be used here)
B24:B30- User enters whether the account is a "Taxable" or "Tax Deferred" account
C24:C30- User enters the Account Balance

OUTPUTS:
D24:D30 displays the specific account percentage relative to the total percentage of the account tax class (Taxable or Tax Deferred)
E:24:E30 displays the specific account percentage relative to the total percentage of the portfolio (not specific to tax class)
B1 displays the total portfolio dollar amount
B2 displays the total "Taxable" account dollar amount
B3 displays the total "Tax Deferred" account dollar amount
C2 displays the total "Taxable" account percentage weight
C3 displays the total "Tax Deferred" account percentage weight.
A7:A22 displays the asset class percentages (which sum to 100%)
B7:B22 displays the asset class names (US Large, Real Estate, etc.)
C7:C22 displays the calculation of whether the asset class should be allocated to a "taxable" account or a "tax deferred" account or to "both" (due to percentage differences).
D7:D22 displays the specific fund to be bought for the asset class.
E7:E22 displays the calculation of the dollar amount of the trade for the asset class. (if the respective C cell displays "both" then only the "taxable" trade amount is displayed.
(All of the above has been successful.)
I want F7:F22 to display which specific account number to place the trade to. I am having difficulty with this step. I realize there is a possibility that some of the asset classes will require more than one account to trade to, of which i can designate additional cells to display those "additional trades" (but I have not gone that far yet).
Sorry this is so long, but I do sincerely appreciate any guidance.

 

[NdNoviceHlp]

That's abit more clear but yet somewhat difficult to follow. So F7:F22 should contain an account number(s) from A24:A30 based on the asset class traded (B7:B22)? I'm not quite following the nexus between account number and asset class? Does each account number hold a single asset? Do you want a spreadsheet or VBA solution? Dave

 

[luckyajr]

Hi Dave,

Thanks so much for the reply.
Yes, you are correct in that F7:F22 should contain an account number from A24:A30. So let's stop right there, and I'll clarify beyond this point.

I will try to be as descriptive as possible here:

All of the inputed accounts that are listed in A24:A30 (if there are that many accounts, that is) are all to be assumed that they consist of no securities and only "cash". Therefore, these accounts need to be allocated to a predetermined investment model. This predetermined investment model consists of individual asset classes (B7:B22). Based upon a client profile (irrelevant to this spreadsheet), the percentage of the individual asset class weight (A7:A22) is adjusted (totalling to 100%). All of the accounts are to be treated as "one bucket" of cash to be allocated to the investment model. In other words, instead of taking A7 and allocating all of that cash into the model, and then taking A8 and investing all of that cash into the model, etc. We want to treat the portfolio of accounts as if they are just one account. The reason for this is that this will minimize trading costs. So one account may only be allocated to 3 or 4 of the asset classes (B7:B22) and another account may have another 5 of the asset classes, and so on, instead of every account containing every asset class.
So, how do we know which asset class to assign the account to? Well, this is why I segment the accounts by "Taxable" and "Tax Deferred" (B24:B30) because different asset classes generally have higher current taxes associated with them. Therefore, we want to assign the "Tax Deferred" accounts to the higher current taxed asset classes (if possible). So, we rank the asset classes in terms of priority for being traded to a "taxable" account. Hopefully you're still with me.
You asked if each each account number holds a single asset. The opposite is actually the goal here (to minimize trades to multiple accounts). We want each asset class to contain only one account number (although invariably some asset classes will contain multiple accounts).
So once I input the information (see my below post) it outputs all of the factors needed to allocate the portfolio of accounts as "one account".
Again, I have success with all of the elements up to which specific account numbers (A24:A30) to allocate the asset class to (which are to appear in (F7:F22) and any other unused cells for the additional trades (if multiple accounts are necessary for an asset class). Although I have been successful with this process up to this point, I have no experience when it comes to VBA, so I'm not sure what I am wanting or needing for that matter. I do not necessarily need a spreadsheet as I have already created a spreadsheet up to this point (unless I am misusing the term "spreadsheet" and you are meaning something else here). I just want to be able to have the spreadsheet complete itself using only the "user inputs" described below in my previous post. Thanks again!!

 

[NdNoviceHlp]

Perhaps we need to put your algorithm for determining what account is traded into words/cell locations for a single example. I'm having difficulty following how you know what asset classes are included in each account and/or where this information is stored. It would be very useful for you to post an image of your "spreadsheet" (not sure if you have enough posts to use html maker). It seems that you will need a VBA solution if you may have more than 1 account represented in F7:F22 (ie. it will need to be comma delineated). There's likely a fairly easy solution... it's just difficult to craft a solution without a full understanding. Dave

 

[luckyajr]

Dave,

I'm not seeing anything about an HTML maker, so I'm not quite sure what would be the best way to display these algorithms, so any guidance on what exactly is the most efficient way would be appreciated. In the meantime, I will lay out an example as detailed as I can be, and apologies in advance if this gets wordy and lengthy.

Let's say a new household, Jane & John Doe, have the following three new accounts that need to be allocated via this spreadsheet:
Account #111 - Jane Doe- Tax Deferred with $355,000.00* in cash
Account #222 - John Doe- Tax Deferred with $425,000.00 in cash
Account #333 - John & Jane Doe - Taxable with $157,000 in cash
*For purposes of this example, I tried to keep the dollar amounts very clean and rounded, when obviously these accounts may actually have much more specific amounts.

The following "USER INPUTS" are entered into the spreadsheet:
In cell A25 - "111" is entered
In cell A26 - "222" is entered
In cell A27 - "333" is entered
In cell B25 - "Tax Deferred" is entered
In cell B26 - "Tax Deferred" is entered
In cell B27 - "Tax Deferred" is entered
In cell C25 - $355,000 is entered
In cell C26 - $425,000 is entered
In cell C27 - $157,000 is entered

With those USER INPUTS, it will output the following displays (and I will include the formulas next to them).

B2 -displays the total amount for all "Taxable" accounts ($157,000)
=SUMIF($B$25:$B$30,Sheet2!A30,$C$25:$C$30)

B3 -displays the total account for all "Tax Deferred" accounts ($780,000)
=SUMIF($B$25:$B$30,Sheet2!A31,C25:C30)

B1 -displays the total account balances ($937,000)
=B2+B3
On a side note, Sheet2!A30 displays "Taxable" & Sheet2!A31 displays "Tax Deferred".

C2 -displays the percentage of the total portfolio that is "Taxable" (17%)
=ROUND(B2/B1,2)

C3 -displays the percentage of the total portfolio that is "Tax Deferred" (83%)
=ROUND(B3/B1,2)

D25 -displays the relative account weight to its tax class (45.5%)
=IF(B25="Taxable",C25/$B$2)+IF(B25="Tax Deferred",C25/$B$3)

D26 - same idea as above but copied downward (54.5%)
=IF(B26="Taxable",C26/$B$2)+IF(B26="Tax Deferred",C26/$B$3)

D27 - same idea as above but copied downward (100%)
=IF(B27="Taxable",C27/$B$2)+IF(B27="Tax Deferred",C27/$B$3)

E25 -displays the relative account weight to the overall portfolio (38%)
=IF(B25="Taxable",D25*$C$2)+IF(B25="Tax Deferred",D25*$C$3)

E26 - same idea as above but copied downward. (45%)
=IF(B26="Taxable",D26*$C$2)+IF(B26="Tax Deferred",D26*$C$3)

E27 - same idea as above but copied downward (17%)
=IF(B27="Taxable",D27*$C$2)+IF(B27="Tax Deferred",D27*$C$3)

Now, let's refresh for a second. From an earlier post, I stated that A7:A22 displays the percentage of each asset class that is to be invested, summing to 100%. These percentages are based upon a client profile formula that is not relevant to the rest of the spreadsheet info. For this example, though, I will state what the percentages say, so we can be "apples-to-apples".
A7 - 6%
A8 - 6%
A9 - 3%
A10 -3%
A11 -3%
A12 -3%
A13 -1.5%
A14 -1.5%
A15 -0.9%
A16 -0.9%
A17 -1.2%
A18 -17.5%
A19 -17.5%
A20 -17.5%
A21 -16.5%
A22* -1%
*Another side note and I don't want to be confusing here, but A22 is essentially a "cash buffer" in which is not imperative to be identified for purposes of whether to allocate it to a tax deferred or taxable account (it can be considered a "carryover"), so I do not include in the following section.

Now, B7:B22 displays the asset class names (irrelevant to the formulas).
This next part deals with your questioning, I believe.
C7:C21 displays which type of tax status to allocate the respective asset class percentage to (i.e. "Taxable", "Tax Deferred", or "Allocate to Both*")
Here is the formula for C7 which needs to be copied downward through to C21.

C7: =IF(A7=0,"No Trade",IF(Sheet2!N20=FALSE,"Tax Deferred",IF(Sheet2!M20>0,"Taxable",IF(AND(Sheet2!$L$17>0,Sheet2!N20=Sheet1!$C$2),"Allocate to Both*"))))

Obviously you are wondering what is on sheet2. I'll explain now.

Sheet2!N20:Sheet2!N34 displays as follows:
N20 - 6%
N21 - 12%
N22 - 15%
N23 - FALSE
N24 - FALSE
N25 - 17%
N26:N34 all display "FALSE"
-These displays are the cumulative progression of the taxable amount invested for each asset class based upon a priority level that was assigned to each asset class for which asset class the "Taxable" accounts should be allocated to first. (I can give you this formula if need be but I do not think it is needed, but let me know if it is).

Sheet2!M20-Sheet2!M34 displays as follows:
M20 - 11%
M21 - 5%
M22 - 2%
M23:M34 all display "0%"
-These displays are the "Tax % Left" after the "Taxable" amount to be allocated the current asset class has been accounted for. (I can provide formula here as well if need be).

Sheet2!L17 - "1%"
-This displays the percentage of the "Allocate to Both*" asset class that will be allocated to "Tax Deferred". (I can provide this formula if need be).

E7:E22 displays as follows:
E7- $56,185
E8 -$56,185
E9 -$28,075
E10-$28,075
E11-$28,075
E12-$18,705
E13-$14,020
E14-$14,020
E15-$8,398
E16-$8,398
E17-$11,209
E18-$163,940
E19-$163,940
E20-$163,940
E21-$154,570
E22-$9,370
-These display the trade amount to be placed for the asset class. If the asset class requires more than one trade, only the relative "Taxable" percentage is displayed here. (I can provide this formula if need be)

I want these figures to calculate which accounts need to be assigned to the trades, so the portfolio of accounts can be allocated to the investment model.
I probably made this way harder than it needed to be, but again, I am very new to this sort of stuff and was learning as I was going. Thanks as always!

 

[NdNoviceHlp]

Being that you're very persistant and have taken much time to be as very specific as possible, I've taken the time to compose a replica of your wb as outlined. Some questions... what's in: sheet2!m25; sheet2!A30; sheet2!A31; where does the E7:E22 go sheet1 or sheet2? Dave

 

[luckyajr]

Dave,

I sincerely appreciate all of your assistance throughout this. I did just notice that I made a typo in my description of my spreadsheet to you in my previous post.

Cell Sheet1!B27 should actually say "Taxable" and NOT "Tax Deferred". Overlooked this when I reviewed it the first time.
So, Sheet1!B25:B27 should read as follows...

B25 - "Tax Deferred"
B26 - "Tax Deferred"
B27 - "Taxable"

So, the answers to your questions are below:

Sheet2!M25 should display "0%" and the formula is...
=Sheet1!C2-(Sheet1!A7+Sheet1!A8+Sheet1!A9+Sheet1!A12)

Sheet2!A30 should display "Taxable"
Sheet2!A31 should display "Tax Deferred"

E7:E22 should go on sheet1.

Unless I made another typo error, assume that cells are in sheet1 unless they are specifically identified elsewhere. Hope this helps. Sorry for the confusion!

 

[NdNoviceHlp]

Is sheet1!C12 supposed to be allocate to both? I don't know what is supposed to happen for the AND if true or false. The formula below is for C12 (note the /100 and something else). The formulas for sheet1 C7:C21 should be...
=IF(A12=0,"No Trade",IF(Sheet2!N25=FALSE,"Tax Deferred",IF(Sheet2!M25>0,"Taxable",IF(AND(Sheet2!$L$17>0,Sheet2!N25/100=Sheet1!$C$2),"Allocate to Both*","Something Else"))))
Dave