loan_mgmt:onepage

Investment Types

There are currently five different investment types available to choose from when editing a Loan Type:

  1. Mortgage (Mtg): traditional mortgages
  2. Security/MBS (MBS): Mortgage-Backed Securities, also referred to as mortgage pools
  3. US Gov't & Agency (USGA): debt securities issued by the U.S. Treasury, by U.S. government agencies and by U.S government-sponsored enterprises; also referred to simply as Treasuries
  4. Stock (Stock): Federal Home Loan Bank (FHLB)-restricted and non-public stock
  5. Loan (Loan): Federal Home Loan Bank (FHLB) advances; ie, cash loaned to Citizens from the FHLB
  6. Cert of Deposit (CD): Certificates of Deposit up to $250,000 (i.e., fully FDIC insured)
Mtg MBS USGA Stock Loan CD
Interest Accrual Method 30/360 30/360 Actual 365 Fixed N/A N/A Actual 365 Fixed
Amortizes Premiums/Discounts N/A Yes Yes N/A N/A N/A
Sale Status HTM HTM/AFS/Trading HTM/AFS/Trading N/A N/A AFS
Interest Income Subject to PA Tax? Yes Yes Yes/No Yes N/A Yes

Loan Types

So what's the difference? The easiest way to explain it is to show the relationships among loan records, loan types, and investment types:

Every Loan record has a Loan Type. Every Loan Type has an Investment Type.

The Loan Management system handles five different Investment Types. These Investment Types are hard-coded into the program and cannot be changed by users. They determine how a loan is treated. For instance, the method used to determine how much interest to accrue for a loan is based on the Investment Type: USGA Investment Type loans have their interest calculated using the Actual/365 method, while Mtg Investment Type loans have their interest calculated using the 30/360 method.

The Loan Management system supports an unlimited number of Loan Types. Users (with appropriate permissions) can make changes to existing Loan Types or add new ones. For each Loan Type, users can specify its Investment Type, its abbreviated name, its full description, and its applicable G/L accounts. Users can control the behavior of loans to a limited extent by omitting certain G/L accounts. For instance, if the AFS Principal/Asset GL Account is left blank, then no loans of that Loan Type will be allowed a Sale Status of AFS.

To make changes to any of the fields in the form, you must first click the Edit Info button. Not all users have permission to edit the loan type records. If you do not have permission to edit the records, the Edit Info button will be grayed out.

At the top of the form is a small table that summarizes the break down of all the loans with the selected loan type. This provides a quick snapshot of what loans are currently active in the system and how they are broken down (Taxable vs. Non-Taxable; AFS vs. HTM vs. Trading). In the screen shot below, for example, the MBS loan type is selected. There are 97 active taxable securities and 58 inactive taxable securities. All of the MBS's are taxable. There are currently 41 active AFS securities, 56 active HTM securities, and no active Trading securities. However, there are 16 Trading securities that are no longer active.

Loan Type Form

Loan Type CodeLoan Type DescriptionInvestment TypePayment Debit Principal (Receivable)Payment Debit Interest (Receivable)Payment Debit Prin + Int (Check)Payment Debit Prin + Int (Wire)Cash Account for PurchasesPrincipal/AssetService ExpenseInterest IncomeAccrued InterestUncollectible Interest

Loan Type Code

This is the short code that shows up in the Loan Type dropdown boxes and the Loan Type report control in Report Builder.

Loan Type Description

The full description of the Loan Type. Appears in the group header sections of reports that are grouped by Loan Type.

Investment Type

For more information see the Investment Types article.

Payment Debit Principal (Receivable)

Asset: This account is debited the Principal amount when Receivable receipts are posted. Leaving this account blank prevents receipts of this loan type being entered as Receivables.

Payment Debit Interest (Receivable)

Asset: This account is debited the Interest amount and credited the Service amount when Receivable receipts are posted. Leaving this account blank prevents receipts of this loan type being entered as Receivables.

Payment Debit Prin + Int (Check)

Asset: This account is debited the Principal and Interest amount and credited the Service amount when Check receipts are posted. Leaving this account blank prevents receipts of this loan type being entered as Checks.

Payment Debit Prin + Int (Wire)

Asset: This account is debited the Principal and Interest amount and credited the Service amount when Wire receipts are posted. Leaving this account blank prevents receipts of this loan type being entered as Wires.

Cash Account for Purchases

Asset: This account is credited the Purchase Price and Purchased Interest when loans are added. Leaving this account blank prevents the Loan Management system from posting a 'Security Purchase' batch to the General Ledger when loans are added.

Principal/Asset

These accounts are:

  • credited the Principal amount when Receipts are posted
  • credited the Amortization amount when Premiums are amortized
  • debited the Amortization amount when Discounts are accreted
  • debited the Purchase Price when loans are added (only if the Cash Account GL is also filled in)
HTM

Asset: Leaving this account blank prevents loans of this loan type from being classified with a Sale Status of HTM.

AFS

Asset: Leaving this account blank prevents loans of this loan type from being classified with a Sale Status of AFS.

Trading

Asset: Leaving this account blank prevents loans of this loan type from being classified with a Sale Status of Trading.

Service Expense

Expense: This account is:

  • debited the accrued service fees when Accruals are posted
  • credited any remaining accrued service fees when the Accruals are Reversed out
  • debited the amount of excess Service fees if the Service fees exceed what was previously accrued when Receipts are posted

Leaving this account blank prevents service amounts from being entered when inputting Receipts.

Interest Income

These accounts are:

  • debited the Amortization amount when Premiums are amortized
  • credited the Amortization amount when Discounts are accreted
  • credited up to four months (current month plus 90 days) of accrued interest when Accruals are posted
  • debited any remaining accrued interest when the Accruals are Reversed out
  • debited the amount of accrued interest that is determined uncollectible when Accruals are posted
  • credited any remaining uncollectible interest when the Accruals are Reversed out
  • credited the amount of service fees that correspond with the uncollectible accrued interest when Accruals are posted
  • debited any remaining service fees that correspond with uncollectible accrued interest when the Accruals are Reversed out
  • credited the amount of Interest received on a loan that had previously accrued uncollectible interest (up to the amount of the previously accrued uncollectible interest) when the Receipts are posted
  • credited the amount of excess Interest if the Interest received exceeds what was previously accrued when Receipts are posted
  • debited the amount of Service fees on a loan that had previously accrued service fees corresponding with uncollectible interest (up to the amount of the previously accrued service fees corresponding with uncollectible interest) when the Receipts are posted
Taxable HTM

Income: Leaving this account blank prevents loans of this loan type that are PA Taxable from being classified with a Sale Status of HTM.

Taxable AFS

Income: Leaving this account blank prevents loans of this loan type that are PA Taxable from being classified with a Sale Status of AFS.

Taxable Trading

Income: Leaving this account blank prevents loans of this loan type that are PA Taxable from being classified with a Sale Status of Trading.

Non-Taxable HTM

Income: Leaving this account blank prevents loans of this loan type that are not PA Taxable from being classified with a Sale Status of HTM.

Non-Taxable AFS

Income: Leaving this account blank prevents loans of this loan type that are not PA Taxable from being classified with a Sale Status of AFS.

Non-Taxable Trading

Income: Leaving this account blank prevents loans of this loan type that are not PA Taxable from being classified with a Sale Status of Trading.

Accrued Interest

Asset: This account is:

  • debited up to four months (current month plus 90 days) of accrued interest when Accruals are posted
  • credited any remaining accrued interest when the Accruals are Reversed out
  • credited the accrued service fees when Accruals are posted
  • debited any remaining accrued service fees when the Accruals are Reversed out
  • credited the amount of Interest received on a loan that had previously accrued interest (up to the amount of the previously accrued interest) when the Receipts are posted
  • debited the amount of Service fees on a loan that had previously accrued service fees (up to the amount of the previously accrued service fees) when Receipts are posted
  • debited the amount of Purchased Interest when loans are added (only if the Cash Account GL is also filled in)

Leaving this account blank prevents the loan type from showing up in the Post Reverse Accruals to GL, Calculate Accruals, and Post Accruals to GL dialog boxes.

Uncollectible Interest

Liability: This account is:

  • credited the amount of accrued interest that is determined uncollectible when Accruals are posted
  • debited any remaining uncollectible interest when the Accruals are Reversed out
  • debited the amount of service fees that correspond with the uncollectible accrued interest when Accruals are posted
  • credited any remaining service fees that correspond with uncollectible accrued interest when the Accruals are Reversed out
  • credited the amount of Service fees on a loan that had previously accrued service fees corresponding with uncollectible interest (up to the amount of the previously accrued service fees corresponding with uncollectible interest) when the Receipts are posted

Participants/Investors

Every financial instrument in the Loan Management system must be assigned an Investor Code (also referred to as a Participant Code when the financial instrument is a mortgage). The Investor Codes are used for informational purposes and grouping within Loan Types.

The same Investor Code may be used with multiple loan types and vice versa. For example, the GNMA Investor Code shown in the screenshot below may be assigned to both a USGA Treasury and an MBS.

Loans are grouped by Investor Code when posting the following batches to the General Ledger:

  • Receipts
  • Reverse Accruals
  • Accruals
  • Amortizations

Participant/Investor Form

Investor Code

(1) Short code that is used to identify the participant / investor in dropdowns and on reports throughout the Loan Management program.

Participation Information

(2) The following fields are no longer used. They are maintained here for historical purposes:

  • Participation Date
  • Original Amount
  • CSB Ownership
  • CSB Orig Amount

Contact Information

(3) Contact information is for general reference only. It is not used anywhere else within the program.

Sale Status

This Statement addresses the accounting and reporting for investments in equity securities that have readily determinable fair values and for all investments in debt securities. Those investments are to be classified in three categories and accounted for as follows:

  • Debt securities that the enterprise has the positive intent and ability to hold to maturity are classified as held-to-maturity securities and reported at amortized cost.
  • Debt and equity securities that are bought and held principally for the purpose of selling them in the near term are classified as trading securities and reported at fair value, with unrealized gains and losses included in earnings.
  • Debt and equity securities not classified as either held-to-maturity securities or trading securities are classified as available-for-sale securities and reported at fair value, with unrealized gains and losses excluded from earnings and reported in a separate component of shareholders’ equity.

This Statement does not apply to unsecuritized loans. However, after mortgage loans are converted to mortgage-backed securities, they are subject to its provisions. This Statement supersedes FASB Statement No. 12, Accounting for Certain Marketable Securities, and related Interpretations and amends FASB Statement No. 65, Accounting for Certain Mortgage Banking Activities, to eliminate mortgage-backed securities from its scope.


%fineprint{The full text of this statement is available at: http://www.fasb.org/pdf/aop_FAS115.pdf.}%

Interest Accrual Calculation

StartDate

EndDate:

  • Date the user enters as the Accrual Apply date
  • Last day of the month by convention
NumDaysToAccrue = EndDate - StartDate + 1

IF Feb 29 falls between StartDate and EndDate THEN subtract 1 from NumDaysToAccrue (Actual/365 calculation treats every year as though it had 365 days)

OriginalValue: Face value of the bond

Interest: Coupon rate as displayed on Loan Form (ie, 4.25% is 4.25, not 0.0425 or 1.0425)

Accrued Interest = Round(OriginalValue * Interest * NumDaysToAccrue / 36500, 2)

The accrued interest is calculated a month at a time to allow for proper handling of uncollectible interest.

Starting with the StartDate calculated previously, repeat the following steps for each month until EndDate is reached.

CurrDate: Current date

  • Use last day of current month as the current date
  • Use Feb 28 instead of Feb 29 (ie, ignore leap years)

EffIntRate: Effective interest rate

  • Remains the same for fixed rate loans
  • May change from one month to the next for adjustable rate loans

NextPmtAmtDue: Next payment amount due

  • For adjustable rate loans, users have the ability to enter future payment amounts; if there are entries for the loan being processed, the system will use them
  • This amount is used to predict drawdown of the principal balance

RemBal: Remaining principal balance

  • Add back in any principal payments received between now and CurrDate to get the remaining principal balance for the instrument as of CurrDate

IntAccrued: Interest accrued this month

IntAccrued = RemBal * EffIntRate / 1200

Draw down balance by assuming timely payments

PrinPmtExpected = NextPmtAmtDue – Round(IntAccrued, 2)
RemBal = RemBal – PrinPmtExpected
  • If PrinPmtExpected or RemBal are negative, set them equal to zero

IF we are in the month this instrument was purchased and the overall start date (as calculated above) is after the first of the month then:

PctOfMonth: percentage of month to calculate interest accrual

PctOfMonth = (30 – StartDay – 1) / 30
IntAccrued = IntAccrued * PctOfMonth
IntAccrued = Round(IntAccrued, 2)

Service: Servicing fee rate as displayed on Loan Form (ie, 0.375% is 0.375, not 0.00375 or 1.00375)

ServiceFees: Dollar amount of service fees owed for this month

ServiceFees = Round(IntAccrued * Service / EffIntRate, 2)

MonthsTilDlq: number of months a loan must be past due before considered delinquent

MonthsTilDlq = 3

NextPmtDate: date the next payment is due

  • Equal to the Next Due date from the most recent Receipt

AccInt: accrued interest

  • Can have up to four months of interest accrued here
    • 1 month of non-delinquent interest
    • 3 months of delinquent interest

SvcFee: service fees

UncolInt: uncollectible interest

  • Interest accrued once the loan enters non-accrual status (ie, more than 3 months past due)

SvcFee90: service fees unlikely to be owed

  • Service fees accrued once the loan enters non-accrual status

IF the number of months between the current month and the NextPmtDate is greater than or equal to MonthsTilDlq THEN:

  • Loan is in non-accrual status this month
UncolInt = UncolInt + IntAccrued
SvcFee90 = SvcFee90 + ServiceFees

OTHERWISE:

  • Loan is accruing interest this month
AccInt = AccInt + IntAccrued
SvcFee = SvcFee + ServiceFees

If we have not reached the EndDate return to Determine Current Date to Use

Import Prices

The most reliable way to import pricing history is to import the prices from a CSV file, available through the FTN website:

Detailed Bond Listing

  1. Log in to FTN.
  2. Roll-over the Analytics tab.
  3. Under the Portfolio Performance heading, click on the Analytical Reports link.
  4. Choose the Detailed Bond Listing report.
  5. Choose Report Type Detail.
  6. Choose the option to show Previous Month-End Prices.
  7. Choose the option to save as a CSV file.
  8. Click on the button to Run Report.
  9. Save the file in the F:\CMR\ directory. The actual name of the file does not matter, but it should be something easy to remember.
  10. Click on Import Prices on the Main Menu of the Loan Management program.
  11. Choose the file you saved above and click the Open button.

The program will tell you if there are any CUSIPs in the CSV file that could not be found in the Loan Management database:

Missing CUSIPs Dialog

The program will then tell you how many pricing records were imported. It will break down how many new records were added and how many were simply updated. Please note that you may run the Import Prices process multiple times for a single month without causing any problems.

Import Successful Dialog

As part of the pricing import functionality, the program takes advantage of the fact that the Detailed Bond Listing includes a current coupon rate. It uses this coupon rate to update the interest rate for adjustable MBS's. Because adjustable rate MBS's are composed of mortgages of varying interest rates and the individual mortgages pay off somewhat unpredictably, the interest rate for an adjustable MBS will vary throughout the year (independent of the actual reset date for the underlying mortgages). To account for this, the program automatically imports updated interest rates for these adjustable MBS's.

If there were adjustable MBS's that had interest rates change, these will be listed in a query that will be displayed at the end of the process:

Adjustable Interest Rate Updates Query

Issues with Using the Detailed Bond Listing to Update Interest Rates

One issue with using the Detailed Bond Listing to update interest rates is that this report always shows the current interest rate for the securities, even if you run the report for the previous month end.

In other words, if you log on to the FTN website on May 3 and download the Detailed Bond Listing for previous month end you will get Pricing history as of April 30 and interest rates as of May 3. If a few days later you download the Detailed Bond Listing for previous month end on May 7, you will still get Pricing history as of April 30, but your interest rates will be as of May 7.

Problem with ajdustable interest rate update

This is only a problem if you download and import the Detailed Bond Listing on different days during the same month.

Forecast Adjustments

These are numbers based on actual records (not projections) as of the previous year end (12/31/2009 in the example).

  • Principal- ∑(Principal) the sum of the principal balances of the loans/securities
  • Accrual- ∑(Principal × Interest / 12) excluding non-accrual loans the sum of the principal balances times the respective interest rates of all loans that are not more than 90 days past due (ie, nonaccrual loans are excluded).
  • WAC- ∑(Principal × Interest) / ∑(Principal) (Weighted Average Coupon) the sum of the principal balances times the respective interest rates divided by the sum of the principal balances

These columns are computed by the program as explained below.

  • Principal- ∑(Principal) - ∑(Principal of maturing loans/securities) - ∑(Anticipated Mortgage Principal Payments)
    • Mortgages: If a payment was received for a mortgage during the last month of the previous year ('Mtg' Inv Type) then we assume timely monthly payments of that principal amount
    • USGA: Only receive principal payments at maturity, so no monthly principal payment is calculated
    • MBS: Because of the extraordinarily wide variance in monthly principal payments for MBS's, no attempt to forecast these payments is made; these payments can be projected manually by including them in the Pmts/Payoffs column under the Projection Adjustments section
  • Accrual- ∑((Days active / Days in month) × Interest × Principal / 12)
    • this calculation takes into account the expected Maturity Date of the loans
    • once a loan is past its maturity date it no longer contributes to this number
    • the calculation allows for partial month accruals if the maturity date lands in the middle of the month
    • Mortgages: the calculation takes into account a reduced principal based on anticipated mortgage principal payments as detailed above (ie, it uses the Principal from the above calculation)
  • WAC- ∑(Principal × Interest) / ∑(Principal) (Weighted Average Coupon) the Principal is the Principal from the above calculation (ie, it takes into account maturities and principal payments for mortgages)

These columns can be edited by the user, but only if the Sale Status and PA Taxable dropdown boxes are all filled in. If one of these boxes is empty, these columns will display a summary of the user entered totals. In other words, if Sale Status is not filled in, the columns will include the total of all user-entered Available for Sale, Held To Maturity, and Trading items.

  • Pmts/Payoffs- enter the projected amount of principal that will be paid down during the month
    • Mortgages: For mortgages, this column would be used to project pre-payments only; projected payments are calculated automatically by the form (see Forecast Principal section above for details)
    • USGA: For treasuries, this column would be used to project calls only; maturities are calculated automatically by the form based on each treasury's maturity date
    • MBS: For mortgage pools, this column is used to project both regular payments and pre-payments; maturities are calculated automatically by the form based on the maturity date of each MBS
  • Purchases- enter the projected amount of principal that will be added (ie, purchased) during the month
  • Purch Int- enter the projected coupon rate of any purchases that will be added during the month; it is important to fill in this information if there are purchases, otherwise the default of 0.00 will be used when calculating the Adjusted WAC (see details below)
  • WAC Change- enter a value here if you want to replace the Forecast WAC when performing the Adjusted calculations (see Effective WAC below for details)
    • this value should not be the difference between the Forecast WAC and the Projected WAC Change, but an actual replacement value
    • unlike the other columns in the Projection Adjustment section, this column does not have a cumulative effect; so if you want to project a change in the coupon rate and have it carry forward for multiple months, you would need to enter the WAC Change in the current month and all future months
    • for example, if you want to project a drop of 500 basis points (0.5%) starting in September 2010 followed by an additional drop of 200 basis points (0.2%), then using the screen shot below you would enter 1.5409 in the WAC Change column for September and October and 1.3409 in the WAC Change column for November and December

These amounts are running totals where each row is influenced by the Projections on the same or higher rows of the table.

  • Effective WAC- in the formulas below, the Effective WAC is calculated as follows for each month:
    • if there is a value in the WAC Change column then Effective WAC = WAC Change
    • if there is no value in the WAC Change column then Effective WAC = Forecast WAC
  • Principal- ForecastPrincipal - ∑(Previous Pmts/Payoffs) - (Current Pmts/Payoffs) + ∑(Previous Purchases) + (Current Purchases) ie, Principal - Pmts/Payoffs + Purchases
  • Accrual- ∑((Principal - Projected purchases) × Effective WAC) + ∑(Projected purchases × Projected purchased interest) the adjusted accrual is the sum of two components:
    1. the Forecast principal less Projected pmts/payoffs times the Forecast weighted average coupon
    2. the Projected purchases times the Projected purchased interest
  • WAC- (∑((Principal - Projected purchases) × Effective WAC) + ∑(Projected purchases × Projected purchased interest)) / ∑(Principal) the weighted average coupon is calculated as follows:
    1. multiply the Forecast principal less Projected pmts/payoffs by the Forecast weighted average coupon
    2. multiply the Projected purchases by the Projected purchased interest
    3. add 1. and 2. together
    4. divide 3. by the full Adjusted principal amount

Forecast Adjustments

Import Adjustable MBS Rates

The current coupon for adjustable Mortgage-Backed Securities (MBS) may change slightly from month-to-month outside of the normal coupon reset interval. Thus, the current rates must be updated monthly for every adjustable MBS. The rates may be updated as follows using the Adjustable Securities Sorted By Roll report from the FTN website:

  1. Log in to FTN.
  2. Choose the Analytics tab.
  3. Choose the Reports link.
  4. Choose the Adjustable Securities Sorted By Roll report.
  5. Choose the option to show Previous Month-End Prices.
  6. Choose the option to save as a CSV file.
  7. Click on Run Report.
  8. Save the file in the F:\CMR\ directory. The actual name of the file does not matter, but it should be something easy to remember, e.g. F:\CMR\Adjust.csv.
  9. Click on Import Adjustable MBS Rates on the Main Menu of the Loan Management program.
  10. Choose the file you saved above and click the Open button.

Post Pricing Adjustments

This process creates a batch in the GL to account for mark-to-market reporting requirements for securities (MBS and USGA). The calculation uses the amortized cost of the securities, so it is important that the amortizations are posted to the GL before running this process.

This process can be run more than once for the same date and loan type. For example, if this process is run prior to the amortizations being posted then it will need to be re-run for the same date once the amortizations have been posted. However, each time it is run it will create a batch in the General Ledger. It is important that only a single batch be applied in the GL. Any other batches created by this process should either be deleted (if not yet posted) or voided (if already posted).

Reports Form

Help on specific reports:

Transfer Mortgage to REO

  1. Open Loan Form and change Disposition from Active to REO (Screenshot)
  2. Save the record (Shift+Enter)
  3. Enter the effective date of the transfer to REO status (Screenshot)
  4. Post the batch that gets automatically created in the GL (Screenshot)

  • loan_mgmt/onepage.txt
  • Last modified: 2016/02/07 20:06 UTC
  • by mwolfe