Using a spreadsheet to maintain a portfolio

Introduction
Investors should look at all of their accounts as a unified portfolio to construct a portfolio that is low cost, well diversified, and tax efficient. Those who are new to the concept are often overwhelmed by the complexity involved in maintaining such a portfolio. This page is intended to suggest one way of easing the burden.

Suggested Spreadsheet
One way to make it easy to maintain your portfolio is to use a spreadsheet like so:


 * Cells in the rows for Taxable, Roth IRA, and 401(k) should be manually entered. All other cells can be automatically calculated.
 * Each cell in the Total row represents the total amount of domestic stocks, that of REIT, and so on.
 * The Desired row represents the desired amount of dollars in each asset class and can be calculated by multiplying the value of your portfolio by the percentages in your target asset allocation.
 * The Difference row tells you how far off your portfolio is relative to your desired asset allocation. It can be calculated by subtracting a cell in the Total row from the corresponding cell in the Desired row.

Possible additional features

 * You might want to make a separate Target row to keep track of your target allocation; in that case, the Desired row would be the product of the Target row and the total portfolio.
 * Similarly, you might make an Actual row to compute your percentage allocation, so that you could compare your target percentages to your actual percentages.
 * If you have funds which cover multiple asset classes, you can put each fund in a different row, make an extra column for the total fund value, and have the spreadsheet automatically compute the amount of the fund which is in each class. This allows you to easily track the effect on your allocation of adding $10,000 to a balanced fund.

Sample spreadsheets
(Anyone else who has designed a spreadsheet and would like to make it available is welcome to list it here; mine is more comprehensive and thus not as simple as the example above.) Grabiner
 * Here is a sample spreadsheet in this style. It includes the additional features above; it can also adjust for the different after-tax value of assets in different accounts.

Online Resources

 * Morningstar Instant X-Ray is essentially a pre-built spreadsheet; enter your portfolio and the X-Ray will show its current allocation and other statistics.
 * Be careful about using Vanguard Portfolio Watch (available on the Vanguard site) for tracking asset allocation; it treats most funds as being entirely in their primary stock category, so a fund which is 40% large-cap and 60% mid-cap might be treated as entirely mid-cap in computing your allocation.