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

[Cowboy13]

October 2023 Update- Part 1 (Rev 1.6)

• @wally.bryan found error on the All Investment tab. This fixes that error.
• Rev 1.6 can be found here

July 2023 Update- Part 3 (Rev 1.5)

• Update based on my July 23 post below. Rev 1.5 can be found here
• This updates allows a larger number of Balance History cells to be imported. @jmilner uncovered a scenario where importrange reached its limit of importing 175,000 cells from Balance History. I updated the importrange function to pull in 1 column of Balance History at a time.

July Update 2023- Part 2 (Rev 1.4)

• I finally figured out how to (correctly) calculate Total Returns= (Current Balance + Withdrawals [assuming WDs are positive #s])/(Total Deposits)

• I also use this to calculate YTD returns- counting Beginning Balance of the year as a deposit.

• Optimized multiple formulas.

The updated file (Rev 1.4) is here.

---

July 2023 Update (Rev 1.3)

• Updated the YTD performance formulas to provide more accurate returns %s

• Corrected a few misc errors

The updated file (Rev 1.3) can be found here

If you used a previous version, please updated to this one if you run into any errors; and let me know if you encounter any issues.

March 2023 Update

• Fixed the EOM error on Monthly Investment sheet. It was pulling BOM, not EOM.

• Updated the workbook to allow up to five categories to be used as Cash flow in/out of Investment accounts. Original was 1 category. You enter these categories on the “Single Investment” sheet.
  

• Misc improvements

The updated file can be found here.

---

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, they are explained below.

There are no custom scripts or formulas.

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

Absolutely. Here is the link- Investment 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 workbook determines which accounts are valid.

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

2. If you know your cash flows and balances for any investment account by month, you can determine your return by month, year, lifetime. The smallest time period allowed is by month.

In order for the spreadsheet to calculate returns, you need to choose the category types for your deposits and withdrawals into your investment accounts. You can choose up to 5 categories. I only use one Cash Flow Category- “Investment Cash Flow”, but you can name it anything you want and choose those Categories in the “Single Investment” sheet.

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.

3. Once you can generate the above table for any Account or Group, you can determine any type of return!

The Sheets

1. Single Investment sheet

You can pick any valid Account, 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 returns are for this comparison ticker using the same deposit and withdrawals you put into the Account, Group, or All. FYI- Sometimes, GoogleFinance is not able to pull the data needed for the comparison. This seems random but you’ll see this in the table as “[ticker] issue”.

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

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

2. All Investments sheet

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.

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 can 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. FYI- Sometimes, GoogleFinance is not able to pull the data needed for the comparison. This seems random but when it happens you’ll see this in the table as “[ticker] issue”.

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…

3. Monthly Investments sheet

Choose which Account or Group you want and it will show you the returns by month. You can choose a month on the right and it will show EOM balances for all accounts.

Corner Cases/Error Handling (what should happen )

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.

2. 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

Misc

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

To help with this, there is sheet called “Tiller Connection”. Put your Tiller workbook URL into this sheet and choose your Cash Flow Categories in the Single Investment sheet. This workbook will automatically import and filter the Transactions, Categories, Accounts, and Balance History data it needs. If the Transactions, Categories, Accounts, and Balance History sheets are blank, you either have the wrong URL for your main Tiller workbook or you haven’t allowed a connection between the 2.

Enjoy and let me know if you have any questions.

Scott

[Cowboy13]

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

[danielrthompsonjr]

This is fantastic!!! Thanks very very much!

[Cowboy13]

Glad you like it. However, for me, it’s depressing to look at this year!

[TexasBill]

Howdy Cowboy13. I’m struggling to understand how to get this set up and working. Is everything still working? should I be having trouble?

[Cowboy13]

TexasBill,
Sorry for the delay in getting back to you.

It should still work fine. I would needed more details into what issues you are running into to offer any suggestions.

[dixonge]

@Cowboy13 I’m finding this pretty difficult to understand. I put my main Tiller Spreadsheet URL into the Tiller Connection tab/sheet, but nothing changed anywhere.

Very confused.

[Cowboy13]

What does your “Connections” tab look like. If the link is correct, it should look something like this…

Thanks.

[dixonge]

yeah, I finally got that part working, but no idea where to put transactions? Can they be imported?

[Cowboy13]

If your URL is correct, then the problem may be the Category name to import. You choose this in the “Single Investment” tab…

Then the transactions are automatically imported in the “Transactions” tab.

My Category to import from the investment accounts is “Investment Cash Flow”. To determine return, I just want to compare the current balance compared to how much money I deposited - withdrawls.

Dividends, fees, etc. affect return but are not categorized as “Investment Cash Flow” because they’re not cash that I deposit or withdrawal.

[jmilner]

Hi, I am trying to use this template, which looks great, but I am getting some errors. From what I can trace back in the formulas it looks like the issue relates to the transactions tab, which, for whatever reason, doesn’t seem to be matching all the columns. Below is what my import tab looks like. You’ll se there are a few #N/A. When I look at my Transactions tab, I dont see columns for Tags, Account ID, Group, or Account. That seems to the issue, but not sure why I wouldn’t have those columns.

EDIT: For whatever reason I can’t seem to upload the screen shot, but I’ve listed out the missing columns above.

[jmilner]

I may have sorted this in a less elegant way. It seems the “Account” column is not present in any of my two tiller workbooks on the transactions tab, and that seems to be the lookup that was broken. I changed that to “account #”, which I think solves the problem. It is less elegant as I need to know what account numbers correspond to which accounts.

I am not sure why my transactions (and other sheets) would have fewer categories than yours.

[Cowboy13]

@jmilner- First off, welcome to the Tiller community!

Not sure why the “Account” is not present in your “Transactions” sheet as I think it is foundational column in that sheet. What does Your “Tiller Connection” sheet look like in the Investment Returns spreadsheet?

-Scott

[jmilner]

So I had the “account” column in my transactions sheet, but it was missing the column header and had not been populated with data for any row since mid-2020. I added the column header and the feed seems to be populating the column for new imports. I also backfilled the missing rows. So that’s now solved.

That has lead to another challenge, some accounts have a different name between the transaction sheet and the balance history sheet so they are not matching in your workbook. I’m trying to fix that. it seems more manual. I know I’ve updated the names of the accounts through the years, but not sure why the name would be different between the sheets.

I would post the connection sheet, but every time I try, I get an error “An error occurred: Sorry, you can’t embed media items in a post.”

[jmilner]

Here is a screen shot (just needed a few more posts).

[Cowboy13]

@jmilner

It’s connected to your Tiller workbook and specifically the Transactions sheet; otherwise, it would not pick up any of the columns headers. Looks like you have some missing headers in that sheet.

Once you fix that, it should work.

-Scott

[Cowboy13]

Another thing to look at in the “Tiller Connection” sheet. Check out the “Foundation Sheets Headers”…

This shows the column headers from your main Tiller workbook. If you don’t see the column headers there, this workbook will not find them.

-Scott

[jmilner]

From what I can tell, the columns that I’m missing do not seem to impact the usability. Or maybe I haven’t run into that case yet?

One aspect that I am struggling to think through is the usage of one category for cash flow. I understand the need to track the cash in/out for return calculation, but thinking through the cases were limited to one category may impact the usefulness of other reporting (e.g., live P&L). I’m guessing you’ve through about this and curious for your perspective.

For example, employer 401(k) match, that is a cash inflow and not a return. I could categorize that as “cash flow for investment” but then I dont have any reporting on how much my match was unless I went digging for individual transactions based on name.

From a P&L perspective, you’d end up with one net category that could represent a number of different in and outflows. Maybe in practice from a P&L reporting perspective that doesn’t matter as you’ve captured them in the investment return sheet.

[jmilner]

One other thing that I have noticed in the “monthly investment” tab (and this logic may flow through to the other calculation), column D is labeled EOM balance - which I read as end of month balance and would expect that amount to be, but based on the balances being pulled into the table, they are beginning of the month balances.

For example, here is one account in the monthly cash flow tab: Notice how there is cash flow in the month but no EOM balance.

When I look at the balance register in the same tab (which agrees to the tiller master sheet):
There is a zero balance at the beginning of the month. This seems to be the value being looked up:

But at the end of the month there is a value in the same table that is not being pulled in:

Spot checking some of the other EOM balances, I’m seeing the same thing (the EOM balance is not EOM but beginning of month).

[Cowboy13]

@jmilner

Quick answer as to why I used only 1 Category is that it was simple to implement and met my needs. I agree that it would be more flexible if it allowed multiple categories. However, I would limit to <5 as the formulas and processing overhead go up significantly as you add more categories for your investment cash flow.

As for company match, I categorize those as an investment cash flow even though it’s not my money. The reason why is my primary objective is to measure my returns and compare to a baseline. Counting company match as cash flow accomplishes this.

As for EOM, I’d need to look into that. Might be as easy as subtracting 1 day and it’s EOM or just changing the column title to BOM!

There are some other minor improvements I’d like to make as well.

Give me a bit of time to make some edits (allowing multiple categories, look into EOM, and misc others) and I will post the update when I’m done. I hope to have it done in the next 2 weeks.

Thanks.