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

I understand the one category, I’m not sure that it needs to be more. I need to think through how to make that work for me.

Ha, I think EOM needs to be EOM as it should take into account the corresponding cash flow changes.

Thanks for all the effort on this one!

@jmilner

The EOM is exactly what it’s supposed to be. Column B is month, but formatted as MM/DD/YYYY. Column DD (“EOM Balance”) is the end of month balance of that month.

Although using only one category works for me, I’m updating the workbook to allow more than one category so it’s more flexible and can be used by more people. I’m testing now and will post update shortly.

I’ll take a look at the update, as the EOM balances in my copy (which I haven’t changed) the EOM balances started in Col DK are all BOM and not EOM.

I’ve made some updates…

  • Fixed the EOM error on Monthly Investment sheet. It is now the EOM, not BOM. FYI- I had updated column K in Monthly Investment sheet to be EOM, but never updated the data that fed column D.

  • Updated the workbook to allow up to five categories to be used as Cash flow in/out of Investment accounts…
    image

  • Misc improvements

The updated file can found here.

Please take a look and let me know if you have any additional questions or feedback.

I hope you find this workbook useful.

Hi @Cowboy13, really awesome sheet, I just started using it today.

It is working great for my accounts that I have complete data on (initial deposit, all transactions, all balances) but I am having issues with accounts that are older where I don’t have the complete balance or transaction history. Or in some cases, I have more transaction history than balance history.

Is there a way that I can handle this, for example just being able to specify a start date and start balance so all of the calculations are done using those values as a baseline?

@frank.cusano9,

Glad you got it to work and you’re finding it useful.

As for your “incomplete” accounts, check out the “corner case” section in my original post…

  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.

(At least this is the way it’s supposed to work!)

In reality, you can’t have an initial balance without an initial deposit. For an account you’re having issues on, try entering an initial deposit into the Transactions sheet (in your original Tiller workbook) for the same amount as the initial balance in your Balance History; but a day before the first balance date. This will set the start date for that account equal to the first transaction date and it should populate returns until the last balance date in Balance History.

Let me know how this works. You can see the details by choosing that account in the “Monthly Investment” sheet.

So I actually have the opposite problem of #1 “What if you don’t have a deposit prior to the balance date?”.

I have an account with deposits going back ~6 months but a balance history for only 3 months. So what happens is my initial deposit is set as the initial balance (like you mentioned). But then once my actual balance kicks in 3 months later, the sheet thinks that I had a 103,000% gain because it is not accounting for all of the other deposits that have been made, nor is it accounting for the initial balance (balance of my account before importing into Tiller).

The only way that I think I can solve this currently would be to get my entire account balance history and manually import it into Tiller. I would do this but unfortunately, it seems like Fidelity does not store your balance history so there really is no way of knowing.

I think a simple solution would be to allow you to specify a starting date and starting balance for an account, rather than using the first transaction date and assuming a $0 initial balance.

Frank,
To calculate an accurate return, the data needs to be in Tiller.

I have Fidelity accounts as well and had to manually enter this info into Tiller so I know it takes some time. The good news is that you only have to do it once for the history and it automatically updates any new activity. The other good news is that, for Fidelity you can pull 10 years of data the workbook needs (balances and deposits and withdrawals) from their website.

For balances, you can pull up to 10 years through the “Performance” option…

You can pull deposits and withdrawals through the “Activity & Orders” option for the past 5 years…

For more history (balances and activity) up to 10 years back, go to the “Statements” section…

image

Hope this helps.

Thank you.

Thank you for helping out, unfortunately, I only have access to Fidelity through their NetBenefits portal and there is no easy way to export balance history from there (I tried and there is even a thread on Reddit about it). I ended up just biting the bullet and manually typing in the balance history statement by statement which ended up not being that bad. Now I have full transaction and balance history so its working good now.

Yes, I had to do that from Net Benefits as well. Took a bit but only need to do it once.

Great to hear it’s working now; let me know if you have any other questions.

@Cowboy13 Thank you again for this sheet. I’ve run into another issue. On the “balance history” tab I am getting an error message “Result too large”. It looks like the import limits of IMPORTRANGE are not posted.

I was thinking I could alter the formula to only pull specific accounts that have been selected vs. all asset balances, but not sore if that would work with your file construction. Thoughts?

John,
Haven’t run into that error. How many line items is your “Asset” Balance history? FYI- I’m pulling >7500 rows into mine which (x 13 columns) equates to ~100,000 total cells.

I did find this article- google sheets - How to work around the IMPORTRANGE error: "Results too large"? - Web Applications Stack Exchange, that says maximum # of cells for ImportRange is ~175,000. If this is true, I’m below the limit. It also suggests a pretty simple fix- split the ImportRange into multiple to pull in fewer rows.

If you want to check this on yours, change the formula in cell A1 in the Balance History tab to:

=query(importrange(‘Tiller Connection’!A2,“‘Balance History’!A:T5000”),“Select * Where Col”&Column(indirect(‘Tiller Connection’!C25&1))&" = ‘Asset’",1)

If the error goes away, that’s the issue. Let me know if this works.

Thanks,
Scott

Yep, that’s it. Too many rows/cells. If I limit the query to 5,000 rows it works fine. I tried adding the array to the formula, but clearing I’m doing something wrong as Google says I haven’t closed something. Here is where I am:

=ARRAYFORMULA({query(importrange(‘Tiller Connection’!A2,“‘Balance History’!A1:T5000),”Select * Where Col”&Column(indirect(‘Tiller Connection’!C25&1))&" = 'Asset’”,1));query(importrange(‘Tiller Connection’!A2,“‘Balance History’!A50001:T10000),”Select * Where Col”&Column(indirect(‘Tiller Connection’!C25&1))&" = 'Asset’”,1)})

John,
Here’s a better solution. I’ve updated the workbook to use a separate importrange on each column of Balance History. This will now work to 175,000 rows of Balance History!

Also, since I only use 6 columns from the Balance History in this workbook, I only import these 6 columns. This reduces the number of cells this workbook uses.

I’ve posted this update at the top of this topic.

Let me know if this works.

FYI- In the Tools section on the Tiller Community Solutions extension, there is a Trim balance history tool.

You may want to use this to trim the number of balances you have. This would not only help this spreadsheet, but your main Tiller one as well.

Thanks! Looks like the share link is locked. I’ve used the trim balance history, it is still a long history…

I think it’s fixed now. Try this .

I also updated the link at the top.

Hey @Cowboy13 , I love the sheet. Still working through it and figuring it out. New to Tiller too, so I don’t have too much balance history yet, so looking forward to seeing this grow as I continue to update my investment account balances and activity.

I think I found a formula error in the “All Investments” sheet.
Cell QJ has =if(isblank(QG1),iferror(1/0),index($CY$1:$HR$1,1,match(QG1,$CY$1:$HR$1,0)+3))
I changed to: =if(isblank(QG1),iferror(1/0),index($CY$1:$HS$1,1,match(QG1,$CY$1:$HR$1,0)+3))
I was getting an out of bounds error as the match returned 125, and the 125th cell is HS1.
This only becomes evident if you choose All in the selection in B31.

Look forward to your feedback/thoughts.

Wally,
Glad you find it useful.

What cell are you referring to? You list column QJ, but no cell.

Thanks,
Scott

Sorry, my brain must have been fried from unraveling the formulas and references. :slight_smile:

Cell is QJ1

Thanks,
Wally

Wow, nice catch. Yes, that array goes through column HS, not HR.

I have updated the Shared Sheet to v1.6.

The new link is at the top of this thread as well.

1 Like