IL662 Fields

Month end date

• Relates to report criteria prompt, “Month end date”
• This is a user-specified date which is entered on the report criteria screen
• This must be a month-end date
• In this document, “Month end date” will be referred to as Base Date

Include 13th period

• Relates to report criteria prompt, “Include 13th period”
• This reporting criteria field determines if G/L Period 13 should be considered when calculating the general ledger “Lines of Credit” balance and “Total Assets”

Reporting timeframes

• This report will generate information for a 37-month timeframe starting from the user-specified “Month end date”
• The 37-month period will be divided into the following categories:
  • 1 Month (i.e. Base Date + 1-day) Example when Base Date = 03/31/2013: “1 Month” will be 04/01/2013 thru 04/30/2013
  • 2-6 Months
  • 7-12 Months
  • 13-24 Months
  • 25-36 Months
  • 37+ Months

REPORT SECTION: INTEREST EARNING ASSETS

Investment securities:

• Relates to report criteria prompt, “Securities”
• User selection options: Portfolio Types
• If no criteria is specified for “Securities” or if no active portfolios exist for the specified “Securities” (i.e. Portfolio Types), no information will be generated for the Investment securities categories

The following portfolios will be reviewed for inclusion in this report:

• Portfolios having a status code of: 1 (active)
• Portfolios having a Type that is listed in the “Securities” reporting criteria

Initial portfolio calculations (simulated):

• Portfolio Maturity Date – If the portfolio maturity date is less than the Base Date + 1-day, then the portfolio maturity date will be set to the Base Date + 1-day.

Reporting Portfolio value:

• Portfolio Face Value – The face value of the portfolio will be reported in the appropriate category based on maturity date
  • If the portfolio is still active and the maturity date is less than the Base Date + 1-day, then the portfolio value will be reported in the first category (1 Month)
  • If the portfolio maturity date is greater than 36 months, the portfolio value will be reported in the last category (37+ Months)

CD’s and commercial paper:

• Relates to report criteria prompt, “Commercial”
• User selection options: Portfolio Types
  • If no criteria is specified for “Commercial” or if no active portfolios exist for the specified “Commercial” (i.e. Portfolio Types), no information will be generated for the CD’s and commercial paper categories

The following portfolios will be reviewed for inclusion in this report:

• Portfolios having a status code of: 1 (active)
• Portfolios having a Type that is listed in the “Commercial” reporting criteria

Initial portfolio calculations (simulated):

• Portfolio Maturity Date – If the portfolio maturity date is less than the Base Date + 1-day, then the portfolio maturity date will be set to the Base Date + 1-day

Reporting Portfolio value:

• Portfolio Face Value – The face value of the portfolio will be reported in the appropriate category based on maturity date
  • If the portfolio is still active and the maturity date is less than the Base Date + 1-day, then the portfolio value will be reported in the first category (1 Month).
  • If the portfolio maturity date is greater than 36 months, the portfolio value will be reported in the last category (37+ Months)

Interest bearing cash:

• Relates to report criteria prompt, “Interest bearing”
• User selection options: Portfolio Types
• If no criteria is specified for “Interest bearing” or if no active portfolios exist for the specified “Interest bearing” (i.e. Portfolio Types), no information will be generated for the Interest bearing cash categories

The following portfolios will be reviewed for inclusion in this report:

• Portfolios having a status code of: 1 (active)
• Portfolios having a Type that is listed in the “Interest bearing” reporting criteria

Initial portfolio calculations (simulated):

• Portfolio Maturity Date – If the portfolio maturity date is less than the Base Date + 1-day, then the portfolio maturity date will be set to the Base Date + 1-day

Reporting Portfolio value:

• Portfolio Face Value – The face value of the portfolio will be reported in the appropriate category based on maturity date
  • If the portfolio is still active and the maturity date is less than the Base Date + 1-day, then the portfolio value will be reported in the first category (1 Month).
  • If the portfolio maturity date is greater than 36 months, the portfolio value will be reported in the last category (37+ Months)

Loans:

The following loans will be reviewed for inclusion in this report:

• Loans which are active as of the Base Date
• Loans having one of the following loan status codes as of the Base Date: 01, 02, 11, or 12

Initial loan calculations (simulated):

• Loan Adjustment Date – If the loan adjustment date is less than the Base Date + 1-day, then the loan adjustment date will be set to the Base Date + 1-day
• Accrued Interest – Accrued interest for the loan will be calculated up to the Base Date and the Last Interest Date will be set to the Base Date
• Payment Due Date – If the payment due date is blank, the payment due date will be set to the Base Date

Loan amortization date:

• Amortization date - The date that all loans will be amortized through will be assigned as: Base Date + 1 day PLUS 37 months

Calculations:

• Summary:
  • Amortize interest up through the 37th month after the base date
  • Mimic the receipt of a regularly scheduled loan payment in order to more accurately reflect the amount of interest income generated throughout the life of the loan
• Critical information: Payment due date, Payment code, First payment due date, Loan status
• Advance the Payment Due Date: If the current payment due date is less than the Base Date + 1-day, perform calculations to advance the Payment Due Date until it exceeds the Base Date + 1-day. Change interest-only loans to amortized once the first payment date is reached
• Loan Amortization: This process will estimate if/when the loan principal is collected
  • Calculations will be based on the amortization period of Base Date + 1-day through the Amortization Date (i.e. 37-month timeframe)
  • During the loan amortization process, if the amortization date equals the loan adjustment date the amortization process will end
  • Loan Interest will be accrued at the end of each month during the amortization timeframe
  • Payments will be mimicked during the amortization timeframe. If the loan status is 11 or 12 (interest-only loan), the loan payment will be estimated as the Interest Due at that time. If the loan status is 01 or 02 (regular loan), the loan payment will be estimated as the minimum amount of: (a) Payment Amount, or (b) Loan Balance + Interest Due
  • Loan Status will be reviewed during each iteration of the amortization process. Once an interest-only loan reaches the First Payment Due Date, it will be changed to a regular loan (01 or 02) until the amortization process has completed
  • Loan Balance will be reviewed during each iteration of the amortization process, and if the loan balance reaches zero the amortization process will end for this loan
  • Once the amortization process has completed, any remaining loan balance will be placed in the 37+ Months category

Line of credit loans:

The following LOC loans will be reviewed for inclusion in this report:

• LOCs which are active as of the Base Date
• LOCs with a Credit Balance greater than zero as of the Base Date
• If the LOC is not active or does not have a Credit Balance greater than zero as of the Base Date, the LOC will be excluded from further processing

LOC Reporting:

• Summary:
  • Search for line of credit loans with a positive balance as of the base date
  • Assume the line of credit balance will be collected by the next adjustment date
• Critical information: Next adjustment date, Credit balance
• Each LOC loan will have the total Credit Balance reported in the appropriate category based on the Next Adjustment Date

Total (Interest Earning Assets):

• A total will be calculated for each of the categories, including the “Total” column.

REPORT SECTION: INTEREST BEARING LIABILITIES

Demand notes:

• Relates to report criteria prompt, “Demand notes”
• User selection options: Investment Categories
• If no criteria is specified for “Demand notes” or if no active investments exist for the specified “Demand notes” (i.e. Investment Categories), no information will be generated for the Demand notes categories

The following investments will be reviewed for inclusion in this report:

• Investments having a status code of: 01 (active)
• Investments having a Term Category that is listed in the “Demand notes” reporting criteria

Initial investment calculations (simulated):

• Investment Maturity Date – If the investment maturity date is less than the Base Date + 1-day, then the investment maturity date will be set to the Base Date + 1-day

Reporting Investment value:

• Investment Principal Balance – The principal balance of the investment will be reported in the appropriate category based on maturity date
  • If the investment is still active and the maturity date is less than the Base Date + 1-day, then the investment principal balance will be reported in the first category (1 Month)
  • If the investment maturity date is greater than 36 months, the investment principal balance will be reported in the last category (37+ Months)

Investment notes:

• Relates to report criteria prompt, “Investment notes”
• User selection options: Investment Categories
• If no criteria is specified for “Investment notes” or if no active investments exist for the specified “Investment notes” (i.e. Investment Categories), no information will be generated for the Investment notes categories

The following investments will be reviewed for inclusion in this report:

• Investments having a status code of: 01 (active)
• Investments having a Term Category that is listed in the “Investment notes” reporting criteria

Initial investment calculations (simulated):

• Investment Maturity Date – If the investment maturity date is less than the Base Date + 1-day, then the investment maturity date will be set to the Base Date + 1-day

Reporting Investment value:

• Investment Principal Balance – The principal balance of the investment will be reported in the appropriate category based on maturity date
  • If the investment is still active and the maturity date is less than the Base Date + 1-day, then the investment principal balance will be reported in the first category (1 Month)
  • If the investment maturity date is greater than 36 months, the investment principal balance will be reported in the last category (37+ Months)

Flexible rate notes:

• Relates to report criteria prompt, “Flex notes”
• User selection options: Investment Categories
• If no criteria is specified for “Flex notes” or if no active investments exist for the specified “Flex notes” (i.e. Investment Categories), no information will be generated for the Flexible rate notes categories

The following investments will be reviewed for inclusion in this report:

• Investments having a status code of: 01 (active)
• Investments having a Term Category that is listed in the “Flex notes” reporting criteria.

Initial investment calculations (simulated):

• Investment Maturity Date – If the investment maturity date is less than the Base Date + 1-day, then the investment maturity date will be set to the Base Date + 1-day

Reporting Investment value:

• Investment Principal Balance – The principal balance of the investment will be reported in the appropriate category based on maturity date
  • If the investment is still active and the maturity date is less than the Base Date + 1-day, then the investment principal balance will be reported in the first category (1 Month)
  • If the investment maturity date is greater than 36 months, the investment principal balance will be reported in the last category (37+ Months)

Lines of credit:

• Relates to report criteria prompts, “Lines of credit G/L Segment Selections”
• If no criteria is specified for “Lines of credit G/L Segment Selections” or if no active general ledger accounts exist for the specified “Lines of credit G/L Segment Selections”, no information will be generated for the Lines of credit categories
• If the reporting criteria for “Include 13th period” is active, the general ledger 13th period will be included in calculations

Calculations:

• General Ledger accounts matching the criteria specified for “Lines of credit G/L Segment Selections” will be included in the calculations
• Calculate the total amount for the current fiscal year and period
• The total account balance for all general ledger accounts meeting the specified criteria will be reported in the first category (i.e. “1 Month”)

Total (Interest Bearing Liabilities):

• A total will be calculated for each of the categories, including the “Total” column

REPORT SECTION: PERIOD GAP, CUMULATIVE GAP, TOTAL ASSETS, PERIOD GAP TO TOTAL ASSETS, CUMULATIVE GAP TO TOTAL ASSETS, CUMULATIVE INTEREST-EARNING TO CUMULATIVE INTEREST BEARING

Period Gap:

• Calculations:
• For each of the categories, subtract the Liabilities Total from the Assets Total
  • Period Gap (1 Month) = Assets Total (1 Month) – Liabilities Total (1 Month)
  • Period Gap (2-6 Months) = Assets Total (2-6 Months) – Liabilities Total (2-6 Months)
  • Etc…

Cumulative Gap:

• Cumulative Gap is an accumulation of Period Gap amounts for each of the categories

Calculations:

• Cumulative Gap (1 Month) = Period Gap (1 Month)
• Cumulative Gap (2-6 Months) = Period Gap (1 Month) + Period Gap (2-6 Months)
• Cumulative Gap (7-12 Months) = Period Gap (1 Month) + Period Gap (2-6 Months) + Period Gap (7-12 Months)
• Etc…

Total Assets:

Calculations:

• Calculate the total amount of all General Ledger ASSET accounts for the current fiscal year and period
• The total account balance will be reported in the first category (i.e. “1 Month”)
• If the reporting criteria for “Include 13th period” is active, the general ledger 13th period will be included in calculations

Period Gap to Total Assets:

Calculations:

• For each of the reporting categories, divide “Period Gap” by “Total Assets” and multiple the result by 100 to get a percentage.
  • Period Gap to Total Assets (1 Month) = Period Gap (1 Month) / Total Assets (1 Month)*100
  • Period Gap to Total Assets (2-6 Months) = Period Gap (2-6 Months) / Total Assets*100
  • Etc…

Cumulative Gap to Total Assets:

Calculations:

• For each of the reporting categories, divide “Cumulative Gap” by “Total Assets” and multiple the result by 100 to get a percentage
  • Cumulative Gap to Total Assets (1 Month) = Cumulative Gap (1 Month) / Total Assets (1 Month)*100
  • Cumulative Gap to Total Assets (2-6 Months) = Cumulative Gap (2-6 Months) / Total Assets*100
  • Etc…

Cumulative Interest-Earning to Cumulative Interest-Bearing:

• Cumulative Gap is an accumulation of Asset totals for each category DIVIDED by Liabilities totals for each category MULTIPLIED BY 100 to get a percentage.

Calculations:

• Cumulative IE to Cumulative IB (1 Month) = Assets total (1 Month) / Liabilities total (1 Month)*100
• Cumulative IE to Cumulative IB (2-6 Months) = Assets total (1 Month + 2-6 Months) / Liabilities total (1 Month + 2-6 Months)*100
• Cumulative IE to Cumulative IB (7-12 Months) = Assets total (1 Month + 2-6 Months + 7-12 Months) / Liabilities total (1 Month + 2-6 Months + 7-12 Months}*100
• Etc.