How to create an average of several unitized accounts?

Question

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.

Answer 1

Your second equation looks ok.

If I understand the question (I for one, do better with actual numbers in an example), you simply want to combine average returns, keeping mindful of the weighted average.

If I have 2 accounts, returning 10% and 12% respectively, the average is 11% if and only if, they have the same value. Say the 10% acct is $100K and the 12% acct is $200K. The second acct is 2/3 the size of the total, 2/3 * 12 + 1/3 * 10 = 11.33%. And we'd expect that, as the 12% acct was more than half the portfolio. This matched the second example you edited in.