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

## Convert CPR, SMM & ABS (APS)

We have already discussed Constant Prepayment Rate (CPR) and Single Monthly Mortality Rate (SMM). Another prepayment rate is the Absolute Prepayment Speed (APS), used mostly for  securities backed by auto, truck, and RV loans. The acronym for Absolute Prepayment Speed (APS) is also referred to as ABS, which is confusing because ABS is also the acronym for Asset-Backed […]

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

## Mortgage Pool Price and Average Life One Cell Formulas

See updated formula at: MBS Math Formula. Servicing, CPR, Payment Delay, Default Rate & Loss Severity http://pistulka.com/Other/?p=2384 A few posts back I showed the “megaformula” I used back in the day for calculating the price of a mortgage pool with prepayments (CPR). Rather than treating the one cell formula as just an interesting antique, I […]

## Currency Risk

After the bubble burst in home prices (along with the rest of the U.S. economy), I was wondering what happened to the European investors that were buying up million dollar condos in Florida at the highs. They had two major risks if they were not hedged: housing prices and currency fluctuations. I used the same […]

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

## PSA vs. CPR

The workbook for this post has been replaced with a workbook with two separate sheets.   In past posts I showed you how to calculate a Constant Prepayment Rate (CPR) with an amortization schedule. This post will allow you to compare a CPR rate to a model provided by the Public Securities Association (PSA). PSA […]

## 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: http://www.pistulka.com/Excel_Shared/ Downloads Written in Excel 2013

## Dutch Auction

The U.S. Treasury (and other countries) uses a Dutch auction to sell securities. This workbook contains two examples of how a Dutch auction might work. One assumes the bids are on a yield basis or discount rate basis, like Treasury bills. The other sheet is the same except it assumes bids are made on a price […]

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

## Time Value Of Money

Back when financial institutions wanted your money and actually competed for your deposits, there were all kinds of gimmicks to entice you to deposit your money with them. One such gimmick was a credit union that was offering to pay the interest upfront on their certificates of deposit (CDs). If you are reading this blog, I probably don’t have […]

## Two Mortgage Payoff Tables

Assuming you don’t have a balloon payment on your fixed rate 30-year mortgage, these two tables may be helpful. Table1 will tell you when your mortgage will be paid off, given making extra payments each month. Table2 is the reverse of Table1, in that you give it the number of years you want to have […]

## Credit Card Payoff

When you get your credit card bill you might see a box that tells you how long it will take to pay off the balance if you make the minimum payment. This spreadsheet shows the payoff years, give a minimum payment of the greater of: A percent of the balance A dollar amount The percent […]