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:

Download “TotalMega“

DavidThe 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

Don PistulkaPost authorDavid,

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

LesDon, I sent you a file illustrating my point.

Thanks

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

Pingback: Mortgage Pool Price and Average Life One Cell Formulas – Excel@CFO

Don PistulkaPost authorLes

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

LesThanks 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,

Don PistulkaPost authorHi 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

LesThanks, 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.

Don PistulkaPost authorLes,

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

Don

Pingback: Mortgage Pool Price Given Yield, Amortization vs. Mega-Formula – Excel@CFO

Pingback: Breaking Down the Mortgage Mega-Formula – Excel@CFO

Pingback: Conforming the “Mega” MBS Formula to Street Conventions – Excel@CFO

Pingback: Mortgage Backed Securities (MBS) “MegaFormula” – Excel@CFO

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

Don PistulkaPost authorTristan,

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

kumar pimpaleHi 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?

Don PistulkaPost authorKumar,

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.