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 […]
Month: November 2014
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? […]
Tax Calculators
First, I want to point out the prominent disclaimer on the first sheet of the “Tax” workbook. I am not a CPA or a tax attorney. I was a CFO of a Credit Union that was a “not for profit” and therefore exempt from federal income tax. One of the reasons I have collected the tax calculators in […]
Mortgage Backed Securities (MBS) “MegaFormula”
See updated formula at: MBS Math Formula. Servicing, CPR, Payment Delay, Default Rate & Loss Severity http://pistulka.com/Other/?p=2384 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 […]
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 […]
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: 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 […]