3 ms·

Do you have a spreadsheet template? Finding the efficient frontier and the relevant numbers seems hard?

anonu 7y ago
If you just select 80/20 there's no mean variance optimization going on... If you want to do mvo, there's tons of free tools on the web. Or is very easy to build one yourself in Excel. You need the solver and at least a year of historical data for each name.IEX cloud has a fantastic free API for this.
toast0 7y ago
It sounds like you're having trouble with asset allocation, more than figuring out how to implement your allocation. Nevertheless, here's a description of my spreadsheet. I'm using a variant of the three fund portfolio. So I've crafted a target percentage for stocks and bonds, and the target percentages for each of the funds I'm using for that. Then you add things like your traditional 401k balance (which I've put in bonds), your roth 401k balance (in stocks for me), and maybe you've got a couple of those, it's easier to have one line for each, so you can run through the online accounts and put it in. Anyway, so on the left side of the sheet, I've got the list of all those funds, the target %, the current balance, and then the target balance. On the right side, I have my new contribution -- most of my contributions are coming from equity based compensation, so I have a bunch of stuff over there to help me set aside the right amount for taxes (I could have a side rant on that). Target balance is computed for taxable stocks and bonds overall by doing (total balance + contribution) times allocation% - amount of that in tax advantaged. Then, for each fund in taxable, I take the overall balance for stocks or bonds and use the ratio of allocations within that class to compute the target for that fund (if I have 30% for bonds, and 21% is for fund A, and 9% for fund B; whatever I got for taxable bonds times 21 divided by 30 is the target for A. Things to note: a) depending on the size of your taxable and tax advantaged balances and your overall asset allocation, tax efficient fund placement may dictate what goes where. For me, my tax advantaged has clear choices, so each type (tax deferred, roth) gets fully allocated to one type of fund. Also, you want to avoid using "substantially identical" funds in taxable and non-taxable, to avoid potential wash sale issues that are hard to track and have unfortunate consequences (if you do a wash sale in taxable, it's fine, you don't get the loss booked, but your cost basis is preserved; if you sell for a loss in taxable and buy substantially identical in tax advantaged, the loss is disallowed but the basis is not transferred, so you just lose that forever). If this isn't very helpful, I can probably make a clean copy of my spreadsheet as a template; I would share mine directly, but it's hard to know what hidden data is in there.

If you just select 80/20 there's no mean variance optimization going on... If you want to do mvo, there's tons of free tools on the web. Or is very easy to build one yourself in Excel. You need the solver and at least a year of historical data for each name.IEX cloud has a fantastic free API for this.

toast0 7y ago

It sounds like you're having trouble with asset allocation, more than figuring out how to implement your allocation. Nevertheless, here's a description of my spreadsheet. I'm using a variant of the three fund portfolio. So I've crafted a target percentage for stocks and bonds, and the target percentages for each of the funds I'm using for that. Then you add things like your traditional 401k balance (which I've put in bonds), your roth 401k balance (in stocks for me), and maybe you've got a couple of those, it's easier to have one line for each, so you can run through the online accounts and put it in. Anyway, so on the left side of the sheet, I've got the list of all those funds, the target %, the current balance, and then the target balance. On the right side, I have my new contribution -- most of my contributions are coming from equity based compensation, so I have a bunch of stuff over there to help me set aside the right amount for taxes (I could have a side rant on that). Target balance is computed for taxable stocks and bonds overall by doing (total balance + contribution) times allocation% - amount of that in tax advantaged. Then, for each fund in taxable, I take the overall balance for stocks or bonds and use the ratio of allocations within that class to compute the target for that fund (if I have 30% for bonds, and 21% is for fund A, and 9% for fund B; whatever I got for taxable bonds times 21 divided by 30 is the target for A. Things to note: a) depending on the size of your taxable and tax advantaged balances and your overall asset allocation, tax efficient fund placement may dictate what goes where. For me, my tax advantaged has clear choices, so each type (tax deferred, roth) gets fully allocated to one type of fund. Also, you want to avoid using "substantially identical" funds in taxable and non-taxable, to avoid potential wash sale issues that are hard to track and have unfortunate consequences (if you do a wash sale in taxable, it's fine, you don't get the loss booked, but your cost basis is preserved; if you sell for a loss in taxable and buy substantially identical in tax advantaged, the loss is disallowed but the basis is not transferred, so you just lose that forever). If this isn't very helpful, I can probably make a clean copy of my spreadsheet as a template; I would share mine directly, but it's hard to know what hidden data is in there.