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 […]

Effective Duration – No VBA or Amortization

Back in October I showed how to calculate effective duration and convexity on a pool of fixed rate mortgages with the help of amortization schedules. In November I posted a formula that could calculate the price of a fixed rate pool of mortgages, including the use of CPR and servicing, in one cell. That same month I […]

California Home Prices Dashboard

The California Association of Realtors® (CAR) publishes monthly home price data for each county in California. I produced a dashboard using their data and the 2010 census for income and population data. In order to keep the dashboard up to date, the “All Counties” sheet stores the data from CAR. There is a link to […]

Purchasing Power – FRED Add-In

If you are interested in the economy, interest rates, stock prices, etc., you will want to get the Federal Reserve Bank of St. Louis Economic Data (FRED) Add-In. The FRED add-in provides free access to over 240,000 data series from various sources (e.g., BEA, BLS, Census, and OECD) directly through Microsoft Excel. I created a spreadsheet […]


Important This post and spreadsheet has been updated. Please click this link to Defeasance 4.0 If you are involved in commercial lending or perhaps municipal bonds you are probably familiar with the term defeasance. Commercial loans normally have hefty prepayment penalties, while some loan documents prohibit prepayment altogether. Commercial loan defeasance is a collateral substitution […]

Asset Growth vs. ROA – Net Worth Ratio

This concept came from a calculator that was readily available on the internet years ago. I would give credit to the original author, but I don’t remember the name. It was made available online by either CUNA or one of the other credit union associations. I have only embellished it with the addition of charts and a […]