My take on investment returns tracking

[purujit.saha]

Overview

It tracks the IRR (specifically XIRR) of different accounts over specified time periods based on cash flow and balance history.
The goal is to get an accurate view of how your portfolio is doing. I have been using @Cowboy13’s awesome solution until now but it is pretty slow and it does not support more than 15 accounts without some battle. It is a common community ask and I wanted to share what I hacked together this evening.

Installation

You can copy this template into your spreadsheet. See screenshot below.

You also need to import a named function from the template to your sheet. In your foundation sheet, Go to “Data > Named Functions”. It should open a side bar with a “Import function” button near bottom right. Use that to import the COMPUTE_DATE_RANGE function from the template sheet. This part is no longer needed since the sheet does not use named functions anymore.

Setup

Populate the accounts to track (column A), investment categories (column B) and periods to track (column C). The rest should auto-populate.
It requires Transactions, Accounts and Balance History sheets in the foundation template. Don’t modify the hidden section that contains external references for INDIRECT and some other computed columns but everything else should be fair game.

Usage

It should auto-update as new transactions are added. It supports common period shorthands like YTD, 1Y, 3Y, 6M etc. The amount of computation depends on (number of accounts) * (number of periods) - so if you have a lot of accounts, try tracking fewer periods.

Permissions

Yes, I’d love it if it helps someone. Let me know if you spot bugs and have feature requests. I am looking for testers with longer transaction history at Tiller because I have a suspicion that my approach may not scale very well to years of transactions.

Notes

I am always looking for cool enhancements to Tiller. If you have an idea and want to collaborate, please get in touch. I have decades of experience in building large scale distributed systems but I like this type of low-stakes development for fun.

cc’ing some folks who were looking for this type of tracking in the past in other threads. @dsm @RedNell @christopher.scherr

[purujit.saha]

Quick note: I have since added support for relative time period strings like YTD, 1Y, 6M etc.
I have also added another column for simple returns in addition to IRR.

[RedNell]

I gave this a try but have been unable to get it to work. I copied the sheet into my Foundation template, designated two investment accounts in column A, two types of investment deposit transaction in column B, and left column C alone. Almost every cell starting in column Q gives a 'Unknown function: ‘COMPUTE_DATE_RANGE’. error.

[purujit.saha]

@RedNell Thanks for giving this a shot and sorry about the missing function. I thought functions will copy along with the template but apparently, you need to specifically import them. I have now updated the installation instructions with this

You also need to import a named function from the template to your sheet. In your foundation sheet, Go to “Data > Named Functions”. It should open a side bar with a “Import function” button near bottom right. Use that to import the COMPUTE_DATE_RANGE function from the template sheet.

When you get a chance, please give this a shot. Thanks again!

[RedNell]

Hmm, even though the function imported, I am not seeing any change with the error. I even imported a fresh version of the returns sheet after the function import and set that one up again, with the same errors, even though I now have COMPUTE_DATE_RANGE

Screenshot

[purujit.saha]

@RedNell Can you see the formulas in cells Q3 and Q4? Do either of them have the following line:

period, #REF!(periodExpression, $J$3),

If so, change the #REF! to COMPUTE_DATE_RANGE and see if that helps. I think we are running into some bug with how named function import interacts with sheet import.

[RedNell]

It almost seems to me like part of the function in Q3 is not importing, maybe failing when it encounters the named function. If I compare my Q3 to the Q3 in the sample template, it looks like there is a big chunk missing.

= LET(
    periods, TRANSPOSE(FILTER(C2:C, NOT(ISBLANK(C2:C)))),
    numPeriods, COUNTA(periods),
    numAccounts, COUNTA(A2:A),
    MAKEARRAY(
        1, numPeriods * 5,
        LAMBDA(
            rowIndex, col,
            LET(
                colIndex, MOD(col - 1, 5),
                periodIndex, FLOOR((col - 1) / 5),
                periodExpression, INDEX(periods, periodIndex + 1),
                startingBalanceColumns, ADDRESS(ROW() + 1, COLUMN() + periodIndex * 5, 1) &":"& ADDRESS(ROW() + numAccounts, COLUMN() + periodIndex * 5, 1),
                cashFlowColumns, ADDRESS(ROW() + 1, COLUMN() + 1 + periodIndex * 5, 1) &":"& ADDRESS(ROW() + numAccounts, COLUMN() + 1 + periodIndex * 5, 1),
                endingBalanceColumns, ADDRESS(ROW() + 1, COLUMN() + 2 + periodIndex * 5, 1) &":"& ADDRESS(ROW() + numAccounts, COLUMN() + 2 + periodIndex * 5, 1),
                startingBalance,  SUM(INDIRECT(startingBalanceColumns)),
                endingBalance,  SUM(INDIRECT(endingBalanceColumns)),
                cashFlow, SUM(INDIRECT(cashFlowColumns)),
                IFS(
                    (colIndex=0), startingBalance,
                    (colIndex=1), cashFlow,
                    (colIndex=2), endingBalance,
                    (colIndex=3), (endingBalance - startingBalance - cashFlow) / (startingBalance + cashFlow),
                    (colIndex=4), "TODO"
                )
            )
        )
    )
)

[purujit.saha]

That’s a red herring - the big chunk is the implementation for the Todo part that I have just added an hour ago. If you import now, that part should come through. But I’m seeing the REF! Issue that I mentioned in my previous reply when I try to import it fresh and it goes away when I change the formula after importing to reference that function. So it seems like Google sheets somehow messes up the reference when importing the formula.

[purujit.saha]

@RedNell I have now uploaded a new version without the named function. Hopefully, that sidesteps the issue altogether. Please try deleting the previous import and re-importing when you get a chance.

[RedNell]

That works much better to get going, thank you. I do think this has good potential as a light-weight, fast alternative to Cowboy13’s larger return sheet.

I am seeing the numbers that don’t make sense to me, though. The cash flow should be a sum of the investment transactions for the time period, right? I selected a 1M, 1Y, 2Y periods, and for one example account they are all showing the same cash flow, even though that account had regular deposits over the last two years.

Not sure what I can do to help debug if you aren’t seeing the same. I have two types of investment transactions listed. For this account I am focusing on all the deposits are the same one of those (“Investment Deposit”).

[purujit.saha]

@RedNell That is strange. I have a similar account and it is showing up correctly for me. But we can debug this if you are up for it.

• Is the cash flow amount that is shown for all periods correct for any of the periods? Maybe, the 1M cash flow is showing up for all the periods? Is it the first period that you chose? Does it change if you choose a different period as the first period? If so, I might have a theory and uploaded a bug fix to the same template but as a different sheet titled “Investment Tracker (INDEX bug fix)”.
• The matching transactions are imported into the sheet in (hidden) columns L through O. Can you unhide and see if all “Investment Deposit” transactions are there?
• The formula for Cashflow computation is really simple. I am putting it below. Can you paste it in an empty cell and see if that returns the right number (of course, use your date range and account id)? You can find the account ids in Column K - first row is the first account id and so on…

= SUMIFS($O1:$O, $M1:$M, "655e9999f3667f002fc9a903", $L1:$L, ">=2024-01-01", $L1:$L, "<=2024-02-24")

Thanks again for trying this out…

[RedNell]

Columns L-O don’t look right - it stops at row 17 (dated 1/16/2024). I’ve tried changing around the Periods specified in col C including the first one. Even if the first period is 5Y then matching transactions stop at row 17 even though there should be dozens/hundreds? of transactions.

[purujit.saha]

Ah, yeah, that would lead to the result you are seeing. I suspect there might be some subtle difference in either the “Account ID” or the “Category” values prior to 1/16/2024.
The query that populates columns L:O is = QUERY({INDIRECT(G5), INDIRECT(G7), INDIRECT(G8), INDIRECT(G6)}, "Select * where Col3 matches '" &TEXTJOIN("|", TRUE, $B$2:$B)& "' and Col2 matches '" &TEXTJOIN("|", TRUE, $K$1:$K)& "'", 0)

It is basically fetching the “Transactions” sheet columns “Date”, “Account ID”, “Amount” and “Category” while matching the account id with the one you are tracking along with the category. I think it is likely that your account id has changed or there is a subtle difference in the category text. Can you find a transaction that should come through but didn’t and compare with one that did come through?

[purujit.saha]

Note that the “Account ID” column is hidden by default in the transactions sheet. It should be around column L or M in that sheet depending on if you added any columns or not. It should look like a gibberish identifier like 655ef562f3667f002fd6f00d

[RedNell]

Ah ha, that did it. I manually imported everything from Mint at the end of the year. I don’t think anything in the Foundation template really relies on the account ID in the transactions sheet so I never filled that in. Looks to all be working now.

I like the solution! It’s nice and quick. I wonder if it has the potential to gain a few of the visualization features that Cowboy’s template has while remaining single-sheet and fast.

[purujit.saha]

Awesome!
Which visualization features would you like? The bar chart in the “Single Investments” page? That should be easy to add - one bar for each period - one bar for each account would be too busy imo. I also love the comparison with a specified ticker feature but I want to be able to use a total return index - otherwise it’s pretty useless since about 2% of SPY returns are dividends. That’d require an Apps Script function to pull from Yahoo finance.
If you are happy with just the price returns, I can add that in quickly - I’m thinking a row below “All” and in the bar chart.

[RedNell]

I don’t have any specific ideas, but I think there’s a lot of potential here. A bar graph comparison to an index would be great. I also like the ‘All Investments’ visualization of Cowboy’s solution which has a nice table of account returns by year, although I’m not sure how much you could fit into a single-sheet solution.

[purujit.saha]

I’ll work on adding the bar chart. Meanwhile, it is possible to get a similar visualization as the “All Investments” in the other solution. You can just hide the balance and cashflow columns - I have now added the period header in all columns. Here’s an example:

I like selecting relative dates like 1Y, 3Y and 5Y instead of absolute ones like a specific year because I am used to that format of presentation from brokerages e.g. in Fidelity:

Also, I feel like all that matters is what is the performance to-date and it does not matter if the performance was very good/bad in 2023 since I can only spend what I have now - haha.

[purujit.saha]

@RedNell I added bar chart with a ticker comparison now (just a single benchmark is usable for now) using GoogleFinance data. You can import the “Investment Tracker With Comparison Ticker” sheet in the template

It is tricky to compute the equivalent return from the benchmark because attributing cash flow at the right granularity is both complex and slow because of all the lookups from GoogleFinance. I chose to just keep it simple and attribute the period’s total cashflow against the average of the period start and end benchmark price. I think that gives good ballpark results for most portfolios without sacrificing speed too much. Hope you and others find this useful.

[RedNell]

I like this a lot and will definitely use it. I think it strikes a nice balance of usefulness and ease of use and speed. Thanks for your contribution.