Mortgage Pool – Holding Period Return

This spreadsheet was originally compiled to help make the decision to either sell mortgages that were originated, or keep them. It can also be used to help with the decision to purchase a pool of whole loans, or a securitized mortgage pools. Two different concepts of return are referred to in this post. The two are […]

Bonus Checking

To understand this next spreadsheet, you have to take your accounting hat off and put your financing hat on. Consider all your sources of funding; savings accounts, money market accounts, CDs, borrowing, and checking accounts. There may be more, but considering the above, checking accounts will always be the cheapest source of funds. The reason […]

Valuing Servicing Matrix

On August 26, 2014 I showed  an amortization schedule that could be used for initially valuing servicing. I introduced the use of constant prepayment rates (CPR) in order to get a more realistic pricing than assuming the pool of mortgages or commercial loans would never prepay. Considering the ease of refinancing and the mobility of the labor […]

Sum Amounts Sold During Month & Year

You have a table of products, sales dates, and amounts: Objective: Sum all sales amounts , from the same product, during a certain month and year. Input in yellows cells only, any date within the month and year to be summed and a product code: The answer has two formulas that are the same formula except one […]

Forward Rates Part 3: Spot Rates

A newer version with four bootstrap methods can be found at: A Forth Way To Bootstrap Spot Rates This method of calculating spot rates is referred to as the bootstrapping method. Each spot rate (or zero coupon) along the Treasury yield curve needs the previous spot rates, in order to discount the current securities coupon payments. […]