Effective Duration, Convexity, and Convexity Adjustment For MBS

This is an update of a post and spreadsheet I wrote in Oct. of 2014 called: “Modified & Effective Duration, Plus Convexity of a Loan”.  The previous post was hard-wired for a plus or minus 100 basis point change in yields. These new formulas and inputs allow for changes in yield other than 100 basis […]

Simplified Linear Interpolation of Treasury Rates

In August of 2014 I showed a spreadsheet for interpolating a yield, given a range of dollar prices. That post was call Interpolation. I thought of another way to use the same linear interpolation. Lets say you want to know what the Treasury yield would be for an investment. Unfortunately, you only have the current Treasury […]

Math for amortization rows with CPR, Servicing, CDR, & Loss Severity

First, let me say that this is my 100th post and spreadsheet. I started in August of last year. If this spreadsheet looks a lot like my last post, it is very close. My last spreadsheet showed how to calculate any row of an amortization schedule, including starting balance, scheduled  payments, interest, principal, servicing, prepayment (CPR) and […]

Math for amortization rows with CPR and Servicing

  After you read this post, check out my updated post   In my post called The Math Behind Excel’s CUMIPMT() & CUMPRINC() Functions I showed how to calculate a single row of a simple amortization schedule, using math instead of the amortization schedule. In this post and spreadsheet I show how to calculate the starting balance, payment, interest, […]

Calculating Historical CPR

There is a newer post that replaces the amortization schedule to calculate historic PSA with a UDF: http://pistulka.com/Other/?p=2964   Back in December of 2014 I wrote a post and Excel spreadsheet called Reverse Engineering Constant Prepayment Rate (CPR). I called it that because instead of giving a CPR to a model, we could do the reverse and calculate the […]

Certificate of Deposit Ladder Optimizer

There are all kinds of calculators you can find to “optimize” your rate of return by laddering certificates. Of course the term optimize is a misnomer because you don’t know what the reinvestment rate will be on the shorter term CDs.  There are some advantages to laddering such as, if you need the funds earlier than expected, you can […]

Conforming the “Mega” MBS Formula to Street Conventions

See updated formula at: MBS Math Formula. Servicing, CPR, Payment Delay, Default Rate & Loss Severity http://pistulka.com/Other/?p=2384 This adjustment to the mega formula for mortgage-backed securities only affects those that use an odd first payment, by entering an amount other than 30, in the “Payment Days Delay” cell. It was pointed out by Win Smith (The Well-Tempered Spreadsheet) […]

U.S. Treasury Forward Rate Curves

Note: A revised spreadsheet has been publish for this topic. Please see the new revised version here: http://pistulka.com/Other/?p=3459 Back in September of last year I wrote three posts and spreadsheets about Gap Analysis, Spot Rates, and Forward Rates. I put all three together in this post and Excel workbook. I have connected the Treasury’s daily Treasury yield curve […]

Bond Immunization

Bond immunization is closely related to defeasance which I discussed in posts  Defeasance2.0 and Defeasance. The similarity between immunization and defeasance is that you are trying to hedge the interest rate risk involved with a given liability cash flow. The example on the spreadsheet “Immunize” may be over simplified, but I think it describes the concept sufficiently. Normally […]

Default Rates Affect on Servicing

A reader (Justin) asked about how the CRD (Constant Default Rate) affects MSR (Mortgage Servicing Rights). I have taken a spreadsheet previously published on CDR, and added the value of servicing. The new spreadsheet is called CDR_Servicing. The CDR includes an additional CPR, which will reduce the life of the MBS and reduce the value of servicing. However,  […]