Mortgage-Backed Securities by Hand: Pool Cash Flows, Prepayment Speeds, Servicing Strips, Weighted Average Life & Price Scenarios

What you'll build

What you will be able to do

Each lab ends with a concrete result that you build yourself and the checker confirms, cell by cell.

• Treat a fictional pool of 30-year home loans as one big loan: compute its level monthly payment, split the first months into interest and scheduled principal, and measure how much of the pool is left with the pool factor.
• Convert yearly prepayment rates (CPR) into monthly rates (SMM) and back, compute the prepaid principal of a month, and see why the pool's level payment shrinks after prepayments.
• Build a fictional standard prepayment ramp that rises with loan age, scale it to 50% and 200% speed, convert a fast speed into an SMM and read an observed CPR as a speed multiple.
• Strip the servicing and guarantee fees from the gross coupon, compute the investor interest and the total investor cash flow per month, and add up what the fee strip earns over six months.
• Compute the weighted average life of a fictional 15-year pool at 50%, 100% and 200% speed with SUMPRODUCT, measure how much principal comes back in the first three years and chart the three paydown paths.
• Price a fictional pool per 100 of face at three yields and three speeds with NPV, find the yield at which speed does not matter and measure how faster prepayments hurt a premium bond.
• Split the yearly principal of a fictional pool between a first-pay tranche A and a second-pay tranche B, find the year A is paid off and compute the WAL of each tranche.
• Compare five fictional rate scenarios, compute each price change against the base case, measure effective duration and convexity when prepayments respond to rates, and chart the WAL by scenario.