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:
You don’t need both spreadsheets, Allformula2.xlsx has all the math that Allformula.xlsx has.