CECL Primer for Small Credit Unions: ALPHA VERSION
CECL Primer for Small Credit Unions: ALPHA VERSION TOC \o "1-3" CECL Primer for Small Credit Unions PAGEREF _Toc523215620 \h 1Introduction PAGEREF _Toc523215621 \h 2Disclaimer PAGEREF _Toc523215622 \h 2Credit where credit is due PAGEREF _Toc523215623 \h 3CECL Basics PAGEREF _Toc523215624 \h 4Segmentation PAGEREF _Toc523215625 \h 5Methodologies PAGEREF _Toc523215626 \h 7One final adjustment PAGEREF _Toc523215627 \h 7Traditional meets CECL PAGEREF _Toc523215628 \h 7Snapshot PAGEREF _Toc523215629 \h 9Modified vintage PAGEREF _Toc523215630 \h 10Life of the loan PAGEREF _Toc523215631 \h 13Life of loan methods for closed-end loans PAGEREF _Toc523215632 \h 13Life of loan for open-ended loans PAGEREF _Toc523215633 \h 16Forecast PAGEREF _Toc523215634 \h 19About economic indicators PAGEREF _Toc523215635 \h 19Is the indicator any good for your credit union? PAGEREF _Toc523215636 \h 20Building a model PAGEREF _Toc523215637 \h 21Applying the forecast and baseline model PAGEREF _Toc523215638 \h 22Mean reversion PAGEREF _Toc523215639 \h 23How a small credit union can use a forecast PAGEREF _Toc523215640 \h 23The process for building the forecast PAGEREF _Toc523215641 \h 24Data PAGEREF _Toc523215642 \h 29About weighted averages PAGEREF _Toc523215643 \h 30IntroductionMuch has been said, written, and recorded regarding current expected credit losses (CECL), the new accounting rule that changes how credit unions will calculate their Allowance for Loan and Lease Losses (ALLL). Many smaller credit unions fear they will have to hire a vendor to compute their figures—just one more multiple-thousand-dollar cut in the death by a thousand cuts they are experiencing.The good news is that CECL is scalable, and regulators are interested in helping smaller institutions fulfill requirements without seeking outside help. The Federal Reserve has said:“Smaller and less complex institutions will be able to adjust their existing allowance methods to meet the requirements of the new accounting standard without the use of costly and/or complex modeling techniques.”More good news is that much of the loan data you may need can probably be found in an Aires file. This document attempts to outline how a smaller credit union could go about fulfilling CECL requirements without hiring an outside resource. It is certainly possible.But make no mistake--for a credit union to complete its own calculations, it must have access to its loan data and it must have someone who has a solid grounding in spreadsheets (as in, that person must understand how to create formulas, create charts, and possibly how to use pivot tables; if you lack those skills, search the Internet; there is no shortage of instruction out there). Also, it will take time. It’s hard to say how much, but it’s not something that can be whipped out in a morning. Depending on your credit union’s complexity, you may be looking at a dozen hours or a hundred hours of work or more. But don’t despair. You have time. You have until the first fiscal year after December 15, 2021 to be ready. This is doable. If you have the will and the patience, you can do it. DisclaimerNote we can’t guarantee that the information in this document will satisfy your examiners or your accountants. For that matter, there will likely be some people who read this document and roll their eyes at it or take issue with one or many points. This document represents how one person has come to understand CECL after spending a fair amount of time studying it. Before you get really far into implementing this, talk with your CPA about the methodology you plan to use. Also, nothing in here should be considered the end all and be all of CECL for small credit unions. This document represents a starting point that you can use to better understand CECL and how to implement it. It’s likely that over a number of years—even after the implementation date—you will refine and adjust whatever method you select for CECL, based on CPA and examiner feedback. If you use the information in this document to build an ALLL calculation, you should be well on the path to satisfy anyone who cares to consider what you’re doing. You’ll probably also know more than many examiners.This document will also evolve over time as people provide feedback. It’s currently in alpha format (beta well be next, then production-ready version) If you would like to send some, you’re welcome to do so at sn@. Credit where credit is dueThis document comes after reviewing many free resources online, usually available from vendors with CECL solutions. Notable among them are Visible Equity, SageWorks, and Brick & Associates. They provided numerous free webinars and documents that helped me piece together this document. If, after reading this, you decide that CECL calculation is better left to a vendor, any of those might be able to serve you well. Let’s get startedCECL BasicsCECL changes how credit unions will calculate their loss reserves. It adds three important requirements. The calculation must: Segment portfoliosConsider life of loanIncorporate a reasonable and supportable forecastThere are multiple ways that you can fulfill each of the above requirements. This document explains a few options for each, address special challenges that small CUs will likely face, and demonstrate specific methodologies for actually doing the calculation—what this document calls the calculation methodology. Segmentation This is probably the easiest part of CECL for a small credit union to comply with. Most already do this, to a degree.CECL requires that credit unions estimate future losses on different groups of loans, called segments. The idea is that loans with different characteristics likely have different loss patterns and pose varying degrees of risk—they have a similar risk profile. You likely already segment your loans in some ways with your current ALLL calculation. The risk profile is affected by many loan or borrower attributes, such as: Collateral typeTerm (or maybe life of loan)Borrower credit scoreEach credit union must segment loans as it sees fit. In fact, this is one area where more sophisticated solutions can get especially fancy, as segmentation could happen on any number of increasingly complex characteristics. For example: Nature of origination (for example, indirect vs. direct)Change over time of credit score of borrower (credit score migration)Original or current LTV of loanCollateral value or change in collateral valuePast-due statusRisk grade based on many factors.In the spirit of simplifying CECL for small institutions, a smaller, less complex credit union will have fewer segments than a lager, more complex credit union. Quite simply, it may not have experienced as many losses, and therefore cannot build reliable risk profiles for many groups of loans. So, the question becomes: how should you segment your loans? The answer is simple: you likely already segment your loans, and it’s also likely that your current segmentation is adequate to meet CECL’s requirements. In fact, in an FAQ, the Federal Reserve said as much.“Although the new accounting standard provides examples of similar risk characteristics, smaller and less complex institutions may conclude that the segmentation practices they have used under the incurred loss methodology are also appropriate under the expected loss methodology.” If you don’t currently segment your loans for your ALLL calculation, you’ll need to create segments based on risk. Perhaps talk with your CPA for suggestions on this, or talk with credit union peers in your area to find out how they do it. At a minimum, loans should probably be segmented by type. For example, barring special circumstances, a small credit union might have the following adequate segments: New auto loansUsed auto loansPersonal/signature loansCredit cardsSecond mortgagesYou could get into finer detail by segmenting auto loans based on whether they were indirect or direct (if you do indirect lending), mortgages based on whether you hold the first lien or second lien, etc. Again, we’re trying to keep it simple.MethodologiesThis document details three methods for determining an appropriate ALLL amount based on CECL requirements. They vary in their level of sophistication and difficulty, as well as in the final ALLL amount they might determine. The trade-off is that the more sophisticated the method is, the more precise, accurate, and (probably) smaller the amount would be. The less sophisticated the method, the more general and probably larger the amount would be. From easiest to hardest, (the ranking isn’t an exact science, though):Traditional meets CECLSnapshotModified vintageThe descriptions below include references to things like “life of loan” figures and “forecast”. How to figure life of loan and create a forecast are detailed in latter sections of the document. Don’t worry about them right now. For now, just get a handle on how each methodology works. Then, you can get a better understanding about life of loan figures and forecasting later on.First, though, we’ll cover the final step in any CECL calculation: the qualitative adjustment.One final adjustmentThe calculation arrived at using any of the three methodologies below should provide a good estimate of the losses you currently expect to incur. However, this estimate does not include qualitative information that you or your credit union may have. For example:If you know of loans that are likely to go bad, and they exceed your ALLL amount, you will need to adjust your ALLL amount accordingly. If you are aware that a key SEG will be laying off a large number of your members with loans, you’ll want to adjust your ALLL accordingly. If you have recently changed your underwriting policies, so that you are assuming more credit risk, your ALLL will need to reflect that change.These are qualitative pieces of information not included in the methodologies, and they should be included in your final calculation. So, after you have completed a CECL calculation, whatever the method, you’ll need to make an adjustment using qualitative information. The important thing is that as you make final adjustments to the ALLL you document the reasoning in as much detail as possible. Be as scientific about it as you can be.Traditional meets CECLAs the name implies, this method most closely matches what you are probably already doing. Basically, it multiplies the annual loss rate by the life of each loan segment, sums the segments, and compares that to a forecast. It’s the easiest method but is also more likely to yield a larger ALLL than more complicated methods. As with any CECL model, we must incorporate a reasonable and supportable forecast. To keep it simple, rather than incorporate the forecast right into this methodology, we will use this methodology without a forecast, but create a separate prediction using the forecast, then compare the two figures and adjust as seems appropriate. After that, we use qualitative information to make further adjustments to the calculation.The processAll of this process, below, is demonstrated in the blue spreadsheet tab labeled “Traditional method”.Segment your loans.Find the ending balances of the loan segments for each year of the last several years (for this example we are using 3 years). Total all loans for each period. For each segment, identify the losses for each period that you are tracking (we are going with 3 years for this example). So, that is, find the dollar amount in losses that you had in each segment each year. For each period, calculate what % of each category was written off. Do this by dividing the dollar amount written off in a period for each segment by the corresponding period’s segment total. For each segment, for the time period you are studying, average the % of the segment that was written off. So, we are going to find what % of each loan segment was written off, on average, for over the last three years. For each segment, multiply the latest balance of the segment by the average % that was written off. This gives you the amount you expect to write off in each segment in the next year. Input the average life of loan for each loan segment. You could find that by using the loan start and end dates, or the balances and payments method.Multiply the amount you expect to write off in each segment in one year by that segment’s average life of loans. This gives you the amount you expect to write off for each segment over the lives of all the loans.Sum the segment amounts found in step 10. This gives you your total expected credit loss over the lives of all loans on your books, barring any prediction of the pare the value found in step 15 with the overall prediction made using the model built with an economic indicator. Adjust the final amount based on qualitative information you have, that isn’t included in the calculation.Snapshot The snapshot method (sometimes called “open pool”) entails creating pools of loans that reflect all of a segment’s loans on the books at a certain time and tracking those loans and their write-offs over time. For example, perhaps you have a loan segment of “used auto loans”. In figuring your ALLL, you create a pool of used auto loans as of December 31, 2015. Any used auto loans on your books at that moment in time are counted in the pool. As we move forward, the pool won’t change—no loans will be added or taken from the pool—although the balance will pay down and there will be charge-offs. The idea is that the snapshot captures a pretty good picture of what the segments look like at any given time, and that what happens to it is likely to happen to all loans in the segment. Each snapshot or pool has some new loans and some old loans. Some of those loans in the pool (older ones) will be nearly paid off, while others (newer ones), will be pretty new. Some will be written off. In the end, it balances out. Over time, as you gather data, you will have multiple pools of loans for the same segment, with the same loan counted in multiple pools. For example, with our used auto loans, we may have a pool as of December 31, 2015, and another for December 31, 2016. In this case, some loans (perhaps those made in late 2015) will be in both pools, and if those loans are written off, those loans will be counted for both pools.At first glance, it might seem that we are counting the write-off twice. It’s true that we are counting the write-off for each pool, but because in the end we will average the write-off rates for each pool, it comes out in the wash. As with any CECL model, we must incorporate a reasonable and supportable forecast. To keep it simple, rather than incorporate the forecast right into this methodology, we will use this methodology without a forecast, but then separately create a prediction using the forecast, and compare the two figures and adjust as seems appropriate. After that, we use qualitative information to make further adjustments to the calculation.The processIn the spreadsheet, this process is demonstrated for one segment in the orange tab labeled “Snapshot method”.Segment your loans.For each segment:Take a snapshot of the total balance for each segment at a certain date. The loans that comprise that balance are the pool of loans we will consider. You should create multiple pools, and in fact as time passes and you gather more loan data, you will want to create more pools in order to create a better picture of your loss history. Pools can be spaced at any interval you like, but an annual spacing between pools seems reasonable. Track the write-offs of loans from the pools. You must be able to identify which write-offs go into which pools. Depending on the overlap of your pools, some write-offs will be in multiple pools (but never multiple segments). It’s okay—using the average later will make it all good. Do this until the pool of loans is off the books (when you can be sure write-offs for the pool are over). This meets the life-of-loan requirement.Total the write-offs for each pool.Calculate the % of each pool written off by dividing the total write-offs for the segment by the original pool balance.Average the write-of % for as many recent periods as you wish to use. In this example we use 4 periods.Take the current balance of the segment, and multiply it by the average write-off % found in the step immediately before this one. This gives you what you can expect to write off for loans currently in this segment. For the current period, list the current expected losses for each segment (found in step 2.f, above).Sum all the current expected losses. This is gives your expected credit losses across all loan pare the value found in step 4 with the overall prediction made using the model built with an economic indicator. You may have to adjust accordingly.Adjust the final amount based on qualitative information you have, that isn’t included in the calculation.You may be wondering, “Where is the life of loan consideration?” The answer is that it’s in step 2.b in the process above. You have considered all the write-offs for all the loans in a snapshot until all of those loans have paid off. So, rather than calculating the losses in a single year, then multiplying those by the life of the loan (which we did in the traditional methodology), we have simply found all off the losses for a snapshot until all of those loans in the snapshot have paid off.Modified vintageA vintage is similar to a snapshot method in that it looks at a group of loans and their associated write-offs. The difference is that a vintage only includes the loans made in a certain period, and loans in one vintage will never overlap with loans in another vintage. To be clearer, a vintage of a segment consists of all of the loans for that segment made during a certain period. For example, all of the used car loans issued in 2017 would be the 2017 vintage of used car loans. The 2016 vintage would be loans originated just in 2016.The main difference is that we can’t simply take the average charge-off rate for a vintage because it doesn’t represent an entire portfolio, as the average charge-off rate for snapshots does. Instead, we must look at the vintages that are still on the books, and estimate their future write-offs based on the performance of vintages in the past.This process for one segment is demonstrated in the purple tab labeled “Vintage”.The process: Segment your loans.For each segment:Create the “vintages” of loans. Each vintage represents all of the loans of a segment made during a certain period. For each vintage:Enter the balance of the vintage—the total amount of loans made during the period. You may also be able to use the balance at the end of the vintage periodTrack the dollar amount of write-offs from each vintage in subsequent periods. On the spreadsheet, the columns labeled 1-5 are the subsequent years for the vintage. Examples:For the 2014 vintage, year 1 is 2014, year 2 is 2015, year 3 is 2016, etc. For 2019, year 1 is 2019, year 2 is 2020, year 3 is 2021—which we don’t have data for, yet, because it’s in the future. We will have to estimate those losses.For each period, find the % of the original balance of the vintage that was written off. In the spreadsheet, this happens in the second table. You’ll notice that some cells are shaded darker gray. These are for future periods of vintages that have not completely paid off, yet.For future periods of vintages that have not yet paid off (the cells in gray) estimate what % of the vintage will be written off in each period. Do this by taking the average amount of write-offs for similar periods in the past. Examples:For the 2020 period 2, average the write-offs for each other “period 2” that has already taken place. See the formula in cell D19.For the 2017 period 5, average the write-offs for each other “period 5” that has already taken place.For each vintage that has not yet completed, sum all actual and predicted write-off rates for the vintage. This happens in column H in the second table. This gives you the overall predicted write-off rate for the vintage. For each vintage that has not already completed, multiply the predicted overall write-off rate in column H by the original balance of the vintage. This gives you the total amount you expect to write-off for the vintage. It’s in column I in the spreadsheet.Sum all the write-offs that have already taken place for vintages whose lives haven’t ended. This is in column J.Subtract the total amount of loans already written off (column J) from the predicted amount of total write-offs (column I). This happens in column K. This gives you the amount you need to reserve for each vintage.Sum the amount you need to reserve for each vintage. This is the total amount that you need to reserve for the segment.Sum the current expected losses for each segment. In the spreadsheet, this is the third table, starting in row 22. This is the amount of ALLL this methodology says you should pare the value found in step 3 with the overall prediction made using the model built with an economic indicator. You’ll need to adjust accordingly.Adjust the final amount based on qualitative information you have, that isn’t included in the calculation.Life of the loanIn the past, credit unions have typically looked at their loan history over the last year or two and based reserves on that. Under CECL, credit unions must consider the lifetime of the loan. For example, if a credit union makes a loan with a term of five years, that loan could be written off at any point during those five years. In the past, you only thought about one or two of those years. Now you must think about all five, and what is the possibility of it being written off at any point in the five years.Note that “lifetime” is different than “term”. You may make a five-year car loan, but it may be paid off (or charged-off) in five months, one year, three years, or 40 months. That is the lifetime.There are many ways to calculate lifetime of loan. We are mostly interested in finding the average lifetime of a loan segment, which is usually just the average of all the lifetimes of all the loans in the segment.A few are described below.Life of loan methods for closed-end loansLoan start and end datesPerhaps the easiest way (if you can pull the data) is to subtract the start date of a loan from the end date—the “end date” being the date it reached a balance of $0 or the date it was charged-off. Do this for each loan in a segment, then find the average of all of those loans in the segment. This gives you the average life of loan for the segment.An important point is to know what units you are using. Depending on how you do the calculation above, you may get the average life of loans in days, months, or years. It’s easy to convert it to whatever unit (day, month, year) you need it to be later on as long as you know what it is when you start.Payments and balancesThis method is surprisingly easy. What we’re going to do is combine all the loans in a segment into one big loan and figure out how long it will take to pay down that loan. To do that:Add all of the current balances of all the loans in the segment together.Find the amount that the principal was paid down on each of the loans in the segment in the last period, then add those amounts together.Divide the first figure by the second. There are at least two ways you could do this, depending on the data available to you. For example:If you have the balances of loans in a segment at the end of two different months, you could calculate how much the balance was paid down in the month, and divide the total balance by that amount. That would give you the number of months, roughly, that it will take for the loans to pay off. Just be sure that the balance reflects all the same loans, that new loans made in the month weren’t added on to the balance at the end of the month.If you have payment data, such as all the payments made to loans in a segment, you can calculate what part of the payments went to principal and which went to interest, then divide the balance by the amount that went to principal.Since the second method is a little more complicated, it’s demonstrated here. There are actually a few ways you could do this, depending on the exact data you have. To do this the way shown, below, you need three pieces of information about every loan in a segment: The balance at the start of the period (or the end of the last period)The annual rate on the loanAlternatively, if you can simply pull the amount of interest charged on the loans in the period, you don’t need the rate because we are going to use the rate to calculate how much interest was changed on the loan in a period.Or, if you know how much the principal was paid down in the period, you could use that information, instead.The payment made on the loan during the period (probably the latest month).On the accompanying spreadsheet, this is done on the green tab labeled “Life of loan pmnts & balances”. It uses data in the green “Sample loans” and “Pivot Table” tabs.Sum the balances of all the loans in a segment at the end of a given period.Total the interest charged on the loans in the period. If you can export this from your system, excellent. Just total it all. If you can’t easily export it, you can estimate it with the following process:Get the loans with their balances and rates into a single spreadsheet, with each loan on its own row, and columns neatly arranged so you have one for interest rate, and another for balance at the end of the term. Use a pivot table to group and sum loans. Group the rows by rate, and in the next column sum the balances of the loans. If you don’t know how to use a pivot table, this will be the hardest step. Once you learn, it’ll be surprisingly easy.For each row of the pivot table, divide the first column (the rate), by 12, and then multiply that by the second column of the pivot table (the balance of all the loans multiplied by the rate). This gives you the amount of interest accrued for loans with each rate. This is presuming that one period is 1/12 of a year, or one month.Sum the interest charged. Sum all of the payments made on the loans in the segment in the period.Subtract the interest on the loans (found in step 2.d) from the sum of all the payments, found in step 3. This gives you the amount of principal paid off in the period.Divide the amount found in step 1 by the amount found in step 4. This tells you, roughly, how many periods it would take to pay off all the loans in the segment.Note that as the balances on the loans get smaller, the amount of interest would get smaller, and therefore more principal would pay off, so the actual life of the loan would likely be smaller. If you wanted, you could create an amortization schedule to figure out the life of the loans, and the life of the loans would be shorter than this basic method, thereby reducing the amount you ultimately would need to reserve. If you wanted to, you could use the average rate, the balances of the loans, and the payments made, to create an amortization schedule for the segment, and figure out the shorter life of loan. Not doing an amortization schedule creates a more conservative estimate.An amortization schedule is shown on the “Life of loan pmnts & balances” tab of the worksheet, under the “Simple amortization schedule to determine life of loan” section. In this example, the amortization points to 26 periods as the life of the loan, while the other method found 27 periods as the life of the loan. Repeat the five steps above for multiple periods.Average the results from multiple periods to find the average life of loan for the segment.Depending on the data you can get out of your system, this may be easier than the process listed above. For example, if you can simply generate the amount each loan was paid down in a period, you will be able to skip a step or two in the above process. If you can get the balances at the end of two periods, you can figure the amount of principal that was paid down. There are multiple ways you could do this—the above is just one example.Some might criticize this method as too simplistic, as not providing a true picture of the life of the loan, perhaps for the reasons given in step 5.a, above. But, for a small credit union trying to complete this in as simple a manner as possible, it should be adequate. Basically, we’re treating the entire segment as one gigantic loan, and figuring out how long it will take the loan to pay off. Because there are many loans in there, and every loan will behave differently, we may be off by a little bit. But, the very fact that we are including many loans and their payments and balances and rates and all the random weird behavior from individuals—the fact that we are essentially using an average—helps to smooth the variations in individual loans. One person may pay it off really early, sure. But another person will keep it for the full term. In the end, it all averages out, and this provides us with a reasonable ballpark figure for the life of the loan.Life of loan for open-ended loansIf you wanted to get fancy with your calculation, life of loan for open-ended loans can be one of the more complicated topics surrounding CECL. The reason it’s so difficult to get a truly accurate picture of the life of the loan is because there is potential for a constant drawing and paying down of credit, as well as the ability to carry a balance or pay off the balance every period. Without the ability to track individual draws on the loan, and then how each payment is applied to each draw, the best we can do is estimate the life of a loan. You may have that ability within your core system. This document assumes you don’t, and that you need a far easier way to estimate the life of loan.The thing is, we’re trying to keep this simple. We’re small credit unions, here. We don’t want to get very fancy. We want the fastest, easiest way to calculate our life of loan, and simplify our lives. So, herein are described two methods to potentially measure the life of an open-ended loan. They are greatly simplified methods, meant to be completed by hand, in a spreadsheet. There are other, more complicated ways that likely provide more accuracy, but those likely require much more data and would be difficult to complete in a spreadsheet. These methods tend to get you into a conservative ballpark.A note on open-ended lines of creditThe accounting standard indicates that you must consider unfunded portions of lines of credit in your ALLL calculation “unless that credit obligation is unconditionally cancellable.” That means that unless you can cut off the line of credit at any time, you’ve got to consider the credit risk you’re exposed to by loans your members might take out in the future. For example, if you offer credit cards, but at any point in time can cut those off, so the members can’t use them any more, you don’t have to consider the unfunded portion of their lines of credit. If you can’t close off the line of credit at any point, you have to consider the unfunded portion of the line of credit. The same goes for something like HELOCs.Currently, this document is limited in scope to open-ended loans that are unconditionally cancellable. Most conservative approachThe most conservative way to estimate the life of an open-ended loan is to figure the maximum payoff term given the current balance. This is a maximally conservative approach to finding the life of the loan for credit cards because it assumes that everything will stay on the books for the maximum term. The reality, of course, is that very few loans stay on the books for the maximum term. In simplest terms, here’s how to do it:Find the average balance for the loans in the segment. To do this:Total the balances of all the loans in the segment.Count how many loans are in the segment.Divide the figure in step a by the figure in step b.Using the contractual minimum payment amount, calculate how long it would take a member to pay off a loan of the average balance without adding any more principal.An amortization schedule would be a great way to do this. An example of an amortization schedule is on the spreadsheet, on the green tab titled “Life of loan pmnts & balances.” The amortization schedule on that page isn’t necessarily for open-ended loans, but it works the same.To create an amortization schedule, you will need an interest rate to use in the calculation. If different loans have different rates (and they probably do), you’ll need to find the weighted average interest rate for the loans. In the spreadsheet, a weighted average rate of many loans is calculated on the green tab titled “Weighted average rate” in cell D6. Work your way through how that number was created, and read the section in this document on weighted averages, for how to find the weighted average rate of a portfolio of loans.If different loans have different repayment terms (minimum payments, especially), you will need to take that into account. You can either try to find the weighted average across all loans or you can separate the loans into different segments. The process above is relatively simple for a homogenous group of loans. For example, if your credit cards all have the same repayment terms (minimum payment terms), it’s easy to figure the average balance and weighted average rate for the credit cards, and then calculate how long it will take to repay the cards. But where the segment of loans isn’t homogenous, it gets complicated. For example, some HELOCs have a draw period of 1 year. Others have a draw period of 5 years or even 10 years. Some HELOCs have repayment terms that only require payment of interest during the draw period, while others require payment on principal and interest during the draw period. Also, HELOCs vary on the repayment period after the draw period has ended—it might be 5 or 10 years or anything else.A credit union with multiple types of HELOCs on the books should probably have a different segment for each type of HELOC, or create a weighted average life and rate for all HELOCs. The same goes for credit cards and other open-ended loans. Anyway, this method for calculating the life of the loan is the maximum possible life of the loan. The chances that your open-ended loans have a life that long are pretty small. Payments and balancesThis is the same method used in the closed-end loans: divide the total balance of the segment by the amount the principal was paid down in a period. See the section above for specific steps.This method may seem far too simple to calculate life of loan for open-ended loans, since they have the potential for more draws, etc. There are certainly some who would say it’s not adequate. The important thing is to remember that we’re trying to simplify the CECL calculation. You could spend all kind of effort figuring the life of loan for open-ended loans, but what we’re trying to do here is figure the life of loan in a simplified manner.This is simplified, yes, but it also can help you get a good idea for the overall picture because if you take the total balances of the open-ended loans at any given moment, that will include some balances that will be paid off before the grace period ends, and some that will be carried forward. Likewise, the total payments represent some minimum payments and some payments that will completely pay off the balance on a card. As you consider many periods—average them together—you get a representation of what happens over time—how balances increase or decrease or get paid off over time. Is it perfect? No. But it’s simplified and creates a picture. This argument may be more justifiable if your segment of loans is unconditionally cancelable. If, at any time, you can prevent members from adding to the balance, then suddenly they become like a closed-end loan, and this method for calculating life of loan is pretty sound.If an examiner or CPA takes issue with this methodology, saying it creates a life of loan that’s too short, try combining the result this method gave you with the result that the most conservative method gives you. That is, average the two values together. It’s more conservative than this method, and certainly closer to the truth than the most conservative approach. ForecastThis is the most difficult new CECL requirement to meet. But don’t worry—in the past, credit unions have needed quantitative and/or qualitative environmental factors with which to adjust their loan losses, and this forecast business is basically the same thing hopped up on steroids.If you read through this section and are just confused or distraught, then forget about it for now. Seriously. First, get a handle on the methodologies (and ignore the steps that tell you to consider your forecast). Those are probably the most important parts of this document. Once you get a handle on those, come back here and try to get a grip on the concepts in this section.On the other hand, this section will appeal to people who have in the past found qualitative and environmental factors frustrating. Examiners want credit unions to adjust their ALLL based on this or that environmental factor, but it often doesn’t feel scientific. It feels hand-wavy and arbitrary, based on the examiner’s licking a finger and holding it up to gauge the wind. The nice thing about this model is that it provides a fairly rigorous, concrete way to incorporate “what’s going on in the economy” and what economists think will happen.This document explains one way it can work, the difficulties a small credit union may encounter, and some options for small credit unions. There are certainly other methods you might use, but this document sticks to just one that is simple enough that anyone can do it.Here’s the short version about how this particular method works:Find an economic indicator that correlates with your losses.Build a model showing the relationship between your losses and the indicator.Find a forecast of the indicator. As you calculate your reserve needs, use the forecast to validate or adjust your amounts. About economic indicatorsThis forecast hinges on an indicator, which is some measure of how the economy is doing. Most people are already familiar with many indicators. Common ones are the: Unemployment rate National gross domestic product (GDP)Others include the:Federal funds rateCore inflation rateHouse pricing index (HPI)SNAP benefitsNonfarm payrollsBankruptcy filingsEtc.Any of those might be used on a national or local level, and all are pretty broad. The broad indicators are pretty easy to find. A great place for indicators is FRED: Federal Reserve Economic Data (). There, you can download the history of many data points.Local data may be harder to find, especially if it’s related specifically to a certain industry or plant or factory. You may have to do some digging. You’ll also want to make sure that a forecast of that indicator is also available—which could prove difficult.Is the indicator any good for your credit union?Once you have an indicator that you’d like to use, the next step is to make sure that it actually bears a relationship to your credit union’s loss rates. If it doesn’t you’ll need to keep looking. A decent way to measure this is with correlation. (Incredibly and unexpectedly, that college statistics class suddenly seems relevant.)Correlation is easy to calculate with the CORREL function in Excel. You can find plenty of information about how to use that function on the Internet or in the Excel Help files. You’ll want to make sure that your correlation is relatively strong. As a reminder (or very brief overview), correlation scores range from -1 to 1. A perfect 1 or -1 would be ideal. Anything over .8 or under -.8 is certainly good. When you land between 0.5 and -0.5, things are looking dicey—there may not be much of a relationship between the indicator and your write offs, and you probably need to find a new indicator. If you can’t find a good indicator, there is a work around, described below.Notably, you could use a different indicator for each loan segment. For example, the house-pricing index may be a good indicator for your mortgage loans, while the unemployment rate may be a good indicator for personal loans. That is, if you’re willing to go through the work to find a different indicator for each segment. I would contend that for a small credit union, it’s not necessary (and probably not possible).To see what the result would be, we took 10 indicators and examined their numbers and the change in their numbers, and correlated those with the total write offs of more than 60 credit unions in Utah. There’s good news and bad news.The good news is that there are some very good indicators for many credit unions. They can correlate their write offs with some very prominent measurements that are regularly forecasted and readily available.The bad news is that the smaller the credit union, the less likely there is to be a correlation with these economic indicators. This means that those credit unions will likely have to work harder to find something that will help them forecast. In many cases, these economic indicators may be very small scale—something specific to the credit union’s geographic area or field of membership. In such cases, it may be difficult to obtain reliable historical data, as well as a reasonable and supportable forecast. (But there is a work around, described below.)If you can’t find an economic indicator that correlates well with your credit union’s losses, you have an option, described below. The sections below (Building a model, and Applying the forecast and baseline model, and Mean reversion), explain how to build the model, so you have a good foundation in how it works, and then explain how small credit unions can use a forecast. Building a modelBuilding a model from one indicator is relatively easy once you know how to do it. You’ll want to take all your data points from your indicator, and your write off data, and make a scatterplot with them. If you don’t know how to do that, ask the Internet or the Excel Help files. Just make sure that your charge-off rate is on the y axis. An example of this is in the spreadsheet, on the red tab titled “Building the equation.” In this example, a credit union finds that its charge offs are moderately correlated with the national unemployment rate (correlation coefficient of 0.55; not weak, but not exactly amazing). It creates a scatter plot of its charge off data and the unemployment rate, and adds a trend line with an equation. This shows the relationship between the loan write offs and the economic indicator, and provides a means to predict how the portfolio will perform in the future. (The above is true when using a single indicator. Using more than one complicates things significantly.) The following is very important: that equation from the trend line (y = 0.038x – 0.0008) is the model. This is what we will use to make predictions of our future write-off rates.Applying the forecast and baseline modelUsing the equation (aka, the model), create a table that shows the write-off rates that would be associated with various unemployment rates. Just plug the predicted unemployment rate into the equation in the place of the x. For example, if unemployment is predicted to be 4.20%, the equation is (0.038*0.042)-0.0008. (Incredibly and unexpectedly, that high school algebra class suddenly seems relevant.)Here is part of the chart that results from various unemployment rates for this example:Unemploy-ment ratePredicted charge-off rate for individual CU4.00%0.07%4.10%0.08%4.20%0.08%4.30%0.08%4.40%0.09%4.50%0.09%4.60%0.09%4.70%0.10%4.80%0.10%4.90%0.11%5.00%0.11%5.10%0.11%5.20%0.12%5.30%0.12%5.40%0.13%5.50%0.13%5.60%0.13%5.70%0.14%5.80%0.14%5.90%0.14%The prediction increases at fairly regular intervals as the unemployment rate increases because it’s based on a simple linear equation. More complex equations could also be used, depending the nature of the relationship between the economic forecasting element and the charge-off rate, but that’s beyond the scope of this document. When you create the trendline on the scatterplot in Excel, you could experiment with the different types of trendlines. You may find that something logarithmic, exponential, polynomial, or power works better for you. Feel free to use any of those, if you like. This demonstration sticks with linear.An example of this is in the spreadsheet, on the red tab titled “Model.” For now, just worry about column B.Note that the forecasts above are annualized quarterly charge off rates.We will use this table to predict our future write-off rates based on the predictions of the future unemployment rate. For example, if the unemployment rate is 6.5% for a given quarter, then the associated annualized write-off rate would be 0.17% for the quarter. We’ll figure this for all the relevant future periods, multiply the total by our total loan balance, and wham-o!, we have our prediction for write-offs. Mean reversionThe pundits seem to agree that predictions reaching further than two years are not very useful. As a result, you’ll use forecasts for the next two years. So, what do you do if the life of a loan segment is longer than two years? You do something that the smart kids call “mean reversion.” (Throw that term at your examiners, and they’ll think you know your stuff.)Mean reversion is the process of moving a number toward its long-term average. For example, if the prediction for unemployment is 4% for the next two years, you would use that prediction to find your predicted write offs for two years. After that, however, you ignore predictions and gradually move the prediction for unemployment toward the long-term average, which we’ll pretend is 6%. So, over the course of years 3 and 4, you move to that average. The predicted rates for the next four years then become: 4.25%, 4.5%, 5.25%, and 6%. You simply move from the last reasonable prediction (at 2 years out) toward the long-term average. In other words, mean reversion is a fancy way to just say, “After two years you move your prediction toward the average”.How a small credit union can use a forecastThe biggest challenge for utilizing a forecast will be correlating your write-offs to an economic indicator. As explained above, this is easy for big credit unions and very hard for small credit unions. But there’s a reasonable away around it, and it’s not that difficult to implement. It harnesses the power of averages. Basically, this:Correlate the write-off rate of all credit unions in the country with the national unemployment rate.Build a model to predict the national credit union write-off rate using the information in the step above.Figure out how your credit union’s write-off rate is different, on average, than the national write-off rate.Take predictions of the national unemployment rate and predict the national credit union write-off rate.Knowing how your write-off rate is different than the national write-off rate, adjust the prediction made in step 4 by the figure you found in step 3.So, you’re building a model that fits all credit unions, and adjusting for how you generally differ from all credit unions. To build the model, use three pieces of information:National unemployment rate: history and forecastHistory is found here: is found here: the “10-year economic projections”, select the most recent data. The write-off rates for all credit unionsThe write-off rate for your credit unionWhen creating the forecast, there’s a challenge in that every segment of loans has a different life. How far out do you forecast, when your used auto loans have an average life of 3.1 years, and your mortgages have an average life of 5.8 years? The answer is that the overall forecast uses the weighted average life of loans, which is, on average, how long a dollar lent out at your credit union remains lent out. The process for building the forecastThis process looks pretty intimidating, but don’t stress about it. It’s broken it down into sections and short steps, many of which are basic math—divide this by that, add this to that. You can do it. Remember, you only have to do the above about once a year. Once you have it set up, you’re just updating the numbers. Easy.Build the model/equationThe first steps below are shown in the red “Building the equation” tab of the spreadsheet. List the unemployment rate for as many periods as you want to use in your calculation. For corresponding periods, list the write-off rate for all credit unions. For corresponding periods, list the write-off rate for your credit union. Using the data you gathered, calculate the average write-off rate for all credit unions across the country. Using the data you gathered, calculate your credit union’s average write-off rate. Using the data you gathered, calculate the average unemployment rate. Note that in the spreadsheet the monthly unemployment data is averaged every three months to find the average quarterly unemployment rate.Find the difference between the numbers found in steps 4 and 5. Find an equation that shows the relationship between all credit union write-offs and the national unemployment rate. In Excel, create a scatter plot of historical unemployment data and historical credit union write-offs, with unemployment on the X axis.Add a trend line to the plot, and display the equation. The equation is the model you’ve been searching for.Make general predictionsWe did the hard stuff in the last section, and here we’ll simply create a lookup table that tells us what the write-off rate will be given various potential unemployment rates. We will reference this table in the next section. These steps are done in the red “Model” tab of the worksheet:Using the equation, build a table of unemployment rates and corresponding national write off rates. In Column A, create a list of feasible unemployment rates.In Column C, use the equation to predict national credit union write-off rates. Do this by plugging the potential unemployment rate in column A into the equation (the unemployment rate is the x in the equation).Add the difference between your credit union’s write-off rate and the national write-off rate to the table in Column D.Note that columns E, shows a model that adds a floor to the predictions. The reason is that without a floor, the model quite frequently predicts a negative write-off rate. This is really pretty rare, so instead we estimate conservatively that the least we will ever write off is a certain amount. That certain amount could be figured in many ways: the average write-off rate, half the average write-off rate, one standard deviation below the average write-off rate, etc. For the rest of this document, we will simply utilize the model that has a floor of 0.09% (half of this credit union’s average write-off rate). You will need to make a determination on what model you want to use. Examiners will like a more conservative method.Forecast write-off ratesHere we will take the prediction of the unemployment rate, and use the table made in the previous section to specify what our predicted write-off rates will be for coming years.These steps are done in the red “Forecasted write-off rates” tab. List the predicted unemployment rate for the next two years (you’ll have to find that prediction from a reliable source). For the 2 years following (years 3 and 4), predict the unemployment rate to move toward the long-term average, so that in the 4th year from the current year, the unemployment rate is the long-term average. For the years following (maybe go all the way out to 10 years), list the predicted unemployment rate to be the long-term average. HYPERLINK \l "predictiontable" Using the table created in the previous section, and using the forecast for the unemployment rate, predict and record your credit union’s write-off rate for the next several years. After the 5th year, it’s going to be based off the average unemployment rate, and therefore the same every year, so you might as well just go out 10 years or so. That should cover all the loans on your books.Put it all togetherThe first step, below, is done on the “Weighted average life of loan” tab. Note that you will have to calculate the average life of each loan segment elsewhere. The last two steps are done back on the “Forecasted write-off rates” tab.Find the weighted average life of loans. This number is, on average, how long you expect a dollar loaned out to stay on your books. In the spreadsheet, it’s in the red “Weighted average life of loan” tab, in cell E8Using the yearly forecasts, determine what % of your current loans are forecasted to be written off over the average weighted life of loans. For example, if it’s 2017, and the average weighted life of loans is 4.67 years, sum the predicted write off rates for 2018, 2019, 2020, and 2021. Then multiply the 2022 rate by 0.67 (since the loans will be done 67% into the year), and add it to the sum of the 4 whole years.Multiply your current total loan balance by the % found in the step above. In the end, you will have a dollar amount, a prediction for what your write-offs might be over the lives of your current loans. You could almost end right here. You have a prediction for your future charge-offs. If you have a very simple balance sheet with one or two kinds of loans, this would probably be adequate. However, changes are that you have a little more complex balance sheet, so we’re not quite done yet.What to do with the forecastThere are many ways you might incorporate the forecast into the methodology, but it’s important to remember that we’re trying to keep things simple. To this end, we will keep the forecast and the methodology separate. But, somehow we have to incorporate the forecast into our final figure. Or, at least, we have to consider it. If we’ve already used one of the three methodologies outlined in the sections above, we’ll take the figure the methodology arrived at, and compare it to the figure arrived at in the forecast. Then we’ll make an evaluation to determine which to use or how to use it. It’s likely that the two figures will be different. How do we decide which to use, or how to adjust them?Remember that the forecast utilized quite a long history of the unemployment rate and the credit union’s and industry’s charge-off rates. That’s how we established the pattern and built the model. On the other hand, the methodology will likely only use a few years’ worth of data to predict charge-offs. And, those recent years of data may not reflect future periods. That’s the important thing to remember. The three or five years we tracked write-offs over, in the historical or vintage or snapshot methods—those years may be different than what the future is predicted to be like.So, what we’ll do is consider the history over which we tracked our write-offs, and compare that to the predicted future, and then adjust our figures accordingly.For example, the historical method detailed below uses a three-year history to calculate the expected write-offs. Over that three-year history, the unemployment rate has been 4.76%. On the other hand, the forecast for the next several years indicates that the unemployment rate will average 5.22% per year. Because of this—and because we know that when the unemployment rate goes up our write-offs generally go up—we can logically conclude that the historical methodology will underestimate our write-offs. Therefore, if forecast amount is higher than what the historical methodology estimated, we should adjust our historical method’s amount toward (or maybe even to) the amount the forecast predicted.On the other hand, if the unemployment rate during period over which we built the prediction had a higher unemployment rate than the predicted unemployment rate, then that prediction will likely be higher than what the forecast shows, and it may become necessary to adjust the write-off amounts lower, toward that forecast.How much to adjust the predicted amount? It’s rather up to you. I’d encourage you to find a methodology that works for you. You may:Adjust the amount based on the difference between the past unemployment rate and the predicted future unemployment rate. For example, if the future unemployment rate is 25% higher than the past, maybe you adjust your methodology’s amount up by 25%, or up to the amount the forecast came up with, whichever is higher.You may simply take the larger of the two figures. This is a conservative approach.Average the two figures together.Whatever you choose, come up with a sound rationale, and make sure there is a consistent way to apply it. Forecast summarySo, in summary, you:Built a model to predict write-offs at all credit unionsFigured out how your write-offs are different, and based on that difference created a model for your credit unionFound a prediction of the unemployment rateUsed the prediction of the unemployment rate in conjunction with the model to predict your write-offs for future years, based on the loans you have on your books right nowThat’s a lot of predictions and predicting. But that’s the point—we’re trying to find out how much we expect to write off in coming years. DataMuch has been said about data and beginning to gather data now. Hopefully, as you’ve looked at the different methodologies, you have an idea for what kind of data you’ll need to gather. Some of it depends on what methodology you use.For example, the historical method requires that you calculate the life of the loan segment. The methodology described in this document requires that you be able to find the average weighted rate of the loans, which in turn requires that you have the interest rate for each loan in a segment, and other pieces of information: the starting balance of the loan, the balance at a certain point, the amount of payment, etc.On the other hand, the snapshot and vintage methods require that you assign loans to a vintage or a snapshot, and track the write-offs of those loans in connection with a specific vintage or snapshot. This will require you to have the date of the write-off and the origination date of the loan, along with the original balance.So, what data do you need to gather? It depends on the methodology you choose. Once you understand the methodology, you should understand what data you need to collect. It may be that all the data you need is in a single Aires file. Take a look at what’s in that file, and compare it to what you need. With a little luck, that may be enough.About weighted averagesA few places throughout this document recommend using a “weighted average” or “weighting” calculations. This concept is best illustrated with an example.What a credit union told you that the average rate of its auto loans was 10%. You would, no doubt, wonder what was going on at the credit union until you received further data. Turns out, the credit union has two auto loans on its books:Balance: $1,000. Rate: 18%Balance: $75,000. Rate: 2%While 10% does accurately describe the average rates on the auto loans, it doesn’t give the entire picture because the balances on those two loans are so different. This is because we give equal weight to both loans, when one is clearly much more important than the other.The best way to get a true picture of the situation is to weigh each loan based on how important it is in the overall scheme of things. There are several ways to do this, and the Wikipedia has some pretty good explanations: . This document tends to use the “convex combination” method.The example in the table below demonstrates this. In the 4th column, titled “% of all loans”, each segment is given a weight based on what % of the total loan balance the segment represents. These percentages equal 100% when added together. Each segment’s percentage is multiplied by the life of the loan, to give the weighted life of the loan. Add those weighted life of loan values together, and you get the average weighted life of each dollar lent out. In this case, it’s 3.2 years. This value, 3.2, takes into account the relatively low amount of “Other” loans, which have a shorter life, and the relatively larger amount of “Real estate” loans, which have a longer life. It figures that, given the balance of each type of loan, and how long each type of loan typically lasts, in general the average dollar lent out is lent for 3.2 years.SegmentTotal balance of segmentLife of segment% of all loansWeighted life of loanNew auto $2,300,000 39.7%0.3Used auto $8,600,000 336.3%1.1Real estate $7,800,000 532.9%1.6Personal $4,000,000 116.9%0.2Other $1,000,000 14.2%0.0Total $23,700,000 2.6100%3.2Average weighted life of a loan dollarWeighted averages tend to create a much better picture than a simple average, without adding very many steps or much complexity, and it’s worth using them in many cases.Below, a weighted average interest rate as calculated. It’s the final cell in the lower right corner. It was arrived at in the same method—weighting the rate of each group based on the percent of the total balance the group comprises. The weight in the third column is arrived at by dividing the group’s balance in the second column by the total balances at the bottom of the second column.RatesBalancesWeightWeight times rate9.90% $1,000,000 0.51 5.1%10.90% $300,000 0.15 1.7%13.90% $500,000 0.26 3.6%17.90% $150,000 0.08 1.4%Total $1,950,000 11.7% weighted rate ................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.