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. Using a spreadsheet to maintain a portfolio is intended to suggest one way of easing the burden.
One of the easiest ways to maintain your portfolio is to categorize by asset allocation, 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.
- 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.
- One important aspect of of maintaining your asset allocation is to do periodic rebalancing. A sample rebalancing spreadsheet is available from Google Drive (by forum member LadyGeek). Three example approaches are used, which could be incorporated into any of the sample spreadsheets.
- 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.
Asset allocation visualized
tfb's article, Cascading Asset Allocation Method, describes how to select select a portfolio's by visualizing the process as a top-down hierarchy. The most important selection is at the top (stocks/bonds), which then breaks down into smaller asset classes. Selection of the actual funds is done last, which is at the bottom of the chart. Two spreadsheets are available.
First, forum member pradador has created a spreadsheet which allows one to create a portfolio by following the advice in the article. The spreadsheet is available on Google Drive: Cascading Asset Allocation (The spreadsheet was further modified by forum member LadyGeek to include error checking.)
This spreadsheet includes a fixed income major asset category, which is not mentioned in the article. There is no consensus on the role of fixed income in a portfolio; it's dependent on your situation. If fixed income is considered as a bond, then set the percentage of Fixed Income to 0.
There are two tabs:
- Top Down: Starting on the left side and proceed to the right, enter the desired asset allocations to arrive at the percentage of each fund's contribution to your portfolio. The bottom row is reserved for error checking. If an entry error is made (the percentages do not add up to 100%), an error will be displayed.
- Bottom Up: Use this tab to determine the asset allocation percentages of your portfolio when you know the funds. In the right-most column, enter the percentages and funds. The spreadsheet will work left and arrive at the asset allocations. The blank cell in the lower right-most corner will display an error if the percentage does not add up to 100%.
Forum member Ducks has another variation. The first page breaks down your asset allocation. The second page checks how close you are to your desired allocation and helps you figure out how to rebalance.
Collective investment trusts and GoogleFinance
If you have funds held in a collective investment trust, in which there is no ticker symbol, then you can use a proxy fund to keep your balance roughly up to date using the GoogleFinance functions in Google spreadsheets.
For example, suppose you hold the C Fund, which is a 500-index collective investment trust (CIT) in the government's Thrift Savings Plan. Find out the closing price of the CIT on the current day, and also find a similar proxy fund, such as Vanguard's institutional index fund (VINIX).
For example, suppose the C Fund's closing price is $23.6602 and Vanguard's VINIX is $167.75. This means that a share of the C Fund is worth 7.08 times a share of VINIX (167.75/23.6602).
To maintain a real-time estimate of your C Fund balance without needing to update the C Fund's share price every day, you can instead take the daily price of VINIX and divide it by the multiplier to get an approximate share price for the C Fund.
For example, these could be the cell along a row in your spreadsheet and this is what you would enter into them.
|A2||Shares||100||Enter the number of shares you have in the Collective Index Trust (CIT). As you add or subtract shares from contributions, withdrawals or exchanges, you'll need to periodically update this amount.|
|B2||Closing Price||23.6602||Enter the CIT's closing price. You'll need to look this up in your plan.|
|C2||Close Date||01/03/2014||Enter the closing date on which you got the "Closing Price" of the CIT in B2. Make sure you enter the date and closing price are in sync, and be sure that the closing price has been updated in case there is a lag between the market close and the posting of the closing prices.|
|D2||Proxy Fund||VINIX||Enter the ticker of a fund that is similar to your CIT.|
|E2||Proxy Price||=INDEX(GoogleFinance(D2, "close", C2); 2; 2 )||This returns the closing price of the proxy fund you selected in D2, on the date C2, which is the same date you recorded the closing price of the CIT.|
|F2||Multiplier||=E2/B2||This is the ratio of the Proxy Fund's Price divided by your CIT's price, on the same date.|
|G2||Proxy Fund Current Price||=GoogleFinance(D2, "price")||The current price of your proxy fund|
|H2||CIT Approx Price||=G2/F2||This is the approximate calculated share price of the CIT|
|I2||Balance||=H2*A2||The approximate balance you hold in the CIT. This is the approximated price of the CIT multiplied by the number of shares you hold in the CIT.|
To keep this reasonably up-to-date and to prevent the "multiplier" from drifting over time, from time to time you can replace the date in cell C2 and the actual closing price of the CIT in cell B2.
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:
- 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.
- Mattman22 created a spreadsheet he calls "My Personal Index." Here is 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)." [footnotes 1]
- The best method of determining your Rate of Return (also known as Return on Investment or ROI) is with the XIRR (Internal Rate of Return) spreadsheet function. Internal refers to the fact that its calculation does not incorporate environmental factors (e.g., the interest rate or inflation).The forum discussion thread XIRR function again has details and a working example.[footnotes 2]
- 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.
- Vanguard Portfolio Watch - an online tool for Vanguard clients to track asset allocation. See linked page for more information as well as caveats and limitations.
- This spreadsheet has been superseded by a stand-alone application. See: My Personal Index - Now a Standalone Application, forum discussion.
- XIRR function again also describes a bug in MS Excel's XIRR() function. This bug is not present in LibreOffice or Google Drive Sheets.
- To download, select File --> Download As --> Excel or OpenOffice.
- Cascading Asset Allocation Spreadsheet, forum discussion.
- Internal rate of return, from Wikipedia