On August 26, 2014 I showed an amortization schedule that could be used for initially valuing servicing. I introduced the use of constant prepayment rates (CPR) in order to get a more realistic pricing than assuming the pool of mortgages or commercial loans would never prepay. Considering the ease of refinancing and the mobility of the labor market, there is a very small chance that a 30-year mortgage borrower will live in the same house for 30 years, pay only the payment due, and never refinance. That is where a CPR assumption comes in.

As I touched upon in my last post on servicing, if you are a financial institution that underwrites and packages mortgages to sell with servicing retained, your auditors might require you to take the present value of the servicing into income upon sale of the mortgages. This matrix was actually developed to give the commercial lending department an idea of how much income they would be producing each month from additional servicing. First, we will start with the inputs:

- The principal value of the pool of loans or mortgages is a given.
- The market rate used to discount the present value of the stream of servicing income however, can be a little challenging. Most auditors would agree that according to FASB rules, the discount rate should represent the observable market rate for like servicing. This might mean relying on expert opinions from firms that either make markets in servicing rights, or firms that have the ability to produce the implied market rate through modeling, if no observable market rate exists.
- In most cases, you will probably never use the next three inputs:
- “Lockout” is asking for the first “X” number of months, that no prepayments are allowed, most likely due to a prepayment penalty. Prepayment penalties are not as prevalent as they were a few years ago, but if you are offering loans with, for example, a 5-year prepayment penalty, you would enter 60 months in the yellow cell, depending on the type of prepayment penalty. Some prepayment clauses waive the penalties for sales and some do not. If no lockout, leave a zero.
- “Interest Only” – It may be an interest only commercial loan. Remember that if you entered any CPR other than zero, the principal prepayment will still be assumed. The stream of payments with a CPR assumes a pool of like loans that would still have prepayments. One other thing to remember: a 5-year interest only loan is actually interest only for 59 months. Enter the interest only to maturity, minus the last month. It doesn’t have to be interest only to maturity. It could be, for example, the first 12 months. If no interest only, leave a zero.
- “Zero Payment” This is for delayed payments. An example might be an agreement with a builder to delay payments for a year on loans offered by your institution to sell their homes. The function assumes negative amortization. If no delayed payments, enter a zero.

I conditionally formatted the matrix to change colors as the value went above the next dollar level:

The matrix is adjustable both horizontally and vertical by changing the yellow cells. The horizontal “Service Fee” is the interest rate retained for servicing the loan . On the vertical “Maturity (Balloon) Years” is the number of years or term of the loan.

Consider the present value of servicing a 5-year commercial loan, amortized over 30 years, with a servicing fee of .35%, you will see the service value of $1.1444. This is the value for $100 in par value. If you click on the matrix cell with the $1.1444, you will see the actual dollar value in cell K5 ($17,919.90), based on the principal value entered in cell D5.

I have frozen the sheet into two panels, so that each time you click a value in the matrix, you can still see the dollar value in cell D5.

If you are looking at the UDF and wondering why the last three inputs are divided by 12, it is because the UDF actually wants the years, not months.

Download workbook “ServiceMatrix” from:

http://www.pistulka.com/Excel_Shared/

Downloads Written in Excel 2013