Populate the NAV Table
- Pamela Totas (Deactivated)
The first step toward implementing the system for calculating mutual fund returns is to develop an uploader for the NAV and distribution history and populate the NAV table for this data each day going forward.
The minimum data requirements for loading the NAV table are:
Entity Identifier
Unique per share class. If multiple share classes have the same ID and are distinguished only by a class letter on the file, the uploader can use the ID in conjunction with the share class to derive a unique ID.
Class Level
Cannot be null in database, but not required for Performance.
Effective Date
NAV
Split Ratio
Required for calculation; if none, maintain as “1.”
Income distributions on a per share basis
Capital Gain Distributions
Short, mid, long; on a per share basis.
Reinvestment Dates for each distribution
There could be more than one reinvest assumption.
You may also need some of these fields for calculating returns or to report along with the returns:
Other distribution types on a per share basis (such as: Mid-term capital gains, Tax-exempt income, REIT Income, foreign tax credits, returns of capital, and so on.) – the file layout (thus uploader) can be set up to handle infrequent distribution types. For example, taxable repurchase agreement interest, foreign tax credits, and returns of capital might only occur once per year. The file and the uploader could be set to accommodate any infrequent distribution type. (If this is not feasible, you can use the NAV maintenance tool for reclassifications).
Mid-term capital gains. Currently, there is no mid-term tax tier. Therefore, there are no mid-term capital gains. However, the loading of historical data contains instances of these gain types.
Dividend earning shares. This is common data on many reports. However, it can also have use in the calculation process. Dynamic calculations rely on per share data. If a file can only provide whole dollar amounts of the various distributions, the uploader can be coded to use the dividend earning shares to calculate the per share amounts (Dollar Amount / dividend earning shares).
Fund expenses and subsidies/waivers (per share). All expense and subsidy data should be stored on a daily per-share basis. Expenses are stored as a positive value. The return calculator is responsible for adding or subtracting as required.
Market price (for closed end funds). The market price (for closed-end funds) would have to be provided on a file. If a market price exists, insert into one of the decimal columns not being used to store NAVs.
Offer price (not required; calculate in uploader if required for reporting). An offer price is only used on the first day of a performance period to determine appropriate shares for a front-end loaded fund. Eagle PACE automatically derives this offer price based on the NAV per share and the front-end load. Therefore, the offer price is not required to be stored on the database. If the offer price, however, is part of any downstream report, it must be stored in the database. If the offer price is part of the daily file, it can be inserted. If not, the uploader can be programmed to calculate and insert by:
(NAV / (1-Front End Load)). Where the front-end load would be stored on the ENTITY table
Prior day (month) NAV. Derive in uploader if required for reporting
Subscriptions/Redemptions. Dollars, shares, or both
Reinvestment Dates
Each distribution amount should be linked to a reinvestment date. The database is designed to have a dedicated reinvestment date column for each distribution type. Reinvestment date data can be stored on any date field on the NAV table including ten user date fields. Many distribution types have the same reinvestment date assumptions. Therefore only one reinvestment date column would need to be maintained. However, in some instances (for example, daily dividend funds) some distribution types may have a different reinvestment date assumptions. These distributions would need a dedicated column.
For example, on a particular day, for example June 15, 2017, a daily dividend fund might declare two capital gains distributions that reinvest on June 15. It might also declare an ordinary income distribution on June 15th that reinvests on June 30th. Because both the long and short term capital gain reinvests on ex-date, it is only necessary to maintain one reinvestment date. However, the behavior of the income distribution is such that it does not reinvest until the end of the month (at which point the NAV is likely different from June 15th thus impacting Performance).
It is important to analyze the reinvestment date assumptions for each distribution type before mapping the uploaders. If distributions always follow the same reinvestment pattern, you only need to maintain one date field. If the reinvestment patterns may differ at some point in time, you should maintain multiple columns to support each assumed pattern. There are enough date/time fields in the database to support a unique reinvestment date field for each distribution type.
Daily Records Required
Daily record requirements include the following:
A NAV record required for each performance period: begin, end, and reinvestment date.
A NAV is required for each reinvestment date, including weekends and holidays. For example, if a field is defined as a Month to date return, the process uses the NAV from the last day of the prior month as the begin NAV of the calculation. If the prior month end was a weekend and is missing a NAV, the return cannot be calculated.
The business calendar can be used to determine non-business days so that the uploader can create weekend and holiday records.
The Price Table
You may already be loading this data into the PRICE table. If so, you can convert the history via a stored procedure, trigger, or exporter to move the data to the NAV table:
Use existing translations to derive an entity_id from the security alias.
Fetch component data for each unique combination of security, source, and date.
For each record, replace security_alias with entity_id and insert to .NAV.
Split Ratio
Split ratio is a required data element for dynamic processing. For non-split funds this can be set to “1” for all historic data. If a fund has a split, be sure to maintain the split ratio as required. For example, if a fund had a 2:1 split on 6/30/2017, all records for dates greater than or equal to 6/30/17 need a split ratio of “2” (2:1). If the same fund has a second 2:1 split on 6/30/2018, the forward ratio must be maintained as 4 [(2/1) x (2/1)].
Distributions
Each distribution must have an associated reinvestment date. If the reinvestment date is not available from the source data or equal to ex-date, you can derive the reinvestment date via entity characteristics that indicate the reinvestment assumption.
Capital gains must be broken down into correct classification (short/mid/long/income, and so on).
The NAV Table
The per/share data should be stored on any decimal-type column on the NAV table. There are several columns available for the most common distributions. There are supplemental columns on the NAV table that can be used to store per share data.
Use Entity Characteristics to Derive Required Data
Entity characteristics can be built in support of uploader initiatives. For example, assume a market price only needs to be inserted for closed end funds. An entity characteristic can be built to indicate closed versus open-ended funds. Uploaders can then be set up to use these characteristics:
If fund 123 = closed end Then Insert market price to column x.
Daily Dividend Funds
Daily dividend funds have some dates when there are multiple days of income posted to a record. First, determine if it is required to spread the multi day income factors. Secondly, the specific method of spreading should be identified. Lastly, uploaders can be built to use a combination of income calendars and characteristics to implement the correct spreading methodology. For example (in pseudo code):
If fund 123 is a daily fund
And
Fund 123 spreads forward (from entity characteristics)
And
Today is a business day
And
The next 2 days are NOT business days (based on a source specific business calendar)
Then
Take the income amount and divide by 3
Create records for day 1-3
Insert value into day1, day2 and day3
Else
Insert whole value into Day1 record
Business Calendars
Although not required, uploaders can be built to automate the creation and support of one or more business calendars. This way, a spreadsheet can be created and used as a record of the business dates for any range of time. If the business date assumptions ever change, the spreadsheet can be edited and reloaded to Eagle PACE. This would also allow for many years of dates to be entered all at once.
Entity Data
To support the uploaders, it is helpful to maintain these entity characteristics:
Open versus closed end indicator
Daily versus periodic dividends
Daily versus monthly pricing
Reinvestment date assumption (month end, specific date)
Income spreading method (forwards, backwards, split month/year end)
Support for Contingent Deferred Sales Charges
The entity database and dynamic mutual fund returns in PACE Mutual Fund Performance support funds with up to 10 years of contingent deferred sales charges (CDSC). The following columns in the ENTITY and ENTITY_HIST tables support this feature:
CDSC_LOAD_YEAR_7
CDSC_LOAD_YEAR_8
CDSC_LOAD_YEAR_9
CDSC_LOAD_YEAR_10
You can edit and view the columns in the entity maintenance windows (based on user security) in the same manner as other columns.
These columns support leap years, optional use of minimum NAV or begin NAV method (for calculating the fee amount), and are also supported in the optional use of the anniversary date rollover feature.