Math for amortization rows with CPR, Servicing, CDR, & Loss Severity

First, let me say that this is my 100th post and spreadsheet. I started in August of last year. If this spreadsheet looks a lot like my last post, it is very close. My last spreadsheet showed how to calculate any row of an amortization schedule, including starting balance, scheduled  payments, interest, principal, servicing, prepayment (CPR) and ending balance.

What I left out was the math to calculate the row of an amortization schedule with all of the above, but including CDR (defaults), and the loss severity (the loss on the defaults). The old spreadsheet was called AllFormula.xlsx and the new spreadsheet is called Allformula2.xlsx.

The inputs are the same, except that you need two more pieces of information, CDR and Loss Severity:

allf2_2You don’t need both spreadsheets, Allformula2.xlsx has all the math that Allformula.xlsx has.

Don Pistulka
Don Pistulka

Retired Credit Union CFO - Finance
Background: over 40 years in investments, asset/Liability management, banking, securities trader.
Worked for: California Credit Union, WesCorp, CalFed S&L, Crocker Bank, Carroll McEntee, Federal Home Loan Bank Board (D.C.), Western Asset Management, Security Pacific National Bank.

5 Comments

  1. Can you also create something which also factors Delinquency and Servicer Advancing for calculation of the Amortization schedule.

    1. Hi Vaibhav,
      If I understand your question, most of my amortization schedules can be from the standpoint of the owner of an MBS, so the guarantor will advance both delinquencies and servicing. I you are using a schedule that has CRD and Loss Severity, just ignore the CDR and Loss Severity by entering a zero for both.
      I will send you an example with zeros for CDR and Loss Severity.

      Don

  2. HI I have a question for this model. My question is about the defaults. I see your principal payment = amortization (total payment calculated from beginning balance of the period)-interest (interest *beginning balance)-servicing fee. However, my question is if the loan is default in this period, would the principal received should exclude the principal generated in the default loans ? since default by definition is not paying principal and interest.

    1. Cheri,
      The same basic question came in within half and hour, but responding to a different post. I assume they are both from the same person.
      The answer lies in the sequence of principal reduction. Notice that prepayment reduces principal, after the payment principal is deducted from the starting principal balance. Likewise, principal defaults are reduced after the payment and the prepayment.
      Therefore, normal P&I came in before any prepayments, and defaults take place. The prepayments and defaults are used establish the starting balance of the next payment. I hope that helps.
      Don
      P.S. Also, remember that these amortization schedules are from the standpoint of the investor. Even though defaults may be indicated in a given month, the investor is advanced the total P&I from the servicer. From the investor standpoint , defaults can be thought of as just a prepayment, due to the guaranteed P&I.

Leave a Reply

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