Home » 2014 » November

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...
Continue reading »

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...
Continue reading »

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...
Continue reading »

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...
Continue reading »

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...
Continue reading »

PSA vs. CPR

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 is based upon the CPR calculation. Both...
Continue reading »

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...
Continue reading »

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...
Continue reading »

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...
Continue reading »

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...
Continue reading »