Mortgage Loan Pool Pricing Table with CPR, CDR, & Loss Severity

Before I added default and loss severity to the mega formula for the price of a mortgage pool, I produced a price/yield table. That old post was http://pistulka.com/Other/?p=707 This post and Excel workbook includes the added variables. As is true with all of my spreadsheets, only the yellow cells are input cells:

 

 * Servicing – If servicing is retained by the seller, enter the servicing fee.
If the buyer intends to service the loans, enter zero.

* Market Yield – The discount rate used to calculate the net present value (price) of the cash flows produced by the pool of loans, based on the various assumptions.

*Days Delay (default 30 days) – Mortgage loans pay interest in arrears, meaning that the payments are typically made at the end of 30 days. Servicers will routinely pass through the payments to the mortage holder after the actual payment date, commonly due to a grace period. Depending on the sale agreement, the servicer will collect the payements and pass them on to the mortgage holder on a specific date. For example, the agreement might state that the cash payments will be sent to the buyer on the 15th of of the next month.
That delay in payments will reduce the return to the loan holder, by delaying the reinvestment of the payments. To compensate for this loss, the price is reduced the approperiate amount, depending on the discount rate (market yield). The Days Delay input would then be entered, using the example above of the 15th day of the next month, by entering 44. That is the normal 30 days plus 14 additial days (15 -1). Some MBS producers, for example, might have payment delays for as long as 75 days.

The average life is important, because although prices will vary with the same market yield, the average time you will get that yield could make a big difference. For example, take the top row of the table with an 8.00% market yield. The price ranges between 92.842 and 84.317 for the same 8.00% yield. The average life at 92.842 (using the assumptions provided) is 3.88 years and 6.72 years for a price of 84.317, using another set of assumptions.

Download: http://pistulka.com/Excel_Shared/PricingTable.xlsx

Leave a Reply

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

WordPress spam blocked by CleanTalk.