This spreadsheet was originally compiled to help make the decision to either sell mortgages that were originated, or keep them. It can also be used to help with the decision to purchase a pool of whole loans, or a securitized mortgage pools. Two different concepts of return are referred to in this post. The two are […]
Category: Loans
Prepayment Penalty
Once upon a time (all my numbers and loans are fictitious) the loan department was negotiating the sale of a parking lot. It seemed that the buyer and we were at a stalemate. We wanted $5,700,000 for the lot and the buyer was only willing to pay $5,500,000. The market rate for this type of loan was […]
Defeasance
Important This post and spreadsheet has been updated. Please click this link to Defeasance 4.0 If you are involved in commercial lending or perhaps municipal bonds you are probably familiar with the term defeasance. Commercial loans normally have hefty prepayment penalties, while some loan documents prohibit prepayment altogether. Commercial loan defeasance is a collateral substitution […]
Skip-a-Payment
Around this time of year, banks and credit unions will sometimes offer borrowers the option to skip a payment or two in return for a fee. This offering is a spreadsheet that calculates the APR on a loan after the borrower accepts the offer to skip one or more payments. There are two amortization schedules […]
Constant Default Rate (CDR)
Also see this post: Comparing CDR Default Formulas to Industry Standard Default Formulas Constant Default Rate (CDR) is an annualized rate of default on a pool of loans. The default rate on loans depends on a number of conditions, such as the age of the loans, seasonality, burnout levels, FICO, LTV, income, etc. Since […]
Convert CPR, SMM & ABS (APS)
We have already discussed Constant Prepayment Rate (CPR) and Single Monthly Mortality Rate (SMM). Another prepayment rate is the Absolute Prepayment Speed (APS), used mostly for securities backed by auto, truck, and RV loans. The acronym for Absolute Prepayment Speed (APS) is also referred to as ABS, which is confusing because ABS is also the acronym for Asset-Backed […]
Solve for Last Payment Due
In my first blog post back in August, I had a formula that would solve for future value given dates and amounts. In this post we will use the same formula to solve for the last payment due. There are a number of scenarios where the last payment might be needed. Assume for a moment that you won a court judgment […]
Reverse Engineering Constant Prepayment Rate (CPR)
This post has been updated in a new post called Calculating Historic CPR. The spreadsheets has also been updated “CalCPR.xlsm” After you review this post, please see the updated post. Unless you work for a firm that packages pools of mortgages, you will probably never need to calculate the historic CPR on an existing pool. Even so, […]
Mortgage Pool Price and Average Life One Cell Formulas
See updated formula at: MBS Math Formula. Servicing, CPR, Payment Delay, Default Rate & Loss Severity http://pistulka.com/Other/?p=2384 A few posts back I showed the “megaformula” I used back in the day for calculating the price of a mortgage pool with prepayments (CPR). Rather than treating the one cell formula as just an interesting antique, I […]
Amortization Schedule With Variable Rates
Note: I have updated this post with more options. See Variable Rate Amortization – Day/Year Count & Last Payment Options. Have you ever wanted an amortization schedule where you can set the rate for one term and then change the rate for another term, and change the rate and term a total of six times? […]
Mortgage Backed Securities (MBS) “MegaFormula”
See updated formula at: MBS Math Formula. Servicing, CPR, Payment Delay, Default Rate & Loss Severity http://pistulka.com/Other/?p=2384 This post is just to show you 20-somethings what we had to go through to calculate the price of a mortgage backed security back in the day. I had the first desktop computer (before Apple and Radio Shack were […]
PSA vs. CPR
In past posts I showed you how to calculate a Constant Prepayment Rate (CPR) with an amortization schedule. This post will allow you to compare a CPR rate to a model provided by the Public Securities Association (PSA). PSA is based upon the CPR calculation. Both use a Single Monthly Mortality (SMM) calculation. The difference is […]
TDR – Decision Tree for Notice of Defaults
After the housing bubble crash, defaults on mortgage loans became a big problem for financial institutions. A large number of homeowners either could not or would not continue to make payments on homes that were “underwater” (home value less than the amount owed on mortgages and other liens). After 90 days of no payments, a […]