0

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 at 18:14
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.

  • Thanks JoeTaxpayer. I think the only reason I'm getting confused is the complication of unitization, when adding contributions to the funds over time. But I'm not sure that really affects this aggregation, so I'm probably confusing myself unnecessarily. – Phil Gyford Feb 2 at 17:29
  • Yes, I thought about that. The time issue is resolved in the first calculation. When you tell me you got 10% in 2018 and the year end value, it's already accounted for. – JoeTaxpayer Feb 2 at 17:32

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.