@bee: Your question on tracking a portfolio is thought provoking. Here’s my 2 cents.
I am retired, thus I am in the withdrawal stage, not the accumulation phase. I use a
Lazy Portfolio Scheme to implement an asset allocation of 50/50 stocks to bonds.
I use Excel worksheets as follows:
A “Portfolio” worksheet tracks the portfolio (I also have a “Temp” and “Rebalance” worksheets described later). At the top are today’s date, current value, value at end of last year, and YTD percent gain/loss. My current YTD is a loss of 2.114% that includes all drawdowns to date including most of my Minimum Required Distribution (MRD).
NEXT is the “Assets” table of current price, shares owned, value and category (Domestic Stock, International Stock, Fixed Income, and Cash) for each asset. It is arranged in alphabetic order to accommodate brokerage watch lists and Excel LOOKUP functions.
I use the following categories:
Domestic Stock
International Stock
Fixed Income
Cash
NEXT are details for each account in my portfolio (Rollover IRA, TIAA Traditional, A-Trust, B-Trust, Individual, Experimental, and Credit Union). For each account the Price, Shares, Value and category of each asset are listed. A summary by category shows the value, and percent of each category in the account.
LAST is a Consolidated Details for all accounts. I use a separate table for each of the categories. For each asset I list Price, Sum of shares from all accounts, Value, Current Allocation, and Target Allocation.
Thus I can visualize my portfolio in terms of assets, assets in each account, and assets sorted by category.
A “Temp” worksheet provides an intermediary for specifying current asset prices. I copy values from a Watchlist in my Fidelity account. I paste the values from the watchlist into the Temp worksheet and then copy the price column to the portfolio worksheet. (Note that I have arranged my assets in alphabetical order for this to work.) The Temp worksheet can be used for general calculations with the restriction that no other worksheet should link to any cell in the Temp worksheet.
A “Rebalance” worksheet calculates buys and sells when I rebalance my asset allocation portfolio. In general I compare the current allocation of an asset to a target allocation and do buys and sells to rebalance to target values.
First I create a “Target Allocation Table” that shows target allocations for each of the four categories and the assets in each category. I initially set the target values in collaboration with a fee-only Financial Advisor. The values by major category:
29% Domestic Stock ETFs
21% International Stock ETFs
45% Fixed IncomeS
5% Cash
I won’t go into details of this worksheet, but have the following remarks:
1. There will be a variety of account types: As one gets older different financial situations occur, e.g. trust accounts, retirement, death/divorce of spouse, marriage, etc. There needs to be flexibility to handle financial resources that are not mutual funds or ETFs. My situation has accounts:
Trust-A
Trust-B (with its separate tax-ID)
Rollover IRA
Individual (used as a conduit between other accounts and my bank)
Experimental
Credit Union
Life can become more complicated in retirement!
2. I fine-tune allocations between accounts: Although my Rebalance worksheet does an initial calculation of buys and sells, I manually fine-tune the allocations for the following reasons:
a. I want to have sufficient cash in the IRA to fund my MRD from that account.
b. I want to take
capital gains in tax deferred accounts, not taxable accounts.
c. In the Trust-B account I want to minimize taxes so I over allocate a muni-bond fund.
d. The Experimental account is exempt from buys and sells of a rebalance operation.
3. I combine two or more assets into a “Combined Asset”. For example I am taking a Minimum Required Distribution (MRD) from a TIAA Traditional account that does not allow me to trade this asset. (There are other options I could have selected, but immediately liquidating the entire asset was not available.) I have solved this by combining the TIAA with my BND ETF and allocate a percentage to the combined quantity that I call “Total Bond Market/Long-Term Fixed Income” Within this combined asset, I assign TIAA’s target allocation to its current allocation, and the BND target allocation to the difference of the target allocation assigned to the Combined Asset and the TIAA current allocation. Another example might be a $10,000 T-Bill that can only be sold in its entirety. Yes, I know this is a forum about mutual funds and ETFs, but stuff happens and we need to adapt.
4. I rebalance based on a calendar event, not a market event or a difference between current and target asset percentages. I have chosen the middle of August and February. I want to avoid any semblance of market timing.
I hope you find this useful.
