Home » Excel Spreadsheet » 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.

3 thoughts on “Math for amortization rows with CPR, Servicing, CDR, & Loss Severity

  1. cheri says:

    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. Don Pistulka Don Pistulka says:

      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.
      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 *