The traditional method for calculating the price on mortgage backed securities (MBS) is to calculate the present value of each cash flow received from the security by one discount rate (market yield). Another method would be to use more than one discount rate, the Treasury spot rate curve, plus an add-on spread referred to as Z-spread. Download the attached spreadsheet (Z),
Start 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:
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.00%:
In order to calculate the Z-Spread, enter the price in the yellow cell below and click the button:
This indicates that both the price using the single discount rate of 3.00% and the price using the Z-Spread of .599% will be the same ($103.48275).
You can also calculate price using the Z-Spread, by entering the Z-spread in the yellow cell and clicking the green button: