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

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

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

Macaulay Duration of an Amortizing Loan

The term “duration” is often used interchangeably by market participants to denote either a volatility measurement, a weighted term to maturity, or a portfolio risk measurement. In practice there is more than one definition of duration. The following describes the most basic duration along with it’s calculations. In a future posts we will discuss Modified Duration, Effective Duration, […]

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


There is nothing special about this annuity calculator except: All input is with sliders Each line of the results are one cell text and formulas The dollar growth and payout balances are charted You can pick from monthly deposits or lump sum deposit Inputs: Outputs:   Download workbook “Annuity” from:  Downloads Written in Excel […]