A/L Benchmarks

Removing secure data from the REG-Loans or ALERT file before uploading

As we've mentioned in previous posts we understand that the detail loan file format #1 contains sensitive customer data.  If you upload a file to us with customer sensitive data we delete this information right away, we don't want this information either.  Part of our agreement with you is to keep information confidential.

For those of you that are still concerned about about sending the data file we can offer you a new program utility that deletes the sensitive data from the RegLoans (or ALERT) file before you upload it to us.  We call it the "ALERT File Scrubber".  It's a simple program that you download and run on your computer.  It reads in your original data file, deletes (or scrubs) the sensitive data fields, and saves a new clean version of the file that you can upload to us.

Here is the URL:  (you should probably right-click and choose "Save Target as...")

http://www.olsonresearch.com/blogresources/ALERTFileScrubber.exe

The program will save a "scrubbed" version of your original loan file back into the same folder with the same name and a new extension ".ScrubbedForORA".  This is the file you'll want to upload.

If you have any questions about using this program please leave a comment here, email, or call us.  Contact information is in the margin.

Data File Specifications

Here are links to our suggested data file formats:

• Call report data files
• Investment data files
• Loan data files

Once you have created a data file, you may upload it to us using our Secure file upload (sign-in required) area.

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.