3

I have a Google Sheet which consolidates multiple funds in which I am invested. I also track my trades in the same sheet and update the profits to track XIRR returns overall.

This is working fine for tracking purpose, however, now I have a need where multiple family members would invest (add funds) and the NAV is calculated accordingly. Next, when any of the family member decides to withdraw the NAV is settled. I tried to google but all I can find is NAV associated with Mutual Funds only.

I need some guidance in how to go about this.

Improve this question
7
  • What do you mean by "NAV"?
    – littleadv
    Commented Nov 15, 2023 at 4:37
  • NAV - Net Asset Value. My bad, I should have mentioned.
    – Raj Pawan Gumdal
    Commented Nov 15, 2023 at 4:59
  • 1
    What does it have to do with family members investing?
    – littleadv
    Commented Nov 15, 2023 at 5:22
  • 1
    I mean there will be x number of people adding money into the funds and dis-investing at any point they will, excel should calculate and keep track the growth of the individual's contribution at any point of time. I am not sure if I am explaining it right but what I want is to maintain a mini Mutual fund myself in which people can add money any time and withdraw their share at any point of time.
    – Raj Pawan Gumdal
    Commented Nov 15, 2023 at 5:29
  • 4
    NAV is "Net Asset Value", which is the value of all the holdings of the fund minus all its liabilities. What you're talking about is investment accounting where you need to track the basis of each of your investors and allocate gains and losses accordingly. I suggest talking to an accountant to set up a proper system, otherwise it will be a mess
    – littleadv
    Commented Nov 15, 2023 at 5:48

2 Answers 2

Reset to default
13

This is working fine for tracking purpose, however, now I have a need where multiple family members would invest (add funds) and the NAV is calculated accordingly. Next, when any of the family member decides to withdraw the NAV is settled. I tried to google but all I can find is NAV associated with Mutual Funds only.

The problem you are now facing is you are starting to act like a investment company. The complexity of the calculations is now ramping up. Dividends, interest, and capital gains have to be divided and accounted for. Because you are handling money from other people and investing it for them, you could now face tax issues.

You also have to realize that if a family member thinks you did it wrong, then they will complain, or worse.

You might want to sit down with an accountant and tax expert related to investment clubs to see what the next step is.

Improve this answer
9

I tried to google but all I can find is NAV associated with Mutual Funds only.

Well that's essentially what you're creating so that research may be completely appropriate.

You will need to separate out your performance tracking (your XIRR sheet) from ownership. In a mutual fund, the performance of individual investments is not tracked separately - everyone shares in the overall performance of the fund equally. If the fund overall goes up 10%, everyone's investment goes up 10%.

If that is not your intent, then everyone's contributions and investments need to be tracked completely separately, and it may be best to create separate brokerage accounts for each investor.

Say your fund is currently worth $10,000 (the NAV) and a family member wants to "invest" $5,000 with you. The fund is now worth (has a NAV of) $15,000. They now own 5,000/15,000 or 33.3% of the fund. You own the remaining 2/3 (which is still $10k).

Now the next question is: what do you do with the $5,000? That's an independent decision that you must make. Do you just scale up what you're already doing? You don't even need to invest the $5,000 separately - it is now intermixed with the rest of the funds and all profits/losses are shared proportionally. If you buy a bad stock with that $5k and it goes bust, the entire fund lost $5k but each owner only loses their portion of it. They still own 1/3 of the fund.

The family member should not care about the intricacies of your XIRR calculation. All they care about is how much their $5,000 grows. They may care about what you invested in (i.e. do you have an investing strategy that you follow with all funds?) but the minutia of how each stock performs is largely irrelevant. You care about it to see how your individual choices have performed, but they just care about the size of the pot.

Now suppose the total value of investments in the fund grows to $24,000? They still own 1/3 of the fund, or $8k. It does not matter how "their" $5k performed, it is all one big shared pot.

If another family member wants to invest, repeat. They get a proportional ownership and share all profits/losses proportionally.

When someone decides to sell, then you need to make a decision on how to get them their money. Unless you have enough excess cash, you will need to decide what investments to sell. Again, you don't necessarily have to sell what you bought with their money, it's all one big pot, and they will largely be indifferent to what you actually sell.

I am not addressing the legal, tax, and relational aspects of all of this, just dealing with the mathematics of a shared investment fund.

Improve this answer
2
  • Thanks for the details, I am completely ignoring the taxation part here. The profits to be considered here is post taxation for all the members and hence I would not be worrying about tax at this moment. However, I understand your calculation, but my confusion lies in the fact that person 1 might be invested since the beginning but person 2 joins very late with some investment, so how is the period of holding justified here for the profit share? Thanks.
    – Raj Pawan Gumdal
    Commented Nov 21, 2023 at 7:03
  • 1
    Their profit would be the gain in the fund since they bought shares. So the second investor would share in the gain of the fund since the time that they bought shares. Just like when you buy into a mutual fund, you have a unit price that you bought at and a unit price that you sold at. That unit price is based on the NAV at the time of purchase.
    – D Stanley
    Commented Nov 21, 2023 at 14:24

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .