A/L Benchmarks

Balance Sheet Projection

We have had many requests from clients and regulators for the ability to use a custom budget.  Although we have always had this option, the additional cost has been prohibitive for some clients.  On this page, you have the ability to enter Targeted End-Of-Period Balances for you entire Balance Sheet at no additional cost.  For reference, it may be beneficial to enter the Historical Actual EOP balances from your call report before beginning.  Please note that if you enter any balances, you must complete the BALANCES FOR ALL QUARTERS.  If a category is left blank, the default assumption is flat balance sheet, or no growth.

Additionally, you have the ability to enter the Floating Rate Pricing Spread.  Fixed Rate loans are priced at 50 basis points above the floating rate pricing.  If this relationship is not appropriate for your bank, you can enter your fixed rate pricing in the Notes field of the service kit (see below).

New Pricing rates for deposits should reflect the rate currently being paid on NOW, Savings, Money Market, Small and Large time deposits.

Income and Expense Items should be entered as a QUARTERLY figure for each labeled quarter.  Again, please be sure that if you are entering this information, you fill in ALL QUARTERS.

Equity transactions should also be entered on a QUARTERLY basis, filling in with zeros where appropriate.  Please note that the dividend figure should be entered as a TOTAL DOLLAR figure NOT per share.

If you enter an estimated Net Income Forecast, we can look for entries that might be out of sync to make sure that the forecast is as accurate as possible.

Please enter the number of FTE employees so that we can calculate a Salaries and Benefits per employee figure.

Finally, please enter a rate forecast.  There are three options here:

1)  Enter a projected rate forecast for Fed Funds

2)  Enter a projected rate forecast for two distinct yield curve points; the 2 Year CMT and the 30 Year CMT, Or

3)  Enter a projected rate forecast for Fed Funds AND the 2 Year CMT and the 30 Year CMT

Again you must complete ALL QUARTERS.

Forecast notes to the analyst preparing you reports is for any additional information that you would like to convey that does not fit in this file or information that has been requested by your analyst.  We realized that as detailed as these inputs are, there isn't a spreadsheet large enough to encompass all possible scenarios that might occur in your bank.  Here is where you can communicate the unique nature of you bank so that we can model appropriately.

Other Items

As the category implies, other items are a group of important miscellaneous items.  First, you need to supply information on any unusual and/or infrequent non-interest income and expenses.  These unusual items affect two areas.  First, overhead is a piece of the Fair Value calculation and if identified, we can back unusual items out of the overhead piece.  Secondly, we do not want to forecast income or expenses that will not reoccur.

We can also calculate earnings per share, common dividend per share and  book value per share if we have an ACTUAL number of shares outstanding.  (Please note that this is the only number on the Service Kit that is NOT stated in 000's.)

The State Tax Rate varies widely across the country; we use a default State Tax Rate of 0%.

The call report combines the average balance and expense information for Repos with Fed Funds Purchased.  Although the rates on both instruments have been very similar in the past, those days are gone and we have seen rate differences of over 100 basis points.  Please provide the QUARTERLY Repo expense, End-Of-Period balance and average balance so that we can model Repos separately from Fed Funds.

FHLB Borrowings

Complete this page of the Service Kit only if you have FHLB advances.  Record the name or Advance ID# for your reference, amount, current rate, maturity date and if the advance is amortizing (Y or N).  For variable rate advances, also include the index rate, spread, repricing frequency (D, M, Q, A) and the next reprice date.  Make sure that this date has not already passed and truly represents the next possible reprice.

Many FHLB advances have call options so we need the date of the call (or put) and if the call (or put) is at the FHLB's option (F - the default assumption) or the Bank's option (B).

Finally, the FHLB sends the bank a report entitled "Customer Mark-To-Market Shocks" and the data from this report is also a useful in modeling your FHLB portfolio appropriately.

Deposits - Core Factors

Core Deposits accounts (DDA,NOW, Savings and MMDA) comprise a large part of a banks funding, especially in a community bank.  Questions begin to emerge on how to model the deposit behavior in terms of sensitivity to market rate changes and maturity structure for valuation.

Core Deposit accounts typically have administered rates, meaning the rates change when the powers that be at the bank say they change.  We do know however that there is often some response to market rate changes.  To model this sensitivity we use a Core Deposit Repricing Beta factor. This is the percentage of rate change each account will move with a 100 basis point movement in Fed Funds. 

For example, if Money Market accounts are extremely rate sensitive and perhaps even tied to the Fed Funds rate, the Core Deposit Repricing Beta factor is 100%; Money Market accounts move step in step with Fed Funds. 

Let's look at another example.  If you have not moved the rate on your savings accounts in the last 10 years, the Core Deposit Repricing Beta factor would be 0%, or no sensitivity to movements in Fed Funds.  These are extreme examples and most accounts behave somewhere in between these extremes. 

Also remember that the Core Deposit Repricing Beta factors are not always symmetrical in the rates up and rates down scenarios.  Bankers will move rates down as quickly as possible (more sensitivity to market rate changes and a higher core deposit beta factor) while delaying an increase in the rates on core deposits as long as possible (less sensitivity to market rate changes and a lower core deposit beta factor).  Here is a related post that talks about Core Deposit Beta factors.  And another one here.

In order to calculate the Present Value on an account we need maturity structure of the cash flow.  This presents a particular problem with accounts that have no contractual maturity.  In the case of Core Deposits, we use a Decay Assumption to show how the accounts behave in terms of maturity.  There are four choices for decay assumptions:

1)  FDICIA 305 (default assumption) 

This was proposed by FDICIA over a decade ago and never adopted for several reasons, primarily because the behavior of these accounts in terms of "maturity" varies widely from bank to bank depending on size, location, age, marketing strategy et cetera.   FDICIA 305 tends to produce shorter (modified) durations, typically 1.6 to 1.7 years.  To the right you will find the decay assumptions proposed by FDICIA 305:

2)  OTS NPV

The OTS uses their own formula to determine Net Present Value (NPV) for all Thrifts and the (modified) duration using the OTS formula tends to be longer than the FDICIA 305 proposal, typically over 2 years.

3)  3rd Party data

There are a few vendors who will do an account analysis of your core deposits and calculate a decay rate for each account (DDA, NOW, Savings and MMDA).  This methodology is very precise but also very expensive.  You can supply this data via our secure upload site.

4)  Custom Decay

This is the preferred method of examiners if you do not use a 3rd Party analysis.  Clients internally calculate a decay structure by analyzing the average life of their core deposits, typically done by using a five year history of closed accounts.  This can be difficult due to the purging of closed accounts but you can begin by collecting this data now.  Once the analysis is complete, you can provide this information here.

More reference on Decay Assumptions can be found here.

Wait!, there is sensitive data in this loan file!

Yes, if you are choosing to send us the "Format #1: Interagency (ALERT) format" there is indeed sensitive customer data in the file.  And to be blunt, we don't want you to send us the sensitive data either.  If you are going to send us this file please use the ALERT data scrubber program.  In fact, if you send us the ALERT file without the sensitive data cleaned from it, the first thing we're going to do is permanently DELETE the sensitive data because we don't want the security hassle.

If you can't send us the ALERT data file, then you will need to send us data in one of the other two formats.

The other "good-security" practice you should follow is to upload this file to us via our secure server.  Don't email us the file (many of you still try to!)  This quarter if you email us the file, you'll receive a polite email response back asking you to upload the file instead.

I know it's a change, but I think you'll agree, it's a change for the better.

Why are you requiring us to provide loan detail?

I've been asked this question by a handful of clients over the past month.  Starting with 4th quarter 2007 processing we are requiring clients to provide us with loan detail information (related blog post here).  Why the change?  After all we've been producing our A/L BENCHMARKS reports using call report data & supplementary assumptions for years.

The biggest reason for the change is examiner and auditor pressure.  Ever since "model validation" became a hot button issue in a/l modeling we've been asked include detail loan data in our analysis.  Until now it's something we did on a custom, as requested, basis.  But there has been mounting pressure to always include it.  Many thought it to be a logical next step because we're already including security detail data (from bond accounting systems).

But up until now we've lacked a specific standard file format to use.  We've always attempted to base our service on accepted modeling standards and data sources (hence the name "benchmarks").  Fortunately for us the FDIC, Fed, OTS and most states approved a standard loan data definition back in 2002 to be used for off site examinations.  This standard has had 5 years to "sink-in" and every bank is capable of producing such a file.

So now instead of allocating the maturity and repricing information from your call report to generate cash flow structures, we can model every loan individually (just like we do for your bond portfolio).  Examiners & auditors are happy...

Deposits - CD's

In addition to the data found on your call report, we ask clients to provide detailed data on the maturities and cash flows of the time deposit portfolio in order to accurately model the behavior of these accounts.  Please provide a breakout of CD's into fixed rate maturities and variable rate repricing dates.  For large time deposits (> $100,000), we also ask for the percentage of fixed rate instruments for regular, brokered and public time deposits.

Investments

In order to model your investments accurately we prefer, and more importantly examiners prefer, an investment download from your Bond Account Vendor or one that is created internally.  This allows us to capture all of the optionality in the portfolio that is absent on the call report and model bonds on a CUSIP level.

 

If an investment download is not an option, we ask that you input more detailed information about your portfolio such as the percentage of each category that is fixed rate, the prepayment speed and a cash flow analysis.

Loan detail file FAQs

Here is some helpful FAQ regarding the loan data file.  This FAQ was originally published by the Louisiana Office of Financial Institutions addressing questions about the Interagency Loan File Format.

Are fields considered one row and terminated by 1 carriage-return line-feed sequence?
Yes.

Will all the fields on one line comprise one loan detail record?
Yes.

Should we include home equity loans in the file?
The file should contain all the bank’s loans regardless of type. This would include commercial as well as consumer loans.

Should we include zero-balance loans?
That depends upon the loan. We do want to see open lines of credit where the bank is obligated to advance funds in the future. These might show zero for an outstanding balance, but would show the full amount of the commitment in another field (undisbursed commitment availability). Examples of these types of loans that might be carrying a zero balance would credit card lines, commercial lines of credit, standby lines of credit and home equity lines. On the other hand, we do not want to see a zero-balance loan record that's simply an artifact from a loan that's been paid in full. If these were included in the file, they would show zero for both fields since there is no longer a legal obligation on the bank’s part to re-advance funds.

For the field called "charge off amount", do you want all old charged off loans in the interagency file?
If a loan has been charged off in full, it's a zero-balance loan as described in the answer above and it should not be in the file. We have seen some files where these loans show an outstanding balance and a like number in the charge-off amount. That creates a problem since at first glance these appear to be active
loans. It's important to understand that we will try to reconcile this trial balance to the amount shown for loans on the bank's general ledger using the total
from the outstanding balance field. Any loan that is listed in the file that isn't an active and current relationship that's properly carried as an asset of the bank should not be showing a current balance other than zero. In addition to inhibiting our ability to balance the ledger, these lines will distort the past due and summary reports built into our tools.  If the bank has a note with a partial charge off, the current balance net of the charge off would show up in the outstanding balance since that portion of the note is still active. The charge off amount would be listed in the "charge off" column. If you have already mapped and distributed the format to your customers and you have old charge offs in the file, no change is needed as long as the amount in "balance outstanding" for these notes is zero.

In those instances where the bank sells a portion of a note, should we report the balance as balance outstanding net of the sold amount?
Since we’re interested in balancing "outstanding balance" to the bank’s call report RC-C, it should be net of any amount sold. For example, if a bank makes a loan for 100,000 and then sells 25,000 to another institution, the record for that note should reflect the following subsequent to the sale:
balance outstanding = 75000.00
participation indicator=S
amount sold=25000.00
original amount sold=25000.00
Please note the "amount sold" will decrease as payments are made to the participating bank(s). If your system is incapable of linking the participation to an individual note, then you could report the amount sold as a negative note. In the example above, the amount in outstanding balance for the original note would be 100000.00, and the amount in outstanding balance for the “participation” note would be –25000.00.   In addition, you would need to populate the record for the negative note with some key information so that we could tie it to the underlying borrower. This would include:
Note number (this could be a dummy number, or a number tied to the participation) and Borrower ID.  Please do not populate other fields if you use the negative-note option.

Our loan products can have up to 10 rates, indices and margins. How do you want to address this in the field for interest rate, index and margin?
We envision that this file will be presenting information on a loan-by-loan basis (i.e., one loan per line). That being the case, we would like to see the information/indices related to each loan. While we understand there could be many indices in use, we don’t expect that more than one will apply to an individual note.

Please give further definition of field "Amortizing/Non-Amortizing status"
We’re looking to see if amortization has been suspended. This would be most common on consumer loans where there has been an agreement by the bank to suspend principle payments for specified period of time.

Several fields in the file format document have been identified so far as unavailable or null for our system, and they are not identified as required fields for population.  Do you have any tolerance for the number of fields that are not populated on the file?
A few of the fields are absolutely required (Note Number, Balance, Rate, Maturity Date).  If other fields are unavailable, please leave those fields blank.  Keep in mind though that missing data will impact your analysis (for example, not providing an index and spread for a variable rate loan)

Is the file to be sorted in any manner, i.e. branch, note type, officer, etc.?
Sort is not important to us since our tools will sort the data in the manner we need once we import the file.

Is there a particular method for transmission and data encryption that is expected?
Yes, you must upload this file via our secure (https://) server.  DO NOT EMAIL THIS FILE TO US.

What should we report in "next payment due" when the loan is delinquent?
Show the due date of the next payment. This will be in the past if the loan is delinquent.

What should we report in "payment frequency" when the note has a mixed payment structure (for example, interest monthly with principle quarterly)?
This is a 10-character alpha field. Please describe the payment structure as "mixed" or "variable."

On a related note, what should we report in "payment amount" for loans with mixed payments?
Report the amount of the next expected payment if known.

How should we report negatively amortizing loans in the "amortizing/non-amortizing" field?
Answer "no.”

What should we report in "date of last payment" if the last payment wasn't a full payment?
Report the date of the last payment even if it wasn't a full payment. We are looking for activity on the line.

How should we report non-valid dates?
In some systems, non-valid dates are used to identify a specific event (i.e., 6-31 for quarter end).  In those cases, please convert to a real date. Report all other non-valid dates as is, since they probably reflect a data integrity problem that we will want to investigate.

New detail loan data file

We've mentioned some new things coming this quarter (4th quarter 2007).  One of them is the new standard loan detail file.  For some time we've been under tremendous pressure from examiners and auditors to include in our model detail loan data.  We have always been able to accommodate this request.  We've able to accept loan detail data into A/L BENCHMARKS for the past seven years.  The difference is now we're going to require it. While, many of you have provided us with additional maturity and repricing information about your portfolios via the Service Kit, we'll no longer accept this additional information.  In lieu of that, we're going to ask that you send us a detail loan data file in one of three different formats.  Having loan by loan detail will allow us to more effectively capture the nature your bank's interest rate risk exposure.  Click here to learn more about the file formats or you can click on any of the headlines listed on the right.