# How to create an average of several unitized accounts?

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.

• The shareware software KBH Investor Accounting has an overall mark-to-market accounting that uses a Modified Dietz within a yearly period but accounted year-to-date. That accounting just sets the gain/loss against an average balance but is very sensible. – S Spring Feb 1 '19 at 18:14