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


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

Simple Formula for Converting Compound Interest Rates

 This workbook has a simple formula to convert compound interest rates. =IF(t>500,f*LN(1+g/f),IF(f>=500,t*(EXP(g/t)-1),(t*((1+g/f)^(f/t))-t)))  Where: g = Current interest rate f = Number of times the current rate compounds per year t = Number of times the converted new rate compounds per year This lookup table grabs two of the numbers for the formula Continuous compounding in Excel […]

Constant Default Rate (CDR)

Also see this post: Comparing CDR Default Formulas to Industry Standard Default Formulas   Constant Default Rate (CDR) is an annualized rate of default on a pool of loans. The default rate on loans depends on a number of conditions, such as the age of the loans, seasonality, burnout levels, FICO, LTV, income, etc. Since […]

Solve for Last Payment Due

In my first blog post back in August, I had a formula that would solve for future value given dates and amounts. In this post we will use the same formula to solve for the last payment due. There are a number of scenarios where the last payment might be needed. Assume for a moment that you won a court judgment […]

Reverse Engineering Constant Prepayment Rate (CPR)

This post has been updated in a new post called Calculating Historic CPR. The spreadsheets has also been updated “CalCPR.xlsm” After you review this post, please see the updated post. Unless you work for a firm that packages pools of mortgages, you will probably never need to calculate the historic CPR on an existing pool. Even so, […]

Amortization Schedule With Variable Rates

Note: I have updated this post with more options. See Variable Rate Amortization – Day/Year Count & Last Payment Options. Have you ever wanted an amortization schedule where you can set the rate for one term and then change the rate for another term, and change the rate and term a total of six times? […]

Mortgage Backed Securities (MBS) “MegaFormula”

See updated formula at: MBS Math Formula. Servicing, CPR, Payment Delay, Default Rate & Loss Severity This post is just to show you 20-somethings what we had to go through to calculate the price of a mortgage backed security back in the day. I had the first desktop computer (before Apple and Radio Shack were […]

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

APR – Fixed Rate Mortgage

Given the inputs in yellow cells, check boxes and option buttons, this sheet uses an amortization schedule to calculate APR (or APY) for a fixed rate mortgage. Both Loan Balance and Interest Rate need no explanations. Truth in Lending Fees are additional dollar costs to the borrower to acquire the loan. Points represent a percent of […]