Investement Returns (Monthly, Annual, Total, XIRR)!

What is the goal of your workflow? What problem does it solve? How does it help you?

Goal is to automatically calculate monthly, annual, and XIRR returns on market investments and to compare these returns to any market ticker (e.g. SPY, QQQ, GOOG, etc.).

Before I discovered Tiller, I had created a spreadsheet to calculate these items. Every month I would manually enter the net monthly cash flows and ending balance for all my investment accounts. When you have many different accounts with multiple institutions, this takes a while.

I also wanted to group my investments (Education, Retirement, All, etc.) so I could determine returns on these groups as well.

Automating this task saved a lot of time and it also automatically adds in new accounts and groups.

How did you come up with the idea for your workflow?

I had created this spreadsheet a while ago to calculate these items and wanted to make it easier to determine my returns.

What are the sheets included with your template? Does your workflow use any custom scripts or formulas?

There are 3 included, but they are independent of each other so you can use as little or as many as you want.

There are no custom scripts or formulas.

Is it ok for others to copy, use, and modify your workflow?

Absolutely. Here is the link- Investement Returns

Set up and use

All 3 sheets use the same philosophy…

  1. This only works on Asset accounts that have a Balance and at least 1 deposit. The Sheet determines which accounts are valid…

The first transaction date for each account should be when you make the initial deposit into that investment account.

  1. If you know your cash flows and balances for any investment account, you can determine your return…

Cash Flow Table - Copy

In order for the spreadsheet to calculate returns, you need to categorize all your deposits and withdrawals into your investment accounts with the same Category. My Cash Flow Category is “Investment Cash Flow”, but you can name it anything you want and choose that Category in the Sheets…

Cash Flow Category

Examples of Cash Flow Category Transactions are…

  • 401k contributions (I classify my and my company’s contributions the same so the company’s contributions do not artificially inflate my return)
  • Your Brokerage account deposits and withdrawals

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

  • Automatic dividend reinvestment
  • Market change Transactions
  • Fees
  • etc.
  1. Once you can generate the above table for any Account or Group, you can determine any type of return!

Cash Flow Table

The Sheets

  1. Single Investment

You can pick any Account that meets the criteria above, the associated Account Groups, or All accounts.

You can also choose a Comparison Ticker (e.g. SPY, QQQ, GOOG, etc.) The sheet automatically determines what the return is for this comparison ticker using the same deposit and withdrawals you put into the Account, Group, or All.

The sheet is limited to 20 accounts total. Even if you have < 20 valid Accounts, you still need to determine which Accounts to include…

Include Accounts

You can have all your accounts in one Group or split between multiple Groups. The maximum number of Groups = maximum number of Accounts = 20.

  1. All Investments

This sheet consolidates the returns on all the individual Accounts and Groups into 1 sheet. You can choose which Accounts, Groups, or All to include in the table. As with the

Since the sheet has to know the returns on all Accounts and Groups, it is limited to 15 accounts total and 5 Account Groups. All 15 Accounts could be in 1 Group or split between the 5 Groups. Even if you have <= 15 valid Accounts, you still need to determine which Accounts to include.

If the Accounts you choose result in >5 Groups, only the first 5 Groups alphabetically will be available

As with the Single Investment sheet, you can also choose a Comparison Ticker (e.g. SPY, QQQ, GOOG, etc.) The sheet automatically determines what the return would have been on this comparison ticker using the same deposit and withdrawals from the Account, Group, or All…

The summary table is currently set up for 20 years from the earliest investment cash flow category for all your accounts. You can change the Sequence formula to customize this. If you want to add more years, you would need to add more columns to the right of the display columns and change the Sequence formula…

Sequence

  1. Monthly Cash Flow

Self explanatory.

Corner Cases/Error Handling (what should happen :crossed_fingers:)

  1. What if you don’t have a deposit prior to the balance date?

The sheets use the first Transaction date as the starting date for each Chosen account. If you have a Balance prior to the first deposit and want to include it, manually enter an earlier deposit.

  1. What if I don’t have a Balance for each month?
  • If it is the first Transaction month of the account, Balance = the initial deposit.
  • If it is not the first Transaction month, Balance = previous month’s balance
  1. What if I haven’t categorized all my investment cash flows with one Category?

You can either rename them all to the same Category or create separate sheets for each account or separate investment cash flow category. If you create separate sheets, this will complicate the Transaction ImportRange idea shared above as it filters based on the Category you pick in the Single Investment sheet.

Misc

This spreadsheet is decent sized and processor intensive. Because of this, I have these sheets in a separate spreadsheet from my main Tiller spreadsheet and use Query(ImportRange)) to filter the 4 Foundation sheets you need- Transactions, Balance History, Accounts, and Categories. I suggest you start this way as well.

To help with this, there is sheet in my Spreadsheet called Tiller Connection. Just put your Tiller Spreadsheet URL into this sheet and choose your Cash Flow Category in the Single Investment sheet and this spreadsheet will automatically import and filter the data you need.

Enjoy and let me know if you have any questions.

Scott

There were a few formula errors I discovered after my initial post (mainly in the “Monthly Investments” sheet). I corrected them today.

1 Like