# MBS Math Formula. Servicing, CPR, Payment Delay, Default Rate & Loss Severity

I have made adjustments to the MBS mega formula that calculates the price of a mortgage-backed security. It now allows for default rates (CDR) and loss severity.

In the past I have made other adjustments and I have included a review of these adjustments in the spreadsheet linked below.  I’m not sure who will use this new formula, since I have published a number of spreadsheets that allow for Default Rate (CDR) and Loss Severity when calculating the price of Mortgage-Backed Securities. They include both VBA functions and amortization schedules.

A math formula allows for the calculation of an MBS price in one Excel cell, however the formula is much larger than the original mega formula:

Given:

1. David says:

The Financial Standards Accounting Board is requiring major changes to lease accounting on the part of the lessee in late 2018 and 2019. The changes are reflected in the FASB ASU #842. Leases under 12 months are not subject to the new accounting rules. The vast majority leases are the operating type.

Would you please design an Excel spreadsheet to record the amortization of a group of leases under the new FASB accounting standard?

Thank you,

David

1. Don Pistulka says:

David,
I am not a CPA, nor am I familiar with lessee accounting. If you would send me an example from your outside CPA firm on how the new accounting would look, possibly I could put something together for you.

Send to blog@pistulka.com
Don

2. Les says:

Don, I sent you a file illustrating my point.

Thanks

3. Les says:

Don, I’ve noticed that none of your MBS valuation models consider the GFee charged by the agency. Shouldn’t that be considered?

1. Don Pistulka says:

Les
The guarantee fee is part of the servicing fee. For example, the actual servicing might be 25 basis points and the guarantee fee another 25 basis points. You would enter under servicing fee 50 basis points.

Don

1. Les says:

Thanks Don, I see that the math works out when the Gfee is added to the servicing. I’m not sure if there might be some slight difference in the timing when the agency funds the pool for defaults, but its probably not intended to be that exact,

1. Don Pistulka says:

Hi Les,
From the standpoint of the purchaser, the guarantee fee and the servicing fee both reduce the gross coupon to the net coupon.
For the difference in default timing, see my latest post:
Mortgage Loan Pool – Default Recovery

Don

1. Les says:

Thanks, I was referring to the agency (Fannie Mae, Freddie Mac) funding the security pool for defaults, which is different than the default recovery that you’ve modeled. There is no un-recovered principal (since the agency takes care of that), just a delay in receiving the payment for the default.

2. Don Pistulka says:

Les,
Send my an example of a “security pool for defaults” and I will try and include the pool inside an amortization schedule.

Don

4. tristan hunt says:

Don – do you have any models to actually calculate loss severity itself?

1. Don Pistulka says:

Tristan,
I don’t have any models for estimating loss severity. At the credit union, the head of mortgage lending used an average of 10%. That was based on California laws and his experience. That would include fixing up a repossessed home, realtor sales fees, etc. It would also depend on expectation about the housing market. An experienced mortgage lender would have a good idea of what the average loss severity would be for his/her market.

Don

5. Hi Don,

I am a newbie in a corporate Job and am learning the ropes in mortgages. I want to calculate IRR for a pool of loans, would you able to help me?

1. Don Pistulka says:

Kumar,
Can you tell me how many loans are in the pool and are the interest rates and maturities close to the same? Maybe you could send me a sample of the loans in the pool. It would be easier for me to give you the best method if I can see the loans.

WordPress spam blocked by CleanTalk.