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

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

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

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