# 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

- Details
- Written by Bruce Moore
- Hits: 6378

# Business Card Design

Business card design for a small business can be very quick, but a little time and thought can pay significant dividends. Shortly before I contracted with a graphics firm for a logo and business card design, I attended a professional conference and came back with a collection of business cards, most of which were poorly designed in one of several ways:

- The font was too small–anyone who uses reading glasses would need them to read the card.
- There was nowhere to right notes. On many cards, the back was printed in a dark color or had a design that made it impossible to write notes on the card, such as the question the person needed answered.
- When I tried to scan them in using an automated scanner, many cards wouldn't correctly OCR due to fonts that were too small, abbreviations that were confused for names and a variety of other problems.

The article that follows describes some of the things to consider when designing your business cards, and is divided into the following sections:

- The Front Side
- The Back Side
- Generating a Two-Dimensional Barcode (QR Code) of Your Contact Information

## The Front Side

In designing the front of your card, make sure that you use a font that is large enough that people can read the card. Particularly in business-to-business, most decision makers have reached an age when they need reading glasses–don’t force them to pull out glasses to read your card. Make sure to use a font that is readable and which will OCR. Most decision makers won’t have a card scanner, but most sales people at trade shows probably will. If you indicated that you want them to call you by giving the sales rep a card, make it even easier by giving them a card that will OCR.

Finally, make sure to leave white space for writing in notes on the question that you answered or updates to a phone number or email address.

## The Back Side

Many cards are blank on the back to allow space for writing notes. If you work internationally, put another language on the back. If not, you can generally use some of this space for other purposes. Consider generating a two-dimensional barcode (QR code) of your contact information, so that anyone with a QR reader on their smartphone can import your card accurately without typing. You might also consider putting a diagram or some other graphic that is part of your elevator pitch.

Make sure to leave space for writing notes.

## Generating a Two-Dimensional Barcode (QR Code) of Your Contact Information

If you decide to put a QR code of your contact information or web site on the back of the card, make sure to keep the file small. Many QR code readers will read a .vcf file with contact information, but keep it small. Although the standard for .vcf files allows up to 4K and can include an image, the space on a business card is only big enough to handle a .vcf file that is about 300 characters. Test your QR code on several phones using several different QR code readers; many readers don't support the full .vcf standard, so you will have to experiment to make sure that your QR code will import into contacts correctly.

An example of a small .vcf file follows:

BEGIN:VCARD

VERSION:3.0

N:Name;Your

FN:Your Name

ORG:Your Company Name, LLC

TITLE:Principal

ADR:;;P.O. Box 1;Your Town;TX;00000;USA

TEL:(555) 555-5555

TEL;CELL:(555) 555-5555

EMAIL;INTERNET:This email address is being protected from spambots. You need JavaScript enabled to view it.

URL:https://www.domain.com

END:VCARD

There are a number of online QR code generators, but if you are using Linux, OS X or Cygwin on Windows, the `qrencode`

package will generate QR codes of whatever text string you pass to it. For a contact QR code, you might use

`qrencode -o outfile.png < your_name.vcf`

## Conclusions

With a little thought, you business cards can become a much more useful sales tool.

- Details
- Written by Bruce Moore
- Hits: 6521

# Time-shifting Check-cashing Demand with Variable Pricing

Many banks and check cashing services are swamped with paycheck cashing transactions on Friday afternoons and Saturday mornings. It can be prohibitively expensive to hire additional staff to handle these peak periods, since even part-time staff would be idle for the rest of a 20 to 30 hour work week, and people who are qualified to be tellers can generally find full-time work. It can be difficult to determine policies that handle this peak period volume efficiently.

In some circumstances, it can make sense to charge a higher check-cashing fee during these peak times to encourage check-cashers to move their check-cashing transactions to other times when a branch is underutilized or to use less labor-intensive deposit services. The article that follows is a brief (very) summary of the findings of my Doctor of Engineering Praxis Optimal Time-variable Pricing for Check-cashing Transitions on when variable pricing works in time-shifting demand, and when it would back-fire horribly. The article is divided into the following sections:

- Product Substitution and Check Cashing
- Check-cashing Volume vs. Pricing
- Estimating Per-visit Labor Cost
- Creating an Optimization Model to Maximize Income
- When Time-variable Pricing Works, and When it Does Not
- Conclusions

## Product Substitution and Check Cashing

In designing a pricing policy, understanding product substitution is critical. When we go to the grocery store to buy milk, if the price on your favorite brand of fat-free milk is higher than you want to pay, you will probably buy or “substitute” a lower-priced brand for your favorite. The same behavior holds true in the check-cashing market: if the fee is too high, the check casher will go somewhere else or substitute a different transaction sequence for their favorite sequence as a way to avoid the check-cashing fee. Depending upon check hold policies and account rules, there are several possible substitution alternatives:

- Pay the check-cashing fee
- Deposit a portion of the check and get the remainder in cash
- Deposit the check and withdraw all or part of the check in a teller transaction on the same or a subsequent day
- Deposit the check and withdraw all or part of the amount in an ATM transaction on the same or a subsequent day
- Argue with the teller until the teller gives in and waives the fee as a way to deal with a growing line of disgruntled customers.

Because most of the substitution patterns generate an additional teller visit or require a longer teller visit, any fee increase that encourages substitution can actually increase teller workload.

## Check-cashing Volume vs. Pricing

Understanding how check-cashing demand changes as the check-cashing fee increases is the cornerstone of implementing a time-variable pricing policy. This requires historical data on fee changes and transaction volumes. At a typical bank this can become quite complex, as customers typically do multiple general ledger (GL) transactions during one visit. To get accurate demand information for check-cashing visits, the institution must first categorize all common groups of transaction into visits, and then do regression models on demand for each visit type. Categorizing GL transactions into visit types and then converting GL transaction history into visit type demand data is time-consuming and computationally intensive. Academic computer scientists would describe it as “non-trivial”, though with the advent of solid-state disk drives and inexpensive memory, this is today a much easier problem then it was in 2004.

The basic steps in this process are

- Collect GL transaction data covering the period(s) when there were check-cashing fee changes
- Collect information on dates for other policy changes and prices for other products
- Identify common groups of GL transactions that form common customer visit types. This will typically require the use of Apriori associations or some other type of associations modeling tools. This step is computationally fairly straightforward.
- Group GL transactions into common customer visit types. This step is computationally intensive, and is by far the most difficult step in the process.
- Run regression models to estimate the changes in demand as function of check-cashing fee prices. Controlling for other fee and interest rate changes can be challenging.

Before attempting to do this analysis, estimate the resources required for Step 4 and make sure that you will have the necessary computer resources before proceeding.

## Estimating Per-visit Labor Cost

To choose optimal check-cashing fee prices, an institution must estimate the labor cost for each visit type identified in the previous step. To do this, the institution must collect payroll data at the shift level in order to calculate the number of tellers working at a given time in each branch. When combined with the visit type data from the previous step, it is possible to use linear regression to estimate the number of teller minutes required for each visit type. This information can be interesting all by itself and may lead to changes in policy that are unrelated to check-cashing fees.

## Creating an Optimization Model to Maximize Income

Once the demand sensitivity data and labor cost data have been estimated, it is time to generate an optimization model that chooses a fee timetable and staffing schedule that maximizes income while meeting various constraints on minimum and maximum work-week and other work-schedule quality metrics. Even if time-variable pricing is not used, the work-schedule optimization can be beneficial to the organization.

The most natural formulation for this model is a non-linear (fee price) integer (shift scheduling) model. Because there are few non-linear integer solvers available and because this is a computationally difficult problem, it is generally better to formulate the problem as a strict integer problem. In this case, the fee price is allowed one of several discreet values, probably in 0.25% increments.

## When Time-variable Pricing Works, and When it Does Not

In general, if a bank allows deposit of a check and relatively quick withdrawal of funds, check-cashing fees can backfire due to the substitution of a deposit visit and a withdrawal visit for the single check-cashing visit. For a dedicated check-cashing service where there are no deposit accounts and this no substitution alternatives, time-variable pricing could be an efficient way to handle peak demand.

## Conclusions

Time-variable pricing probably would not work well at most banks and credit unions due to the ability of check-cashing customers to avoid the fee by substituting other groups of transactions that avoid the fee and which are more labor-intensive for the institution. For a dedicated check-cashing service, this could be a way to handle peak-volume problems efficiently. To get the full benefit of a time-variable pricing policy, an institution should do a staff-scheduling optimization model as well so that tellers are as steadily utilized as possible and so that wait times are reasonable throughout the business week.

Analyzing the possible substitution patterns prior to undertaking the analysis for estimating optimal prices is time well spent; if there are any reasonable substitution patterns for paying check-cashing fee, there is little point in looking at time-variable pricing.

## References

- Details
- Written by Bruce Moore
- Hits: 3558

# Effective Interest (Yield) Loan Discount Points Application

Many types of loans–mortgages in particular–allow a borrower to pay discount points at loan origination as a way to reduce the interest rate of a loan. For financial accounting and reporting purposes these discount points are treated as a prepaid interest asset that is expensed over the life of the loan, but the expense is negative--it is income to the loan. For purposes of setting prices, it is critical that up-front discount points be applied to income in a way that allows managers to compare product yields in a realistic way. The article that follows describes how to do this calculation. The procedure is analogous to the procedure for up-front fee expenses described in Effective Interest (Yield) Loan Fee Amortization, but the sign for the pre-paid interest asset is negative instead of positive; this causes the the asset to be amortized to income rather than expense. This approach it makes it possible to use the same fee amortization code needed for Effective Interest (Yield) Loan Fee Amortization without modification; this makes it much easier to maintain reporting and analysis applications.

The article is divided into the following sections:

- Example Mortgage Loan
- Example Calculation with End-of-period Amortization
- Calculating Discount points Amortization to Income for Prepayments
- Calculation Methods for Loan Portfolios
- Software Implementation

## Example Mortgage Loan

Although this type of up-front discount points can occur on many loan and lease types, the most common are mortgage loans. For our example, a mortgage loan will be used:

Principal | $100,000 |

Interest Rate | 3.5% |

Term | 360 months |

Payment | -$449.04 |

Note that the normal loan payment has a negative sign for cash flow and the discount points payment has a positive sign for the cash flow--in this case the loan payment reduces the principal balance and the amortization payment increases the principal balance. The “discount points” payment can be thought of as a regular prepayment to principal, but since it is contractually used to buy down the interest rate, we need to amortize this principal reduction over the life of the loan. To do that, we will treat the discount points payment as a type of loan and then calculate a payment to get the principal and income that must be amortized:

Discount points paid | -$2,000 |

Discount points Interest Rate | 3.5% |

Term | 360 months |

Discount points amortization payment | $8.98 |

## Calculation for Amortization of Pre-paid Interest to Income

With this basic information, it is now time to calculate the discount points amortization to income for a few periods, as shown in Table 1. The columns in coral show the calculation of the monthly principal portion of the monthly payment, with monthly principal of -$157.38, -$157.84 and -$158.30 respectively.

Similarly, the cyan columns show the calculation of the monthly discount points "principal" amortization to principal, with discount points amortization of $3.15, $3.16 and $3.17 respectively. The discount points are subtracted from the principal balance in period 0, and then the “principal” portion of the “amortization payment” is amortized back into the loan principal as the loan pays down. The signs for discount points amortization are the opposite of the signs for fee amortization; the fee amortization is a positive expense while the discount points amortization is a negative expense– income for all intents and purposes.

The light grey columns show the calculation of the level yield as the interest ($291.67 for period 0) divided by the level yield asset ($98,000) multiplied by 12 periods to annualize the result, which gives 3.57%. Repeating this for the other periods confirms that the yield on the combined asset is the same for each period.

What happens to the $5.83 "pseudo interest" in the amortization calculation. If we divide this by the the level yield asset balance ($98,000) and multiply by 12 to annualize it, we get 0.07%--the difference between the contracted 3.5% interest rate and the effective yield after discount points amortization.

It may seem counter-intuitive to reduce the principal by the amount of the discount points, but it may help to think of it in terms of net cash flows: the bank’s net disbursement is $98,000 but the bank gets paid interest on $100,000, just as in the up-front fee case the bank would have a net disbursement of $102,000 but would get interest on only $100,000.

Monthly Principal and Interest | Level Yield Discount Points Amortization Detailed Calculation | Level Yield | Level Yield Simplified Calculation | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|

Period | Principal | Payment | Interest | Applied Principal | Discount points Balance | Discount points Pseudo Payment | Discount points Pseudo Interest | Applied Discount points Principal Amortization Expense | Level Yield Asset | Yield After Discount points Amortization | Amortization Income Increase to Contract Yield | Simplified Calculation of Amortization Income Increase to Contract Yield | Simplified Calculation of Amortization Expense |

0 | $100,000.00 | -$449.04 | $291.67 | -$157.38 | -$2,000.00 | -$8.98 | $5.83 | -$3.15 | $98,000.00 | 3.57 | 0.07 | 0.07 | -$3.15 |

1 | $98,842 | -$449.04 | $291.21 | -$157.84 | -$1996.85 | -$8.98 | $5.82 | -$3.16 | $97,845.77 | 3.57 | 0.07 | 0.07 | -$3.16 |

2 | $98,684 | -$449.04 | $291.21 | -$158.30 | -$1993.70 | -$8.98 | $5.81 | -$3.17 | $97,691.09 | 3.57 | 0.07 | 0.07 | -$3.17 |

In this amortization case, the discount points asset is subtracted from the loan principal rather than added to the loan principal for purposes of yield calculation, which seems backward.

## Calculating Discount points Amortization to Income for Prepayments

This approach to calculating the discount points amortization works fine until an asset prepays. For full prepayment, this is easy--the entire remaining balance is amortized all at once--but how do you calculate the discount points amortization for a partial prepayment?

To do this, first we should look for an easier way to calculate the monthly discount points amortization amount in a way that doesn't require calculating both the pseudo payment and pseudo interest for the fee. Notice that the discount points pseudo payment is proportional to the fee balance divided by the principal balance--$2,000/$100,000 or 0.02 in this case. Similarly, the applied principal, $157.38 is proportional to the discount points amortization, $3.15.

From this we can calculate the monthly discount points amortization as

To calculate the prepayment of an unusual amount--perhaps a double payment in month 0--we would just take the principal applied, and use the formula above to calculate the discount points amortization:

## Calculation Methods for Loan Portfolios

The example above shows how this would be implemented in practice. For loan pricing optimization, the effective yield is needed for each loan type, term and credit grade--including prepayments. Calculating a pseudo payment for each loan and determining the discount points amortization by month would be programmatically painful and inefficient. Since the principal portion of the payment would be present in most accounting systems at the loan level, this becomes an easy way to retroactively calculate the discount points amortization for effective yield. This is also a calculation that is necessary if the institution decides to convert from one amortization method to a level yield method.

A complete example of this approach in an Excel spreadsheet can be downloaded here. The spreadsheet also contains a tab with an alternate calculation approach that may be appropriate for uses other than pricing use, but it would not reuse the code from fee amortization.

## Software Implementation

Writing a program to calculate the level yield discount points amortization schedule is very simple, but writing the code to extract the necessary information from loan systems--and put the calculated values back into the loan system--can be quite involved. If you can implement this as an extract-calculate-report capability through a data warehouse or data mart, implementing this is fairly straightforward. If you need to put values back into your loan system, you should first work with your loan system vendor to find out if they can implement the capability as a new feature or as an add-on. If that is not possible, plan to spend a significant amount of time with your Information Technology staff working out how the calculated values will be put back into the loan system.

In either case, you will need to work out a way to handle loan modifications. If the modified loan is handled systematically as a new loan, you will need to figure out a way to calculate the remaining discount point balance and transfer that to the modified loan record.

## Notes

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.

- Details
- Written by Bruce Moore
- Hits: 10333

On January 16, 2015, I delivered the training module for the Vice President of Education section of the District 50 Vista Division TLI. The slides for that presentation are in the PDF TLI_VP_Education_2015_01_16.pdf. The slides were prepared using LaTeX and Beamer, two tools that are widely used in graduate programs in Computer Science, Mathematics and related disciplines, but which are rarely used elsewhere. If you are interested in the source for this presentation, send me a email and I will send you the source.

The presentation discusses motivating four different groups of Toastmasters club members--fearful members, self-undervalued members, frustrated members and disconnected members. The presentation covers ideas for motivating each group. It also covers self-serve and push assignment approaches to speech scheduling and how the former simplifies life for the VP Education and how the latter generally gets people to give more speeches.

All versions of the presentation files:

TLI_VP_Education_2015_01_16.pdf

TLI_VP_Education_2015_01_16_4x3_pause.pdf

TLI_VP_Education_2015_01_16_16x9_pause.pdf

TLI_VP_Education_2015_01_16_handout.pdf

- Details
- Written by Bruce Moore
- Hits: 6304