Home » Excel Spreadsheet » Certificate Pricing for Credit Unions

The CEO at the credit union where I worked, told the story of how he would price certificates for the small credit union he came from, back in the day. I paraphrase, because like all good stories, it changes each time it is told. The CEO’s credit union was in the same building as another small credit union. In the morning when it was time to price certificates, he would walk downstairs, look up at the other credit union’s deposit rate board, walk back upstairs and match the rates for his credit union.

This is a great story, but unfortunate many credit unions still use the same basic way to price their certificates. I’m not saying comparing the rates of the competition shouldn’t be part of the analysis. What I am saying, is that checking the competition should be the last part of the analysis.

Most credit unions started off serving the financial needs of a certain industry or group of members, identified by their occupation.  For the most part, credit unions are still serving that field of membership and controlled by a board of directors made up of members of that group. If you priced your certificates by comparing to a credit union with reactively the same membership, in the same general area,  that might make sense. That credit union might have developed a niche however, within their membership that allows them to pay higher rates than you.

So where do we start? We start with the U.S. Treasury yield curve. I hesitate to post spreadsheets that are linked to a website. The website might add a row or column, stop updating, or something will go wrong. Also, your IT department may not allow web links, so you might have to get their okay. I compiled this spreadsheet over the last few days. It has never been used in real life pricing. Therefore, it is time for my disclaimer. Use this spreadsheet as an example of what can be done. No guarantees are implied here. You use it at your own risk (i.e. you get what you pay for).

The link is to the Yahoo bond page.   They currently post yields for the 3 and 6 month bills,  the 2,3,5, and 10 year notes, and the 30 year bond. All input cells are yellow and all yellow cells are drop down boxes.

Why price off the Treasury curve? From a credit risk basis, Treasury securities and credit union deposit ( $250,000 or less) both have the full faith and credit of the U.S. government according to Congress. See:

Subsection 901(b) of the Competitive Equality Banking Act of 1987, Pub. Law. 100-86

Back to the spreadsheet. There are two yellow input cells in rows 2 and 3. The first asks for the nearest number of percentage points you wish to round up. The default is .05%. Also, you might always want to limit the downside, so you are never offering zero. The default is .10%.

There are eight columns for regular certificates and three additional columns for promotional rates. If you do not use one or more of the default months, pick “None”. By picking a month, the spreadsheet automatically interpolates a yield, based on the Treasury yield curve, on a linear basis.

I assumed the Treasury yields were stated on a semiannual compound basis, so I converted the semiannual yields to daily compound yields. It doesn’t change much at these low rates.

Then you have another series of yellow drop down boxes, to choose the Add-on rate over the converted Treasury yields. These spreads are up to you. Both the APR and APY are then calculated for each term. If you pay more for jumbo certificates, there are add-ons for that.

In the morning, when setting the certificate rates, click the “Refresh Rates” button to refresh the Treasury rates from Yahoo. If no changes in spreads are made, the new certificate rates are calculated. To post the rates for historical purposes, click the “Post to History” button. If something happens during the day that changes market rates significantly, you can refresh the rates and reprice intraday.

cd1cd2

Download “CU_CDs

Leave a Reply

Your email address will not be published. Required fields are marked *

*
*