The traditional method for calculating the price on MBS is to calculate the present value of each cash flow received from the security by one discount rate. Another method would be to use more than one discount rate, the Treasury spot rate curve, sometimes referred to as Z-spread calculation or Z curve. The attached spreadsheet (Z_Curve_Price), starts off by downloading the Treasury yield curve from the Department of the Treasury’s Resource Center.
Then, using the bootstrapping method, calculates the spot rates:
For more on the bootstrap method for calculating spot rates, see my post “A Fourth Way To Bootstrap Spot Rates.
The inputs for the Treasury yield curve looks like this:
The calculations run down for 360 months, but the first 12 months look like this:
A chart showing the Treasury Curve and the Treasury Spot Curve is included:
On the Amortization sheet, you have the traditional pricing method, which those that follow my posts will recognize. The input data looks like this:
This is followed by the traditional pricing method, based on one discount rate of 3.75%:
For the Yield spread to the Treasury spot rates, you need to enter the spread in the yellow cell below (default 1.000%):