Last November I posted an amortization spreadsheet that allowed for variable interest rates on mortgages. I used that spreadsheet and added a CPR variable. That way you can create your own CPR model, rather than using one CPR for the full term or using the PSA model. The default numbers above show the user wants interest rates […]
Category: Uncategorized
Effective Duration – No VBA or Amortization
Back in October I showed how to calculate effective duration and convexity on a pool of fixed rate mortgages with the help of amortization schedules. In November I posted a formula that could calculate the price of a fixed rate pool of mortgages, including the use of CPR and servicing, in one cell. That same month I […]
California Home Prices Dashboard
The California Association of Realtors® (CAR) publishes monthly home price data for each county in California. I produced a dashboard using their data and the 2010 census for income and population data. In order to keep the dashboard up to date, the “All Counties” sheet stores the data from CAR. There is a link to […]
Purchasing Power – FRED Add-In
If you are interested in the economy, interest rates, stock prices, etc., you will want to get the Federal Reserve Bank of St. Louis Economic Data (FRED) Add-In. The FRED add-in provides free access to over 240,000 data series from various sources (e.g., BEA, BLS, Census, and OECD) directly through Microsoft Excel. I created a spreadsheet […]
Prepayment Penalty
Once upon a time (all my numbers and loans are fictitious) the loan department was negotiating the sale of a parking lot. It seemed that the buyer and we were at a stalemate. We wanted $5,700,000 for the lot and the buyer was only willing to pay $5,500,000. The market rate for this type of loan was […]
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 […]
Why you can’t trust median home prices
This spreadsheet may come in handy if you ever have to explain to someone why you can’t trust home prices published in the media. Typically, home prices are based upon the median sales price. In my area (Sothern California) it is not uncommon to see home prices down 20% or more (YoY) in one city, […]
Skip-a-Payment
Around this time of year, banks and credit unions will sometimes offer borrowers the option to skip a payment or two in return for a fee. This offering is a spreadsheet that calculates the APR on a loan after the borrower accepts the offer to skip one or more payments. There are two amortization schedules […]
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 […]
Standard Default Assumption (SDA)
My last post and spreadsheet concerned the Constant Default Rate (CDR) on a pool of loans. As the name implies, the CDR uses the same default rate for the complete term of the loan. It does not allow for the probability of default, depending upon the age of the loan. The SDA curve was developed […]
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 […]