Using a Spreadsheet to Maintain a Portfolio

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.

Asset allocation

One of the easiest ways to maintain your portfolio is to categorize by asset allocation, like so:

Account	Domestic stocks	REIT	International stocks	Bonds	Total
Taxable	-	-	$14,000	-	$14,000
Roth IRA	-	$9,000	$9,000	-	$18,000
401(k)	$49,000	-	-	$19,000	$68,000
Total	$49,000	$9,000	$23,000	$19,000	$100,000
Desired	$48,000	$8,000	$24,000	$20,000	$100,000
Difference	($1,000)	($1,000)	$1,000	$1,000	-

• 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.

Sample spreadsheets

• 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, and combine multiple subclasses into one class (so that you may have a target allocation to foreign stocks, and separate targets for developed and emerging markets). A simpler version is also available; it does not track subclasses.
• Here is another sample spreadsheet. The first page breaks down your AA via The Finance Buff's Cascading Asset Allocation Method. The second page checks how close you are to your desired allocation and helps you figure out how to rebalance.
• One important aspect of of maintaining your asset allocation is to do periodic rebalancing. A sample rebalancing spreadsheet is available from Google docs (by forum member LadyGeek). Three example approaches are used, which could be incorporated into any of the sample spreadsheets.

Suggested improvements

• 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.

Tax lot tracking

Another use of a spreadsheet is to keep track of share lots in a taxable account, so that you can automatically compute the capital gain or loss when you sell, and easily see which shares should be sold for tax loss harvesting or to minimize the tax cost of selling. A spreadsheet for tax lots could look like this:

Date bought	Amount invested	Share price	Shares	Date sold	Share price	Amount received	Capital gain
2/1/08	$5000	$20.00	250.000	10/31/08	$15.00	$3750	-$1250
5/1/08	$5000	$18.00	277.778	-	-	-	-
7/1/10	$10000	$30.00	333.333	2/1/11	$33.00	$11000	$1000

Sample spreadsheets

• Here are three versions of a spreadsheet for tracking tax lots, and computing the capital gains and losses on each sale. There is a version for stocks or ETFs, most mutual funds, and funds with purchase and redemption fees. In a brokerage account, you would make one sheet in a workbook for each holding. The worksheets adjust for stock splits and share class conversions, but if you sell part of a lot, you have to split the lot into two purchases manually.

Performance tracking

• Mattman created a spreadsheet he calls "My Personal Index." Here is the Excel 2003 version and the Excel 2007 version. His description: "This Excel workbook will help you effectively track the performance of your investment portfolio. The workbook has been designed to handle all of the calculations for you and gather the data; all you need to do is provide the tickers and shares. The spreadsheet makes extensive use of behind the scenes VBA code to effectively compute a pseudo mutual fund based on personal data (using a NAV)."

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.

See also

• Tools and Calculators
• Rebalancing

References

 

Embed this text in your forum post:

Wiki article link: [url=http://www.bogleheads.org/wiki/Using_a_Spreadsheet_to_Maintain_a_Portfolio]Using a Spreadsheet to Maintain a Portfolio[/url]