Net Worth & balance history - formula to pull the dates and balances by account from the balance history tab

Would like to pull dates and balances from balance history sheet by account from on the Google Sheets Found. template to a new page and do some analysis of my investment accounts (XIRR so cash in/out can be looked at). Could someone help me with a formula to pull the dates and balances by account from the balance history tab?

@ty9895

Just thinking down the road a bit here. If you are back testing, the XIRR calculation will need two FMV’s for the beginning and ending dates. Everything in between will be cash flow (in and out) numbers. How do you plan to get this data? You can do this in Tiller by using various Transfer categories to capture this data. How much you have to deal with this depends upon the investments (and activity) in the accounts. Let me know if you want to discuss further.

Cheers,

Blake

See links for background information on XIRR.

Hi @ty9895,
I hope @Blake 's reply helped you with your request.

There are ways of making a pivot table (both by formulas or Data then Tools menu) of the Account Balances by Dates.

But, you might run into a problem because not every account reports a balance every day. So there will be holes in the account balance.

I briefly looked into trying to fix this with a formula, but I didn’t find a quick path to do it. I’m sure it could be done using a custom Google Scripts.

Jon

Thanks, Blake! Yes, I think once I get the balances down that the cash flow portion could be done by using the Transfer categories and potentially tags for each account. Also, thanks for the additional info on the XIRR f(x).

Thanks, Jon! Was thinking a pivot table maybe could be an option but not sure if I would need a pivot table to capture each account so the formulas would not be as messy? Guess a custom script might be the best option.

1 Like