I have a spreadsheet in which I track the unitized progress of three separate investment accounts (all made up of funds, bonds, etc). I think I'm using the Time-Weighted Return method described here or here.
This works fine: I "buy" more units when I put money into an account, based on the account's current value. And at the end of each month I work out the current value each account's units based on the value of everything in it.
I would like to track a single overall unit value, taking into account all three accounts. I could do it like I do above, treating the overall total as if it was a single account, "buy" units when I put money into one of the accounts, etc.
BUT, seeing as I've already done this for each account, is there a simpler method to create some kind of weighted average from the data I already have at the end of each month?
I'm guessing something like this, to calculate the value of an overall unit at a given point in time, but I don't know if this is accurate:
( (Fund1Total$ * Fund1UnitValue) + (Fund2Total$ * Fund2UnitValue) + (Fund3Total$ * Fund3UnitValue) ) / (Fund1Total$ + Fund2Total$ + Fund3Total$)
UPDATE: After playing around with spreadsheets for a bit I think this might be closer:
( (Fund1NumUnits * Fund1UnitValue) + (Fund2NumUnits * Fund2UnitValue) + (Fund3NumUnits * Fund3UnitValue) ) / (Fund1NumUnits + Fund2NumUnits + Fund3NumUnits)
I'm trying to get the average, weighted unit value, so using the total numbers of units, rather than total monetary value, seems more likely. I think I've verified this is correct with some example figures, without paying any money into the accounts. But once I start doing that I can't figure out how to confirm this calculation is accurate.