Investment returns over typical time periods- YTD, 1, 3, 5, and 10 (and custom dates)

Spreadsheets gSheets Templates
1

Overview

I have published a comprehensive spreadsheet for Investment returns here. Due to the overhead, it is a separate spreadsheet from the Tiller foundation spreadsheet which imports the core Tiller Foundation information. A few of tabs use limited resources so I ported them into my Tiller Foundation spreadsheet.

This post is for Investment returns over typical time periods- YTD, 1, 3, 5, and 10 (and custom dates)…

image
image1960×1078 244 KB

Installation and Setup

Download here.

Install in your Tiller Foundation sheet.

You need to expand the columns to right to set up.

  1. Pick your Investment cash flow categories…

image
image796×322 19.4 KB

  1. Pick the Accounts you want to include. You are limited to 30 Accounts and 5 Account Groups (which should be enough for anyone). The only accounts available are those that have one of your Investment Cash Flow categories.

image
image1302×780 63.6 KB

You have the option to add custom dates you want to know the return.

That’s it.

Usage

You don’t need to do anything else.

Permissions

It is OK to use, modify, etc.

Enjoy.

:mega: October 2025 Q3 Awards & Shoutouts
2

This looks really interesting. Potentially stupid question…what do you mean by investment cash flow categories? I have various investment categories such as dividends, interest, advisor fees, etc. Do I need to include all of these?

3

You do not need to include those types of transactions.

Examples of Cash Flow transactions are…

  • Any deposits into your account (e.g. 401k contributions, when you transfer money into any investment account)
  • Any withdrawals you make from your account

You do not want to classify any transactions that are “internal” to the Account. Internal transactions include…

  • Dividends (either automatic dividend reinvestment or cash dividends)
  • Market change Transactions
  • Fees
  • etc.

The reason you don’t want to include these types of transactions is their effects are accounted for in the balance and will be reflected in overall returns.

If you want more investment return details, check out this.

Thanks.

4

That makes perfect sense. Thanks for responding. I’ll give it a try.

5

FYI: I was able to get this to work, but initially ran into a couple of different issues as all returns columns were displaying as NA.

Over the years, I had renamed various accounts and since your formulas rely on account names for matching instead of the account numbers then it showed I had many more accounts than I actually do. I had to go through my Transactions sheet and rename all the old account names to the current names. Easy fix, but you might want to warn users know of this issue.

The other issue was that the earliest transaction dates for my accounts were displaying in column AA, but not in column AE for the selected accounts.

I tracked this down to an issue with column AC, which had the error “Array result was not expanded because it would overwrite data in AC602. I ended up deleting cells AC602 to the end of the sheet and then the error went away and my returns displayed. I’m not sure if this is the correct fix, but it worked for me.

I’m still playing with this, but it appears to be pretty powerful and something I had been wanting. Thanks!

6

Thanks for the feedback and great you got it working. Some comments/questions…

  1. Account Names- I use purposely use Account Names not Account Ids as I have many different Accounts Ids for the same account due to institution mergers, replacement credit cards, etc. However, they are all the same “account” so I name them the same, even when the Account Id changes.

  2. “Array not expanded”- My original sheet ends at row 601, so I’m not sure what was in those cells!I can’t think of any downside to deleting what was in those cells so you should be OK. However, let me know if something looks off.

Thanks again.