Estimating the Cost of Funds for Loan Pricing
Whether your institution sets pricing by a variation of cost of funds plus target, or by demand-based optimization, it is important to use the correct cost of funds in the calculation–a single cost of funds cannot be used for all loan products. A 30-year mortgage and a 5-year auto loan have very different interest rate risk characteristics, just as they have very different credit risk characteristics. The cost of hedging interest rate risk must be included in the cost of funds in order to accurately compare the profitability of different loan types in the same way that cost of hedging credit risk is included in the loan price.
The article that follows describes how to calculate a cost of funds for a loan product that includes the cost of hedging interest rate risk. The article is divided into the following sections:
- Interest Rate Risk Hedging Assumptions in Cost of Funds
- Estimating the Principal Pay-down Schedule
- Interpolating the Yield Curve to Fit the Pay-down Schedule
- Calculating the Interest Rate Risk-Weighted Cost of Funds
- Conclusions
- Notes
Interest Rate Risk Hedging Assumptions in Cost of Funds
Hedging interest rate risk requires either funding the loan with deposits that will mature on the same schedule that the loan will pay down or purchasing an interest rate swap or some other instrument to hedge the interest rate risk. Performing the calculation with the funding approach is much easier; in this approach the interest rate risk weighted cost of funds is the average of the deposit yield curve weighted by the the principal payment schedule for the loan product. A better name might be the “pay-down weighted cost of funds.”
Unfortunately, even this approach requires some knowledge of a derivative call an interest rate swap; swaps are perhaps the best publicly available estimate for the top of the certificate of deposit market, and are thus the easiest way to come up with a deposit yield curve. Swap rates are very useful in transfer pricing and interest rate risk calculations, so it is important to understand how they work.
In an interest rate swap, two parties enter into a contract to pay one another the interest on a fixed balance: party A pays party B the current floating market interest rate, usually London Interbank Overnight Rate (LIBOR) while party B pays party A a fixed interest rate negotiated at the start of the contract. The two institutions “swap” their interest rates to hedge their respective interest rate risks. If party A has a term CD deposit, but a floating rate loan, party A will lose money in a falling rate environment. With a swap, as rates fall, party A will pay less but continue to receive the fixed rate on the swap which will offset the loss in margin on the loan. Similarly, if party B has floating rate deposits and fixed rate loans, party B will lose money as rates rise. With a swap, party B will receive more from the swap as rates rise which will offset the losses on the fixed-rate loan.
If implemented correctly, swaps can be an effective tool in hedging interest rate risk. Implemented incorrectly or when used speculatively, they can bankrupt an institution.
Because swap contracts are readily available and have an established market, it is rare that an institution would pay significantly more than the market swap rate on CD products as part of a funding program. Small institutions may pay about 40 basis points over swap rates in various fees to wholesale providers, but it is unusual for an institution to go any higher in pricing CDs. Swap rates are the best rate to use for transfer prices and interest rate hedging calculations because a bank can both buy and sell funds at this price. The remaining calculations use swap rates to calculate the interest rate risk weighted cost of funds.
Estimating the Principal Pay-down Schedule
The first step in calculating the interest rate risk weighted cost of funds is determining the risk weights–the principal payment schedule for a loan portfolio over the period when the portfolio pays down. It is important to use actual data in this calculation and to include prepayments (and charge-offs) in the estimate. Figure 1 shows the pay-down schedule for a loan pool with no prepayments, with 10% per year in prepayments and for balloon loan (50% of principal in final period) with no prepayments. For short-term loans, estimating prepayments is not critical, but this estimate becomes more important as the length of the loan product increases.
Figure 2 shows the per-period principal pay-down up to the final period where the scale of the $20,000 balloon distorts the scale. The principal pay-down amounts are the weights that will be used for the weighted-average cost of funds.
It is important to note that the prepayment rate can change dramatically as economic conditions change; in a declining economy, prepayments due to charge-offs increase due to job losses, and prepayments due to refinancing increase as interest rates decline. You should work to estimate prepayments as accurately as possible, but how to do that is beyond the scope of this article.
Interpolating the Yield Curve to Fit the Pay-down Schedule
The second step in calculating the interest rate risk weighted cost of funds is to obtain a yield curve for funding sources and then interpolate values for missing portions of the curve. We need to get estimates for rates at all points on the outstanding balance curve shown in Figure 1, but interest rates are routinely available only in 1-year increments. Figure 3 shows the swap rates that are available on the St. Louis Federal Reserve Bank’s FRED 2 research system for December, 2004: overnight, 1-, 2-, 3-, 4-, 5-, 7-, 10- and 30-year. An inspection of the data points in Figure 3 shows a relatively traditional yield curve increasing rapidly in the short term and then flattening out beyond 10 years. It is instructive that the most recent “normal” yield curve occurred 10 years ago.
The easiest way to interpolate the intermediate values is to use a regression model based upon the data points available. In most cases, using a straight-line regression model will give a perfectly reasonable estimate of the intermediate values, but if you have five or more data points, a quadratic, cubic or especially a spline model may work somewhat better. The sections that follow show R code for each of the methods, while Figure 4 compares the fit of the various interpolation models. Whatever interpolation approach you take, make sure to include an interest rate at or beyond the length of the loan; interpolating between points is generally quite safe, but extrapolating beyond available data is generally perilous. If the 60-month swap rate were taken out, it is likely that both interpolation methods would over estimate the 60-month rate by about 0.5%–a big difference.
To do the interpolation, you will need to create a data set or in R a data frame that contains the period of the observation–in this case 0, 12, 24, 36, 48, 60, 84, 120 or 360 months (variable is the FRED2 series and period is the numerical value for the term) and the interest rate (the value column). Depending upon what tools you have available for the curve fitting and interpolation, you may need to add columns for the square of the period (period2 in this example) and the cube of the period (period3 in this example):
summary(swpDf)
## dateVal variable value period period2 period3 ## Min. :2004-12-01 MSWP30 :1 Min. :2.09 Min. : 0.0 Min. : 0 Min. : 0 ## 1st Qu.:2004-12-01 MSWP10 :1 1st Qu.:3.38 1st Qu.: 24.0 1st Qu.: 576 1st Qu.: 13824 ## Median :2004-12-01 MSWP7 :1 Median :3.81 Median : 48.0 Median : 2304 Median : 110592 ## Mean :2004-12-01 MSWP5 :1 Mean :3.79 Mean : 82.7 Mean : 17664 Mean : 5485056 ## 3rd Qu.:2004-12-01 MSWP4 :1 3rd Qu.:4.29 3rd Qu.: 84.0 3rd Qu.: 7056 3rd Qu.: 592704 ## Max. :2004-12-01 MSWP3 :1 Max. :5.25 Max. :360.0 Max. :129600 Max. :46656000 ## (Other):3
The sections below describe four different ways to fit the yield curve so that you can interpolate intermediate values. The first three are readily possible in Excel, IBM’s DB2 database management system (DBMS), Oracle’s DBMS, MySQL and PostgreSQL, but the final–and best– will probably require R or some other statistical package. Some DBMS packages allow calls to R or other statistical packages via custom programming interfaces. The various methods are discussed in the following sections:
- Linear Curve Fitting and Interpolation
- Quadratic Curve Fitting and Interpolation
- Cubic Curve Fitting and Interpolation
- Spline Curve Fitting and Interpolation
- Comparison of Curve Fitting and Interpolation Methods
Linear Curve Fitting and Interpolation
A simple linear regression is the easiest of the interpolation methods. The R call to do this and the regression coefficients are shown below:
# # Show the results of the linear interpolation # ycM <- glm(value~period,data=swpDf) ycM
## ## Call: glm(formula = value ~ period, data = swpDf) ## ## Coefficients: ## (Intercept) period ## 3.22588 0.00677 ## ## Degrees of Freedom: 8 Total (i.e. Null); 7 Residual ## Null Deviance: 6.82 ## Residual Deviance: 2.36 AIC: 19.5
The regression line is of the form
\[ \begin{aligned} \text{interpolated rate}&=&\text{(intercept + coefficient*period)} \\ &=&3.226\text{ + }0.006765\text{*period} \end{aligned} \]Quadratic Curve Fitting and Interpolation
Similarly, the quadratic regression just adds the square of the period to the formula for the call. The R call and coefficients are shown below:
# # Show the results of the quadratic interpolation # ycM <- glm(value~period+period2,data=swpDf) ycM
## ## Call: glm(formula = value ~ period + period2, data = swpDf) ## ## Coefficients: ## (Intercept) period period2 ## 2.59e+00 2.49e-02 -4.88e-05 ## ## Degrees of Freedom: 8 Total (i.e. Null); 6 Residual ## Null Deviance: 6.82 ## Residual Deviance: 0.445 AIC: 6.47
The quadratic regression curve is of the form
\[ \begin{aligned} \text{interpolated rate}&=&\text{intercept + coefficient1*period + coefficient2*period}^2 \\ &=&2.588 \text{ + }0.02492\text{*period + }-4.885e-05\text{*period}^2 \end{aligned} \]Cubic Curve Fitting and Interpolation
Similarly, the cubic regression just adds the square and cube of the period to the formula for the call:
# # Show the results of the cubic interpolation # ycM <- glm(value~period+period2+period3,data=swpDf) ycM
## ## Call: glm(formula = value ~ period + period2 + period3, data = swpDf) ## ## Coefficients: ## (Intercept) period period2 period3 ## 2.32e+00 4.39e-02 -2.67e-04 4.66e-07 ## ## Degrees of Freedom: 8 Total (i.e. Null); 5 Residual ## Null Deviance: 6.82 ## Residual Deviance: 0.156 AIC: -0.947
The cubic regression curve is of the form
\[ \begin{aligned} \text{interpolated rate}&=&\text{intercept + coefficient1*period + coefficient2*period}^2 \text{ + coefficient3*period}^3 \\ &=&2.318 \text{ + }0.04389\text{*period + }-0.0002671\text{*period}^2 \text{ + }4.661e-07\text{*period}^3 \end{aligned} \]Spline Curve Fitting and Interpolation
The spline method is somewhat more complex. A simple description is that it does a cubic regression over portions of the data points to achieve a near perfect fit for that segment of the data, and then does another cubic regression over the next portion of the data until it has fitted the entire range of data points. The R call and output are shown below:
# # Show the results of the spline interpolation # #ycM <- interpSpline(value~period,swpDf) #ycM <- bs(swpDf$value) ycM <- smooth.spline(swpDf$period,swpDf$value) ycM
## Call: ## smooth.spline(x = swpDf$period, y = swpDf$value) ## ## Smoothing Parameter spar= -0.5829 lambda= 2.369e-12 (22 iterations) ## Equivalent Degrees of Freedom (Df): 9 ## Penalized Criterion: 1.997e-14 ## GCV: 1.707e+13
The spline curve uses a cubic fit for each point, so it has four terms for each data point. Spline curves generally provide the best interpolation, but it is important to test and visualize the fit as shown in Figure 4.
Comparison of Interpolation Methods
Figure 4 shows the actual and interpolated values for the various curve fitting methods. For fitting and interpolation over the region of 0 to 10 years, all of the methods do a reasonable job, but when you add the 30-year swap rate, only the spline method works well. For yield curve data, the spline method will generally give the best results, but it is also the most difficult to implement, as it requires a true statistics language.
Calculating the Interest Rate Risk-Weighted Cost of Funds
The final step is to apply the risk weights obtained from the pay-down curve to the yield curve. This is just a weighted average:
\[ \begin{aligned} \text{interest rate risk weighted COF}&=&\frac{\sum_{i=0}^{n} \text{(period pay-down)}_i\text{*(interpolated rate)}_i}{\sum_{i=0}^{n} \text{(period pay-down)}_i} \end{aligned} \]For this example, with no prepayments, the interest rate risk weighted cost of funds is 3.417% while with prepayments it is 3.295%. The total interest cost difference is $111.09 compared to a total interest income of $7522.88 for the prepayment case. Not estimating prepayments in this case would result in an error on the order of -1.477%. A difference of 0.1216% doesn't sound like much until you compare it to the return on assets for an institution, where a 0.1% difference translates into the difference between a good year and bad year. Errors in estimates for prepayments can make a significant difference as the length of the loan increases or when comparing loans of very different durations.
The balloon loan has a cost of funds of 3.703%. Comparing the balloon loan to the normal loan gives a larger but still small difference: 0.2867% which results in an interest income difference of $445.54. Accurate prepayment rates are especially important for leases and for balloon loans.
Conclusions
The procedure for estimating an interest rate risk adjusted cost of funds is a relatively simple calculation; in practice, the only complexity is that you must do the calculation for each product in your portfolio. Until even a few years ago, the regression and interpolation steps in the process made this a difficult task, but with the statistical capabilities in R, Python and other languages, doing this calculation is now fairly simple to automate.
Although it is not a major issue for short-term loans, estimating prepayments accurately becomes increasingly important as the term of a loan product increases. It is impossible to completely hedge interest rate risk due to uncertainty in prepayments, but getting a good estimate to start out can help significantly in hedging interest rate risk, and ensuring that this risk is priced into the loan product.
Notes
The charts in this article were prepared using R, RStudio and the knitr package to integrate text, calculated values and graphics. The charts use the ggplot2 package. Interest rates for the swap rate data on the St. Louis Federal Reserve Bank Fred2 system were obtained using the fredSeries command in the FinCal package.
The formula displays in this example are formatted using MathJax. If the formulas do not display in a recognizable way, you should check your browser to make sure that JavaScript is enabled; MathJax requires JavaScript to render the equations. If you want to copy the math displays, right click on the equation and you will get a menu of options. MathML can be imported into many versions of Microsoft Word by copying the MathML to the clipboard and pasting it into Word using the "Keep Text Only" paste option. It can also by copied and pasted in LaTeX format.
Run Time
## user system elapsed ## 4.248 0.080 6.740