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.
Σ p 2 = w 1 2 σ 1 2 + w 2 2 σ 2 2 + 2w 1 w 2 ρ 1,2 σ 1 σ 2 Keep in mind that this is the calculation for portfolio variance. If a test question asks for the standard deviation then you will need to take the square root of the variance calculation. Portfolio Bar 1.1.1 – Stay updated on stocks. June 21, 2017 Portfolio Bar makes it easy to stay up to date on the stocks, ETFs and investments you care most about.
One of the easiest ways to maintain your portfolio is to categorize by asset allocation, like so:
| Account | Domestic stocks | REITs | 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 | - |
tfb's article, Cascading Asset Allocation Method, describes how to select a portfolio's asset allocation 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.)[2]
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:
https://ilapfd.over-blog.com/2020/12/world-of-tanks-blitz-3-6-624-download-free.html. Google Finance offers historical and real-time securities information using the GoogleFinance() function in Google Sheets.
If you have funds held in a Collective Investment Trust (CIT), 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.[note 1]
For example, suppose you hold the government's Thrift Savings Plan C Fund, which is a collective investment trust (CIT) that tracks the S&P 500 index. 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.
https://truezfil990.weebly.com/making-hard-drive-compatible-with-mac-and-windows.html. For example, these could be the cell along a row in your spreadsheet and this is what you would enter into them.
| Cell | Heading | Enter | Notes |
|---|---|---|---|
| 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.
Another technique is to find a website that outputs the price of the trust in real-time (or near real-time) and then use the GoogleFinance ImportHmtl() function to 'scrape' the HTML from the page.
If the ImportHtml() function can scrape that data cleanly into defined rows and columns with the price landing in a single cell, then you can then use the INDEX function to get the value of a particular cell by referencing its row and column. Two examples are described in the Bogleheads forum:
See also: Internal rate of return, Calculating personal returns
A spreadsheet to calculate investor and portfolio returns is provided in Calculating personal returns. It allows Bogleheads to report their personal returns in a uniform manner.
If you know the dates you made your investment contributions and your withdrawals, the XIRR (Internal Rate of Return) spreadsheet function is the best and most accurate method for calculating your rate of return. This method is far more accurate than annual average returns, as contributions and withdrawals (cash flows) are weighted for both time and value.[3][note 2]
The forum discussion thread XIRR function again has details and a working example.[note 3]
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 |
In addition to the spreadsheets there are various utilities to design and manage portfolios that span multiple accounts. Most tools cannot automatically compose your ideal portfolio, but you can use the tools to enter two or three models that you composed, and then you can compare and contrast the results, looking in particular at the blended expense ratios and the totals of each asset class. Money 4 3 1 – your sweetest accounting application. Two popular tools are:
|
|
| Since you are not logged in, you are only seeing our top performing stock and option positions within the last 180 days that have already been closed. To see our current portfolio with our stock and option selections, please register for a trial account or log in. | ||||
| ||||
| Symbol | Entry Price | Exit Price | Published On | Closed On | Type | Gain/Loss |
| PTON | $85.50 | $99.00 | 9/16/2020 | 9/23/2020 | Long | 15.79% |
| NIO | $14.40 | $16.80 | 8/24/2020 | 8/25/2020 | Long | 16.67% |
| SAIL | $31.00 | $38.13 | 8/5/2020 | 8/7/2020 | Long | 23.00% |
| ENSG | $45.80 | $54.70 | 7/22/2020 | 8/6/2020 | Long | 19.43% |
| AMD | $53.00 | $61.40 | 6/12/2020 | 7/22/2020 | Long | 15.85% |
| AUDC | $36.00 | $43.00 | 7/17/2020 | 7/22/2020 | Long | 19.44% |
| APT | $16.50 | $19.10 | 6/19/2020 | 6/24/2020 | Long | 15.76% |
| BIDU | $111.00 | $128.10 | 5/20/2020 | 6/16/2020 | Long | 15.41% |
| AMC | $5.50 | $6.40 | 5/12/2020 | 6/8/2020 | Long | 16.36% |
| BA | $155.00 | $184.00 | 4/7/2020 | 6/4/2020 | Long | 18.71% |
| MRNA | $53.00 | $64.00 | 5/8/2020 | 5/13/2020 | Long | 20.75% |
| SDC | $7.50 | $9.05 | 5/7/2020 | 5/11/2020 | Long | 20.67% |
| TWLO | $122.50 | $166.75 | 1/29/2020 | 5/7/2020 | Long | 36.12% |
| PYPL | $125.00 | $144.70 | 5/5/2020 | 5/7/2020 | Long | 15.76% |
| SITE | $82.00 | $98.10 | 4/28/2020 | 4/29/2020 | Long | 19.63% |
| Symbol | Entry Price | Exit Price | Published On | Closed On | Type | Gain/Loss |
| JD | $2.25 | $4.00 | 9/28/2020 | 10/7/2020 | Call Options | 77.78% |
| PSXP | $1.45 | $2.90 | 9/21/2020 | 9/28/2020 | Put Options | 100.00% |
| DIS | $2.75 | $6.00 | 8/17/2020 | 9/3/2020 | Call Spread | 118.18% |
| NKE | $2.50 | $6.00 | 8/14/2020 | 8/24/2020 | Call Options | 140.00% |
| KL | $3.69 | $7.12 | 7/9/2020 | 7/31/2020 | Call Spread | 92.95% |
| FOUR | $4.00 | $8.40 | 6/22/2020 | 7/8/2020 | Call Options | 110.00% |
| NOW | $6.11 | $25.05 | 6/15/2020 | 7/2/2020 | Call Spread | 309.98% |
| ADBE | $5.80 | $9.00 | 6/10/2020 | 6/17/2020 | Call Spread | 55.17% |
| BABA | $6.00 | $18.45 | 6/1/2020 | 6/16/2020 | Call Options | 207.50% |
| BSX | $1.95 | $2.80 | 5/26/2020 | 6/16/2020 | Call Options | 43.59% |
| AAPL | $4.70 | $7.05 | 6/8/2020 | 6/9/2020 | Call Spread | 50.00% |
| QSR | $2.95 | $6.20 | 5/22/2020 | 6/3/2020 | Call Options | 110.17% |
| OC | $2.50 | $6.00 | 5/12/2020 | 5/14/2020 | Put Options | 140.00% |
| DE | $4.00 | $7.95 | 4/20/2020 | 4/29/2020 | Call Spread | 98.75% |
| SQ | $3.35 | $5.35 | 4/13/2020 | 4/29/2020 | Call Spread | 59.70% |
0 
Star