The Asset Allocation Worksheet: Version 1 - …

1 The Asset Allocation worksheet : Version J. GrabinerFebruary 3, 2013 Changes from Version : Corrected tax rate computation on taxableaccount to be the fraction of gains lost to taxes. Adjusted example tax ratesfor 2013 tax changes, which are now permanent. If you upgrade from , you need only copy cell Microsoft Works/Excel worksheet is intended to help you keep trackof your Asset Allocation . It will not make the Asset Allocation decisions, butonce you have made the decision, it will tell you what percent of your portfoliois in each Asset class, adjusting for funds which cover multiple Asset , when you are ready to invest new money, or to rebalance, you canincrease your Allocation to those classes in which you are below the can adjust for the different tax treatment of assets in different ac-counts.

2 If you have $60,000 in bonds in a 401(k), and $40,000 in stocks in aRoth IRA, your effective Allocation is not 40% stock. If you will be in a 33%tax bracket at retirement, the IRS and state treasury effectively own 33% ofyour 401(k) but none of your Roth IRA, and thus your portfolio is effectively50% in stock. William Reichtenstein mentions this issue in [2].General instructionsThe sample worksheet includes example entries for several funds usingcommon account types and definitions of Asset classes. The tax rates usedare the ones in the example in the appendix; your own tax rates may Define your Asset can be divided into general classes,such as US stocks, international stocks, and bonds, and sometimes into spe-cific subclasses, such as large-growth US stocks, emerging-markets stocks,and inflation-indexed sample worksheet includes a common set of Asset classes.

3 To add anew specific class, copy columns D and E, and insert the copied columns tothe right of another column in the same general class, or to the right of thereal estate or cash column if the new class does not fit in any general can also delete a class by deleting its pair of Choose your target each class, enter your target per-centage (including the percent sign) at the bottom of its column, in the linelabeled Target Allocation . It may be easiest to do this in several stages,first deciding your Allocation to large classes such as stocks and bonds, andthen dividing each class into subclasses. For example, if you want to hold50% stock, and 80% of your stock in the US, and 40% of the US stock inlarge growth, you would multiply these numbers to get 16% as the target Y AA will automatically compute your Allocation to the largerclasses; real estate and cash are not included in these columns because theseclasses are not usually divided into more specific classes.

4 If you added ordeleted Asset classes, check that the totals at the far right are correct. If thetotals are not correct, change the columns summed in all four rows in Enter the classes of your each fund or individual securitythat you hold, enter the name in Column A, the balance in Column B, andthe Asset -class distribution in the % columns for the Asset classes (again, witha percent sign). The totals should add to 100%. Remember to include thecash holding in stock funds that have a significant cash position. If you havethe same fund in two different accounts such as a taxable account and anIRA, enter the fund Enter the tax rates for your each Asset line, enter inColumn C the effective tax rate (combined federal and state) for the accountthat contains the Asset .

5 For a Roth IRA or Roth 401(k), this is zero. Fora traditional IRA, 401(k), or other tax-deferred investment, enter your esti-mated retirement tax bracket. For a taxable account, use the sub-worksheetat the bottom (lines 28 34), and copy the number from B33 for all fundsheld in your taxable account. Note that this value is necessarily a roughapproximation, as it depends on future tax policy, the future returns of yourfunds, and the portions of these returns which are dividends and B29, enter the percent of your balance which you expect to earn annu-ally in unrealized capital gains. In B30, enter the percent which is in taxable2distributions, both dividends and distributed gains; thus B29+B30 should bethe pre-tax return.

6 In B31, enter the percent of your balance which you pay intaxes. Do not enter the raw tax rate; if distributions are 2% short-term gainsand non-qualified dividends per year taxed at 25%, and 3% long-term gainsand qualified dividends taxed at 15%, enter (.02 .25) + (.03 .15) = B32, enter the long-term capital gains tax rate that will apply when youwithdraw the money; use zero if you are certain that the account will bepassed on to your heirs with a stepped-up basis. In B33, enter the numberof years until you withdraw the money. For a retirement fund, B33 shouldbe the middle of your planned retirement period, in order to give an av-erage rate.

7 The formula for calculating B34 is somewhat complicated; itsderivation is explained in the tax-exempt municipal bonds, the tax laws still impose a cost; in atax-free account, you could hold corporate bonds, which have a higher , for the taxable distribution rate , use the yield on comparable cor-porate bonds, and for the tax on distributions , use the difference betweenthe pre-tax corporate and after-tax (allowing for state taxes if the bonds arefrom a different state) municipal yields. The unrealized gain rate for bondsis zero; any gains or losses you actually realize should be small. The tax is the cost of holding bonds in a taxable rather than a tax-free , if you hold savings bonds, which defer all taxes until they arecashed in, the unrealized gain rate is the yield of these bonds.

8 The capitalgains tax rate is the tax rate you will pay on the tax-deferred gains on thesebonds; use zero if you will qualify for the exclusion of tax on savings bondsused for educational expenses. For both the taxable distribution rate andthe tax on distributions, use the difference between the yield of savingsbonds and Treasury bonds (comparing EE bonds to conventional bonds, orI bonds to TIPS).5. Compare your actual and target 23 24 will nowcontain your effective Asset Allocation in dollars and percent, which you cancompare to your target Asset the far right, columns Y AA, you can compare your actual and targetallocation in the more general Asset classes.

9 This may be useful if you cannotallocate money precisely into the specific Asset classes; for example, if your401(k) has no small-cap international fund, you can still rebalance the 401(k)from US stocks into international stocks to reach your target Allocation inall international and special instructionsIf unexpected negative or huge numbers appear in the table, check formissing percent signs. If you entered a tax rate as 30 (rather than .30 or30%), it will be read as 3000%.The totals in the Asset Allocation line (row 24) will always add to 100%even if there are errors elsewhere in the spreadsheet such as row totals notadding to 100%.

10 Check your row allocations for each fund before looking atthe Asset your row allocations are determined, you can hide the % columnsin order to display the entire spreadsheet more effective tax rate column can be used to adjust for any type of cost,not just taxes. For example, if you are locked into a high-expense retirementplan at work, the expenses should be counted against the value of the the expenses are 1% higher in your work plan than in your other accounts,and you will be able to roll the plan into an IRA when you retire in 20 years,you will keep (.99)20=.82 of the value of the plan. If you then add in a 30%tax rate at retirement, you will keep.