# How to calculate scheduled mortgage payment of a mortgage pass-through security?

I am trying to estimate the cash flows of Mortgage Backed Security. The example is present in the Fixed Income textbook written by Fabozzi.

The problem and the solution is as follows:-

Suppose there is a $400 million mortgage pass-through security with a 7.5% pass-through rate, a weighted average coupon of 8.125% and a weighted average maturity of 357 months, how to compute the cash flows for the next two months assuming a 100 Principal Securities Association(PSA)? I have understood the values for all the columns except for column 5. Could anyone how did the value of$2,975,868 come up in the scheduled mortgage payment for month 1?

Even the text book does not provide any references with respect to this.

• Please click on the image to have a large view. – Silent_bliss Sep 9 '20 at 6:56
• Try excel PMT function: PMT(8.125%/12,357, -400m,,0). – Magic is in the chain Sep 9 '20 at 8:00
• @Magicisinthechain it is working in excel. Any idea how to compute it manually? – Silent_bliss Sep 9 '20 at 16:07
• Here goes: $\mathrm{pmt}=\frac{r}{12} \frac{\mathrm{PV}}{1-\left(1+\frac{r}{12}\right)^{-n}}$ – Magic is in the chain Sep 9 '20 at 16:30
• It is minus n: 1−(1+0.08125÷12)^−357=0.91 – Magic is in the chain Sep 9 '20 at 17:38

As per the answer by Magic is in the chain, this is just the calculation for the standard payment on a level-payment MBS pool with monthly amortization. If $$B$$ is the balance, $$WAC$$ is the gross weighted-average coupon (in percent), and $$R$$ is the remaining term (in months), set $$G = WAC/12$$ and $$U = 1/(1+G)$$. The monthly payment is then given by:
$$PMT = \frac{B * G}{1-U^R}$$