Using a Spreadsheet to Maintain a Portfolio

From Bogleheads

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.

Contents 1 Suggested Spreadsheet 1.1 Possible additional features 2 Sample spreadsheets 3 Online Resources 4 See also 5 How to Cite

Suggested Spreadsheet

One way to make it easy to maintain your portfolio is to use a spreadsheet 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.

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

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

How to Cite

You can link to this page from the Bogleheads Forum by copying and pasting the following text into your forum posting:

Please see [url=http://www.bogleheads.org/wiki/Using_a_Spreadsheet_to_Maintain_a_Portfolio]Using a Spreadsheet to Maintain a Portfolio[/url] on the [url=http://www.bogleheads.org/wiki/Main_Page]Bogleheads Wiki[/url].

Notice something that needs correcting? Notify a wiki editor or become a wiki editor and fix it yourself!

Resources and Links
Academics • Books and Authors • Bogleheads Authors • Financial Websites and Blogs • Glossary • Google Docs.spreadsheets • Historical and Expected Returns • Tools and Calculators

Portfolios
Sample Portfolios	Lazy Portfolios
Portfolio Management	Investment Policy Statement • Rebalancing • Lump sum vs DCA • Using a Spreadsheet to Maintain a Portfolio
Portfolio Withdrawals	Withdrawal Methods • Safe Withdrawal Rates
Asset Classes	US Stocks • International Stocks• Real Estate • Bonds • Money Markets • Alternate Asset Classes