Calculating my portfolio's growth

Factual Questions
1

I’m trying to plot the growth of my investment portfolio vs the major indices. The data I have in my spreadsheet is, for each quarter since 2010, how much money I put in (cumulatively) and how much it was all worth at the end of the quarter. For instance, if I write it out as text:

Sept. 2012:
Total amount invested to date: 80594
Current value: 85066

Dec. 2012:
Total amount invested to date: 83595
Current value: 89637

Mar. 2013:
Total amount invested to date: 86595
Current value: 93728

June 2013:
Total amount invested to date: 87594
Current value: 96007

As you can see, I put in a few thousand every quarter, as available.

At first I just calculated the ratio between these two numbers for every quarter, and projected that onto an imaginary initial investment of 10000. I did the same for the S&P 500, etc. and plotted them together. The result was… depressing.

Then it dawned on me: that recipe could only be accurate if I had invested a fixed amount in 2010 and never touched it since. But since I’ve kept investing more money every quarter, I can’t expect the money that’s been there 6 months to have grown as much as the money that’s been there for 5 years, and so on.

How can I calculate the past growth of my portfolio in a way that would make sense and allow a comparison with historical stock indices ?

I’ve read about personalcapital.com and their “You Index” that apparently does this. If possible, I want to avoid transfering all my information into an online portfolio system, and especially allowing such a system to access my financial institutions. So assume I only have OpenOffice Calc (equivalent to Excel) and desktop Quicken.

(We can neglect inflation for now. I know, it’s silly, I’ll get to it “later”. :slight_smile: )

2

I don’t know what platform you’re using, but on ETrade it’ll show the growth of each purchase (within a given security). Given that when you sell, at least with some brokerages, you can pick which lots to sell first (FIFO, FILO, average etc) and the IRS wants this info, the brokerage must be tracking this, so even if you can’t find out on the website, the info must exist somewhere.

Looking at a random stock that I’ve purchased multiple times on Etrade, I can see on 2/6/18 I bought 15 shares at $33 each and on 3/11/19 I bought 10 shares at $36 each. It’s currently worth $38. It shows, on the the respective lines that the 2/6/18 purchase has increased 18% and the 3/11/19 purchase has increased 3.61% (before anyone checks the math, I rounded to whole dollars). It also shows the total amount I paid and the total current value of each line. There’s also a line for each dividend payment.

I assume that’s what you’re looking for.
This will also give you a workaround to something that drives me up the wall. Dividends are added to your cost basis. That is, if you bought $500 worth of stock, and then got a $10 dividend, it’ll now look like you bought $510. I understand the reasons for this WRT taxes, but it always bugged me since it makes it look like the stock is up less than it actually is.
Because of that, in addition to keeping a running total of how much I’ve put into and taken out of the stock market in general, I’ve started making a note of how much I’ve paid for each stock. There’s been times when, for example, the total I own in a single stock is worth, say, $500 and my cost basis is around $500. It’s nice to know that, in reality, I only paid $450 and the rest was dividends.

I suppose that even if you can’t find this info in an obvious place, you can pick it out of your transaction history.

As for how to track it, I think the only way would be to put each transaction into Calc as an individual purchase on it’s own line or cell. From there it’ll be trivial to group them together by security and use some formulas so you only have to enter the current price for each stock into one spot and it’ll do the math and spit out whatever info you want.

3

nm

4

You can track the growth in your portfolio if you know the the amount of ever addition you made and the value of the portfolio at the time of the addition. You figure it out as the product

(value at time of second "deposit not including deposit)/(initial deposit)
times
(value at time of third “deposit” not including deposit)/(value at time of second deposit including deposit)
times …
(Value today)/(value at time of last deposit including deposit)

This is assuming no withdrawals (including dividends). You can ignore dividends in this calculation. In particular they do not count as deposits.

That will give you total growth. If you want to know the annualized growth rate raise number to the power 1/(# of years since first deposit including any fractional year) then subtract 1. Multiply that by 100% to get the answer in percentage points.

5

I’m using BMO InvestorLine for stock trading, it’s not a full platform. However, for tracking individual stocks, Quicken can show me this (I do enter every transaction).

But I’m trying to get the same kind of picture for all my stocks (I think I have that in Quicken) and all my bonds/GICs (across 3 institutions) and all my mutual funds (across 5 institutions) and especially the whole mix; I haven’t found a way to get that in Quicken.

6

Thanks, I think this is the recipe I was looking for. I believe it could work with my quarterly totals (instead of individual deposits) without losing too much accuracy. I’ll try it out tonight.

7

Forgot to mention: all of this is in tax-deferred or tax-free accounts, so taxes are not (yet) a factor.

8

It sounds like you are trying to calculate the time weighted rate of return. I wrote a spreadsheet years ago that calculates it. The math isn’t too hard but it does require a fair amount of data like OldGuy described. PM me if you want the spreadsheet and I can dig it out.

9

So I tried OldGuy’s formulas but somehow it didn’t work out, it made me look waaay richer than I really was.

Tired and Cranky’s hint that I was looking for time-weighted rate of return was the key. I found some formulas for TWRR at Investopedia. For posterity, here’s what my spreadsheet formulas look like (may require scrolling left-right):



     A         B          C           D        E                    F                    G
 6        Date         Inv to date Value     Quart inv   Quart gain                 TWRR
 7        2009-12-31   28896.30    29888.78       0.00                                   =D7/C7
 8        2010-03-31   44896.30    46252.67   16000.00                =(D8-C8)/C8     =G7*(1+F8)
 9        2010-06-30   44896.30    46171.67       0.00      =(D9-(D8+E9))/(D8+E9)     =G8*(1+F9)
10        2010-09-30   44896.30    46445.02       0.00   =(D10-(D9+E10))/(D9+E10)    =G9*(1+F10)
(...)

The TWRR column looks realistic when I plot it over 10 years against the various indices I’m tracking.

Thank you!

10

Hmm, yeah, on second thought, G7 should just be 1.0000.