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

Macaulay Duration Plus Balloon Payment

I was asked by a reader to add an option to the workbook “MacaulayDuration”. The option is to include a balloon payment. I added the option in a separate workbook called “MacaulayDuration_with_Balloon”. Read my post on “Macaulay Duration of an Amortizing Loan” for further information. Download “MacaulyDuration_with_Balloon” from: Downloads Written in Excel 2013

APR – Adjustable Rate Mortgage (ARM)

Like the previous post this worksheet calculates the APR, but for an adjustable rate mortgage or ARM. The difference between the fixed rate and the ARM is that the ARM cash flow is based upon reaching the fully-indexed rate, given the information available when the loan was made, and assumes it stays at the fully-indexed rate for the remaining term of the […]

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

Modified & Effective Duration, Plus Convexity of a Loan

The spreadsheet has been changed for this post. Please go to: Effective Duration, Convexity, and Convexity Adjustment For Loans My last post showed the formula to calculate a Macaulay Duration on an amortizing loan. The definition of a Macaulay Duration I used was “an effort to more accurately measure the term of an option-free fixed […]

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

TDR – Decision Tree for Notice of Defaults

After the housing bubble crash, defaults on mortgage loans became a big problem for financial institutions. A large number of homeowners either could not or would not continue to make payments on homes that were “underwater” (home value less than the amount owed on mortgages and other liens). After 90 days of no payments, a […]