See updated formula at:
I think I owe the readers of my blog a better explanation of what I call the “Mega-Formula“. The formula solves for price given yield, CPR, servicing fees, and the number of days delayed in receiving payments, on a pool of mortgages. I have used the formula in Mortgage Pool Price and Average Life One Cell Formulas to create a price table and an ALM (Asset Liability Management) interest rate shock for a pool of mortgages, without using VBA.
As I mentioned in my original post on the formula, I would give credit to the mathematician that originally wrote the formula, but I don’t remember where I found it. I used it over 30 years ago and stuck it away with some code I wrote back then. I found it again recently, and decided to post it on my blog. I am not a mathematician and this formula may not be Quantum Mechanics, but I was very impressed with all the data it handles, and still fits in one spreadsheet cell. The amortization schedule and the mega-formula are dynamic, so you can play with the numbers.
I placed the input needed for an amortization schedule, alongside the data needed for the mega-formula. The mega-formula data is linked to the yellow input cells of the amortization schedule. The objective was to see if they both calculated the same correct price.
When the “Payment Days Delay” are 30 days (meaning no delay), the amortization present value of cash flows, divided by the starting balance, matched the mega-price formula every time. When other than the standard 30 day payment is used, the present value price of the amortization cash flows is slightly different (one tenth of a cent) from the mega-formula. This has to do with how the first uneven cash flow is handled.
I won’t go into a description of servings or CPR, because I have quite a few posts and spreadsheets that explain both. I do however, want to try and explain how the mega-formula handles the first uneven cash flow and why I used the same logic to adjust the amortization cash flow present value.
First, a look at what a payment delay is. It represents the actual days you expect to receive the mortgage pool payments. If you own a packaged pool made up of hundreds of residential mortgages, all with a 15 day grace period, you don’t expect that all the payments will show up on the first of the month. Also, government agency or private labeled mortgage-backed security payments will flow through to the investor at a specific delay, which could be anywhere from 45 to 75 days after the payment is due. Actually, the delay for 45 days and 75 days, is 44 and 74 days respectively.
The problem with just using dates with the starting date and the first payment date equal to the first uneven days (see sheet 30-360 PV), is that the interest rate, is still stated on a monthly basis. Even when the rate is adjusted from a monthly to an annual rate, the monthly compounding is still implied in the calculation. Therefore a new first payment period must be created that is equal to the total days (in the example 44 days), with no monthly compounding. Below is how it is done, step by step:
With this adjustment, which I think is the proper way to handle the uneven cash flow, both the amortization and mega-formula match prices with every example I tried.