How to Calculate Market Gains

How to Calculate Market Gains

Mint and Personal Capital are both stellar ways to track your finances, but if you’re like me, you might be more of a DIY type of person.  You might even have your own spreadsheet to track your finances!  This is a post for those of you that track their own finances in Excel or Google Sheets (or I suppose Numbers if you’re into that kind of thing).

I wrote a post recently talking about how to calculate your savings rate and why it’s absolutely the most impactful and informative metric you can track.  Another one I like to track is market gains. This tells you how much money up your down your portfolio rises or drops by on a given month.  I’m not the best about this, but I try to not look at the stock market daily. I don’t find it to be particularly helpful because knowing the current state isn’t going to change my investing strategy.

Well anyways, time to hop into Excel!  Every month I track how much I invest. These fall under a handful of buckets:

  • Roth

  • Traditional

  • After-Tax

  • ESPP

  • RSUs

  • And lastly a column that sums the 5 to the left of it (i.e. total contributions into the market for that month)

 

And then there’s another column off to the right that shows my total portfolio value that I enter once a month from Mint.  The tricky part is making your formula to calculate the gains month over month.

 

The formula above would obviously need to be adapted slightly depending on which columns you have for your data, but in principle, it should be perfect to use as a model.  A quick excel note: the $ signs thrown into the formula sort of “lock” those rows and columns from changing when that same formula is copied and pasted elsewhere. Once you have the formula working, you can simply copy and paste it down on each row for every month in your spreadsheet.  Here is what the formula is actually doing piece by piece:

 

  • Q5 is the cell that contains that total amount of money in your investment portfolio on the final day of that month

  • -sum($k$2:k5) is subtracting the sum of all of the investment contributions you’ve ever made.  If we stopped after this, we would have the total market gains all time, not just for that month

  • -sum($n$2:n4) gives us the sum of the gains/losses from all previous months

  • -sum($l$2:l5) removes any sales of investments that may have happened during that month.  For example say you sold off $20,000 in shares of VTSAX to help fund a down payment. If this final bit of the formula wasn’t added, it would look like your portfolio dropped $20,000 from the market crashing, when in reality it was just you selling it off

 

Did you find this helpful?  I love tinkering in Excel and I love spreading the word about how great of a tool it is.  I plan to keep sharing mini Excel tutorials such as this, but would need some feedback from you.  Is this too complex? Am I not listing enough steps/explaining in enough detail? Please let me know in the comments any feedback you might have, or any Excel related question you’d like me to tackle next!

 

Thanks for reading, hope you enjoyed!

Leave a Reply

Close Menu