@Cowboy13 I really do love this file. It is a great way to track overall account returns vs. individual investments which is what I care most about. I would personally love if you could add another analysis tab in the workbook. I think it would be easy given how itās structured. I also think the analysis would be helpful to others which is why I havenāt tried to add it to my version (plus your updates are great).
It is a simple bridge analysis (table and chart).
Filters:
You would input a start and end date (as a user inputed date) - my use case would be annual periods but I think so long as you resolved to the end of month date for the start and end date it would be great and work with the data structure you currently have.
Select account(s)/groups - logic you already have built
I dont think I care about categories, but maybe thereās a use case.
Analysis:
Your first column is the opening balance of the account(s) on the start date
Second column is the inflows into the account - this is just the positive cash flows into the account
Third column is withdrawals - the negative cash flows from the accounts
Forth is market changes - the difference left (ending - beginning - change in deposits and withdrawals)
Last column is the ending balance
I call it a bridge chart, some people call it a waterfall chart. It is a visual representation of the changes from the beginning to the ending balances.
Iāve tried to simplify it, and the purpose is to be able to look at an account(s) and see what is driving the change in balance (not return %/IRR) over the period due to cash flows (and which way) or market changes.
Check it our here. I included some commentary in my initial post as well.
Was a bit trickier than expected because I used net cash every month; I didnāt break out withdrawals and deposits. Had to create some new tables to break these out.
Also, choosing start and end date has some corner cases I had to deal with.
Let me know if you have any questions or feedback.
Limited testing so far, and will let you know if I find anything odd. One small request, in the data table below, it would be great to see i) total $ changed over the period and the % of that change from market and cash flow. I can do this pretty easy in my copy, but may be nice in the master.
One question on dates, is MM/01/YY the beginning of the month or the end of the month? So if I wanted to show the change from Dec 31, 2024 to Aug 31, 2025 what months would I select?
Thanks and no issue adding those things. For $ changed over time period, are you looking for End balance - Beginning balance over that time period or something different?
On % change, Iām assuming you want return during that time period?
On dates, I use the last balance prior to the month start and transaction are month inclusive.
FYI- I am creating this template to use in the standard Tiller sheet. It is pretty straightforward and will allow additional options because all the data is there- not just Asset Accounts and the Transactions with the chosen Cash Flow Categories. Any interest in this?
After some thought, I realized this could be greatly optimized, so I did. I also added the ability to choose up to 2 Accounts or Groups. I also added in the ability to show how dividends and account fees contribute and added in $ change and return %. You can find this updated version here
These Categories are independent of the Cash Flow categories you choose in the Setup and Calcs tab.
Outside of the Waterfall tab, I also optimized the multiple ImportRanges calls so it loads faster and more reliably than previous versions. And, for those who were seeing āresults too largeā (@aaron18, @MidnightPrzm, @MC_Matthew, there may be others), this issue should no longer happen.
Thank you for sharing this worksheet, it is incredibly useful.
I have a question on the Net $ Change value calculation on the Waterfall worksheet.
When a start date is not specified, the Starting Balance is $0 (just like the image you posted here. As such, the Net $ Change value translates to Ending Balance - Total Deposits (H25 - C25). This makes sense to me, given that the starting balance was $0.
However, when a start date is entered, the Starting Balance reflects the balance of that start date, which might not be $0. I would then expect the Net $ Change to be equal to Ending Balance - Starting Balance - Total Deposits (H25 - B25 - C25).
I may be misunderstanding what the Net $ Change value represents. Would you mind sharing your thoughts?
@AHB,
Net Change is simply Ending Balance - Starting Balance; regardless of Deposits, Withdrawals, Expenses, Dividends, or Market Change.
Market Change is what youāre describing. It is the Ending Balance - (Starting Balance + Deposits - Dividends - abs(Withdrawals) - abs(Expenses)). This is reflected in column W- example from W3ā¦
Thanks for the quick reply. In that case, the formula should be H25 - B25 (Ending Balance - Starting Balance). Somehow on my worksheet, the formula is referencing the incorrect columns. I have I25 = H25 - C25.
Iāve changed it to I25 = H25 - B25 as you suggested and that makes more sense.
Big update with additional features and substantial optimization. Now up to version 3.0
Enhancements includeā¦
Added Returns over common investment timeframes- YTD, 1, 3, 5, All, Custom
More accurate return %s as it uses actual cash flow dates instead of month granularity
Added more information to Single Account tab
Fixed YTD returns for Monthly Investment tab
Comparison ticker pulls daily values instead of weekly to get more accurate ticker comparisons
Added capability to include dividends to comparison ticker if you subscribe to WiseSheets
Optimized Importrange function for Transactions to only pull in transactions that have Investment categories you choose (was pulling in all transactions). I have >13k transactions in my Tiller foundation sheet, <1k for my chosen investment categories
Optimized importrange function for Balances to only pull in Asset accounts
Can now choose up to 30 Investment accounts instead of 20
In terms of optimizationā¦
My file size for Version 3.0 is 325k vs 642k for v2.3 (yours may be different based on the amount of data it imports)
Number of columns in āSet up and Calcsā tab has dropped to 183 vs 516; all while increasing the # of accounts from 20 to 30
Although not quantitative, it seems to load much faster and update its calcs substantially quicker when I change something.
I am in process of updating my initial post with more details, screenshots, and guidance. Please check there.
I appreciate any feedback or questions anyone has.
I just updated the spreadsheet to allow 30 accounts and it also includes reinvested dividends for your comparison tickers if you subscribe to WiseSheets.
You mention you had to increase the Set up and Calcs column count to 556 to support your 28 accounts. That was the issue with the previous versions. This version accepts 30 accounts in 183 columns. Should run substantially faster as well.
Details are in my initial post.
Let me know if you have any questions or feedback.
Thanks for continuing to develop this and letting me know Iāll have a look at your update. My modifications to have 2.1 worked well so I havenāt revisited since.
Understood and let me know if you have any suggestions.
The lack of including dividends in the comparison ticker has bugged me for a while. I still canāt find a free solution gives historical dividends , but Wisesheets can do it for $60 a year for those who want to pay for it. Thanks for the suggestion.
On the # of accounts, I need to rearchitect it to allow more accounts and make it easier to add more accounts. I would think 30 accounts should be enough but if not, it is pretty easy to increase. It now only adds 3 columns for to extend to 31 accounts, 6 for 32, etc.
I spent a minute with it last night. One problem i had was with difficulty getting the Balance History tab to fill I rewrote the code to bypass the reference to the tiller connection tab. I always felt the tiller convention was a little painful and counterintuitive for me so I rewrote:
āSelect Col1, Col3, Col8 Where Col12 = āAssetāā
And it filled.
The other thing i noticed was that because my first balance was on Jan 1 24 and i originally set up the spreadsheet with cash flow deposits on Dec 31 23, the new version of the sheet reports returns for 23 as -100% and returns for 24 as +248%. The previous version was more reasonable, reporting ā23 as 0% returns and 24 as 19%. I may have to jiggle the dates and times on deposits a little bit to make it look right again. I suspect its because the way you coded Setup and Calcs looks totally different and looks way more efficient.
Not sure if i want to pay a yearly fee just to get a comparison with dividends. I generally use index funds so what i have done is track 1/3/12, YTD returns of a vanguard fund with a similar allocation to mine. I can live with that.
All in all just a great sheet. One of a kind. Nice work.
Thanks for the feedback and the encouragement. I like the learning and I use the spreadsheet.
Not sure why the Balance History did not import, but great that you got it working.
As for the first Transaction vs first Balance dates; I made so many changes, I was concerned something like that may happen. It can be easily fixed by adding a balance in your Balance History equal to the initial deposit on 12/31/23. Since you made the deposit that day, it had a balance that day. Also, Iāll let you know if I update it to account for your scenario.
