Data modeling to represent mutual fund investments

[sriram2520]

I am looking to add my investments into mutual funds and track the growth via Maybe.
I am providing how the mutual fund industry works in India (maybe its the same in other parts of the world)

Primer
Mutual fund is an investment category and not an account by itself. It is considered as a marketable security in most countries. Mutual fund pools money from multiple investors and a "Fund Manager" deploys the funds in various asset classes.

Asset Management Companies (AMCs) creates and manages a mutual fund. A single AMC can offer multiple mutual funds.
While investing in mutual fund, the AMC create a folio number or account number for the investor.
Some AMCs creates unique folio for each fund and some AMCs consolidate all funds inside a single folio.
While its not necessary, most mutual funds have a unique ISIN (International Securities Indentification Number).
Once invested, the investor is allotted a particual amount of "units" in the mutual fund.
Each mutual fund has a NAV (Net Asset Value), denominated at a unit level.
Total current value of investor = NAV * Units

Data Modeling
The following information are needed to track the mutual fund investments of an investor.
Folio Level

1. Folio Number
2. ISIN (optional)
3. Units Held
4. Latest NAV
5. NAV date
6. Current Value (as on NAV date)

Transaction Level

1. Account ID
2. Transaction Type (buy/sell or credit/debit)
3. Amount (invested amount or withdrawn amount)
4. Units (purchase units or sold units)
5. NAV (NAV at which the fund was bought for or sold at)
6. Transaction Date

How do we represent this information with the existing db models we have?

[zachgoll]

The short answer:

With the current data model, this is not possible. That said, we do plan to fully support this soon and is a high priority (once "core" is completed)

The long answer:

The way this would be represented in the future is with the following models:

• Account
  • Account::Investment (delegated type)
• Holding
• Security
• Transaction

While a lot of this would be on a case by case basis, typically, the Account would represent a single investment account that is invested in one or more mutual funds. An Account::Investment would be the delegated type, and we'd probably throw a subtype of mutual_fund to further identify it.

The mutual fund itself would be represented by a Security, or in the case the AMC doesn't have an ISIN, it may be a collection of Securitys. These would store the ISIN, NAV, and other security specific info.

How much you own of the mutual fund would be stored in Holding, which has a FK to Security and stores things like "shares held" and "cost basis".

Finally, individual transactions within this (buys/sells) would be a collection of Transaction with a type of trade, which would be a Rails "STI" type. Similar to our current "signed" convention where a negative transaction amount represents an "inflow" to an account, we'd designate a "buy" as a Transaction with -qty and type == trade (or something along those lines)

[followingell]

@zachgoll Thanks for such a detailed response.

I was about to ask if mutual funds are supported. Do you have an ETA for this or a Projects card I can follow (I tried searching but nothing came up).

Thank you!

[zachgoll]

@followingell everything I had talked about above has been implemented at this point, so you should be able to create an "Investment" account type and add mutual funds as "holdings" to that portfolio.

We've still got some work to do around portfolios, but the barebones support is there already.

[followingell]

Hey @zachgoll,

I tried what you mention above for the Vanguard Global Stock Index Fund Institutional Plus EUR Acc (Ticker Symbol IE00BFPM9N11) and I unfortunately come across the following error:

Missing data: This investment has missing values and we could not calculate its returns or value..

[zachgoll]

@followingell it looks like Synth does not have support for this ticker, which is why you're getting this error.

@Shpigford will Synth support securities such as IE00BFPM9N11? What is our recommendation for something like this?

[Shpigford]

@zachgoll @followingell Right now we've only got support for US-based securities.