Home » 2015 » January

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...
Continue reading »

Build your own CPR model

Last November I posted an amortization spreadsheet that allowed for variable interest rates on mortgages. I used that spreadsheet and added a CPR variable. That way you can create your own CPR model, rather than using one CPR for the full term or using the PSA model....
Continue reading »

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...
Continue reading »

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...
Continue reading »

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,...
Continue reading »

Prepayment Penalty

Once upon a time (all my numbers and loans are fictitious) the loan department was negotiating the sale of a parking lot. It seemed that the buyer and we were at a stalemate. We wanted $5,700,000 for the lot and the buyer was only willing to pay...
Continue reading »

Defeasance

A better post and spreadsheet on defeasance can be found here: Defeasance 2.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...
Continue reading »

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...
Continue reading »