Investment Portfolio Tracker

0 ratings

With the help of this Google Sheets template, you can quickly create an interactive and advance but simple-to-understand investment portfolio.

a Google Sheets spreadsheet that makes it simple for you to understand your portfolio's monthly performance.

This sheet available in USD and INR more are coming..

Check out the Example sheet here:

View

The portfolio sheet before

You must establish standards for your investments and portfolio. However,
The absence of easy-to-use portfolio tracking tools prevents investors from being able to simply assess their portfolio's performance.
As a consequence, an investor may find out by searching and conducting research online whether or not his portfolio experiences the projected increase.
Even if an investor is gathering information about their assets and portfolio, they might not have a mechanism in place to analyse and visualise the information in order to provide adequate data.

The portfolio sheet follows the

The template is simple to use, has a transparent workflow, and can be shared with investors.
As soon as data is submitted, portfolio trackers are quickly produced with aesthetically pleasing and tidy dashboards.
All of the investment data is contained on a single worksheet.
The monthly performance of your portfolio will be calculated with the help of script.
The realised and unrealized gains for each stock may be monitored by an investor.
A shareholder can keep track of their overall dividend payout.

Key Elements

Instantaneous categorization of investments for visual breakdown
A dashboard that shows the whole portfolio.
Just one data entry per transaction into a data sheet
Drop-down menus that are dynamically filtered and update as you type to speed up data entry and reduce human error!
Investor can find the value of a portfolio as on custom date by just one click.
Tracking the monthly performance of the investments.
Now, investor can use the Index as a bench mark and can compare the performance of the portfolio with that.
Investor can record the transaction in 5 different currencies and foreign currency investments in local currency through exchange rates automatically.
Investor can categorise transactions by different investments account and also in different industries.
Re-balancing of portfolio can also be done through separate dashboard.
Investor can store any numbers of transactions.
The guide of using the spreadsheet will also provided.

How does it function?

Step 1: Fill up the "Data" spreadsheet with your data.
Step 2: Open the "Dashboard" spreadsheet's interactive dashboard.
That's it. Thank you!

You'll get

Interactive Dashboard
Portfolio holdings
Monthly performance
Realised gain
Dividends
Re-Balancing
A spreadsheet contains a step.

Use HappyInvesting to get 10% off only few left.

FAQ

1)How should I input dividends on the Trade Log tab? What if my dividends are reinvested to purchase new shares (DRIP)?

To keep track of regular dividends that is received as cash:

Enter a “dividend” transaction on the Trade Log tab, where you input the date, ticker, total $ amount, and investment account where the dividend was received
It’s not necessary to enter the quantity of shares or amount per share (however, no issues if you do want to enter this extra info) — just entering a total amount in column G is sufficient

To input dividends that is reinvested into new shares (a “DRIP” strategy):

Enter your dividend as two separate transactions. One transaction is the regular “dividend” transaction, and the second transaction is a “buy” transaction to record the purchase of additional shares
For example, assume you get a dividend of $20 which was reinvested into 4 new shares of XYZ Corp. Enter two transactions: a “dividend” transaction where you receive a total amount of $20, and then a separate “buy” transaction where you buy 4 new shares of XYZ for $5 per share
The spreadsheet will now record that this dividend has been reinvested to purchase additional shares of XYZ

2)What ticker should I input on the Setup tab? and/or… What should I do if a stock or mutual fund isn’t searchable in the Google Finance database?

Please go to the google finance website (https://www.google.com/finance), type the name of the name of the company or fund in the search bar, and copy the ticker that Google Finance provides (the full ticker including the exchange).

Then, use that full ticker symbol in the investment portfolio tracker, on the Setup tab. Make sure that you delete any spaces in the ticker name.

For any stocks or mutual funds which don’t appear on Google Finance, you can do the following in order to have them included in your total portfolio:

Add a new holding on the Setup tab — any ticker is fine as long as it isn’t an actual ticker on Google finance
Then, use the manual price entry columns on the Your Portfolio Holdings tab (columns H, AF, AL) — since Google Finance won’t return a price per share for these tickers automatically, the spreadsheet will use these manual entry prices instead

Now, the Dashboard tab will show these stocks or mutual funds in your total portfolio value.

3)Does this tool work for people who don’t invest in dollars, or for investors with a multi-currency portfolio?

Absolutely! This investment portfolio tracker can be used by investors in any country. The spreadsheet even allows you to track investments in multiple currencies (up to five), all in one place.

For the calculations and outputs in the spreadsheet, all amounts are converted into a single common currency based on the currency that you select at the top of the Dashboard tab (cell C3).

So, if you now select GBP (British Pounds), all values will now convert to GBP. Likewise for CAD, USD, JPY, etc…

4)Does this calculate the total percentage return of my investment portfolio? What’s included in the performance calculation?

This spreadsheet calculates the total return of your portfolio over any given time period — including realized gains, unrealized gains, and dividends.

Go to the Dashboard tab, and scroll down to the Performance section. Once you enter a start date and end date, the spreadsheet will calculate the “money-weighted” return of your portfolio.

The money-weighted return is equivalent to the “internal rate of return” (or IRR) of your portfolio. This return calculation captures portfolio contributions, withdrawals, realized / unrealized gains, and dividends received.

If the time period you’ve entered is greater than 1 year, the result will be shown on an annualized basis. As an illustrative example, the tool would show a return of 7% per year instead of a total three-year return of 22.5%.

If the time period you’ve entered is less than 1 year in length, the result will not be annualized. For example, if your portfolio has earned a return of 5% over 3 months, the spreadsheet will show a return of 5%, rather than an annualized value of 21.6% (which would be misleading in my opinion).

By scrolling further down in the Performance section, you’ll also find the money-weighted returns shown for each of your custom investment categories. This allows you to compare the relative performance of different groups of investments (e.g., U.S. stocks, Canadian stocks, bonds, etc.).

Please note that the spreadsheet does not calculate the money-weighted return of individual stocks at this time.

However, you can view the unrealized gains and dividends received per stock over the life of the investment on the “Your Portfolio Holdings” tab.

You can also view your realized gains per stock on the “Realized Gains” tab.

5)How do I track cash positions / my opening cash balance?

This tool wasn’t built to track cash. However, if you do want to keep tabs on your cash balance as well, you can try this workaround:

On the “Setup” tab, add a new stock ticker called $CASH (or something else that doesn’t return a market value on google finance)
Whenever you get extra cash in your account — dividends, contribution, etc., enter in a buy transaction on the Trade Log where you purchase X shares of cash at $1 per share (X being the total amount of added cash)
Then, on the Your Portfolio Holdings tab, in the manual price entry columns (H, AF, AL), input a value of $1 per share for your $CASH investment
Now, you’ll see your new ticker $CASH, and the corresponding total $ amount reflected
If you use up that cash, remember that you’ll have to enter a corresponding sell transaction

6)How does the Monthly Performance tab work? How do I update the values?

I’ve written a script (a.k.a., a few lines of code) that runs when you click the “Go!” button.

This allows the spreadsheet to cycle through each month in your trading history and record the performance of your portfolio — starting value, contributions, withdrawals, investment returns, and the ending value.

7)Are crypto currencies like Bitcoin and Ethereum supported?

Yes they are! This spreadsheet tracks crypto currencies in the same way that it tracks more traditional stocks and ETFs.

On the Setup tab, input the ticker as the label for the crypto currency (e.g., BTC), followed by the label for the currency that it is priced in (e.g., USD). So, if you buy bitcoin with US dollars, the code is “BTCUSD”.

In the screenshot below, I’ve shown the tickers for Bitcoin and Ethereum, first priced in US dollars, then priced in Canadian dollars.

8)Does the spreadsheet support fractional / partial shares?

Yes — the spreadsheet does support fractional / partial shares.

If you flip between the Trade Log, Dashboard, and Your Portfolio Holdings tabs, you can see the calculations properly reflected.

In your spreadsheet, you’ll just need to increase the decimal points shown in the columns where the spreadsheet shows the number of shares you hold (I did this in the example I created). As it stands, the spreadsheet shows 0 decimals by default, but you can easily show more decimals by using the toolbar at the top of the sheet.

Specifically, the script will copy the numbers in row 76 of the Dashboard tab, and then paste those values into columns N to T of the Monthly Performance tab.

Please note that these numbers as “pasted as values”. Therefore, they will not update if you add new transactions to the trade log that impact a prior month.

If you do update your trade log to reflect new transactions in prior months, you just need to delete the numbers found in columns N to T (delete only the data, not the columns themselves), and then re-run the script.

9)What are the “return of capital” and “reinvested capital gains distribution” transaction types, and when should I use them?

ETFs can give “distributions” in a few different forms. Depending on the type of distribution, there will be different impacts on the adjusted cost base of your investment / different tax consequences.

Here are some good guides for understanding fund distributions:

This spreadsheet allows you to enter various transaction types on the Trade Log — including dividends, return of capital, reinvested capital gains distributions — and will adjust your adjusted cost base accordingly.

10)Why can't I simply purchase a template and edit it myself?

Of course, I say!

Therefore, save yourself time and effort and get the template if you're interested in purchasing the investment portfolio tracker and require it for several accounts so that you may use it right away.

11)If I need assistance, will I get it?

Yes! Please email me if you need assistance or have any suggestions! The template contains the mail ID.

Name a fair price:

I want this!