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.

One thought on “Math for amortization rows with CPR, Servicing, CDR, & Loss Severity

  1. Pingback: Loan Pool Named Formulas, Without An Amortization Schedule – Excel@CFO

Leave a Reply

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

WordPress spam blocked by CleanTalk.