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

Over Six Decades of Yield Curve Animation

  Important An improved version of this spreadsheet can be found on post:   This spreadsheet is mostly for fun, but it does show the volatility of the Treasury yield curve. I took the yields from Federal Reserves  H.15 Selected Interest Rates, downloaded using FRED. I used the 3M, 6M, 1Y, 2Y, 3Y, 5Y, 7Y, 10Y, 20Y, and […]

The Math Behind Excel’s CUMIPMT() & CUMPRINC() Functions

I published the math behind the CUMIPMT() & CUMPRINC() functions at the bottom of my spreadsheet for the post Annuity – Excel Functions and Math. I think it got lost in all the annuity math. When I Google the math for these two functions, my blog doesn’t show up until the 6th or 7th page, depending […]

Math for Excel Functions With Starting and Ending Values

At the beginning of this month I posted a spreadsheet that gave the math formulas for Excel functions that have even periodic cash flows called Annuity – Excel Functions and Math. This is a reminder that if you need the math equivalent formulas for Excel functions with only two cash flows (starting and ending values), […]

Breaking Down the Mortgage Mega-Formula

Additional update to formula: MBS Math Formula. Servicing, CPR, Payment Delay, Default Rate & Loss Severity This post will probably not interest the average reader, but I have been so fascinated by this 40 some year old formula that I stuck away and have recently rediscovered. The formula fits in one Excel cell, calculates the […]

Annuity – Excel Functions and Math

In August of 2014 I wrote a post called “Even Cash Flow Calculator” and published a spreadsheet along with it called “EvenMultCashFlows“. Read “Even Cash Flow Calculator” for more about the spreadsheet. I made some improvements to the spreadsheet, including the math equivalent for each Excel function I used, except IRR() which requires an iteration. The math […]

Spreads Between the 10-Year Treasury and 30-Year Mortgage

The relationship between 10-year U.S. Treasury Notes and 30-year mortgage rates has been used for years by investors. One of the obvious problems with using this relationship is that we are comparing an option-free Treasury note with an amortizing loan that is callable at any time. The average life of a 30-year mortgage with a 4.0% interest rate […]

Mortgage Pool Price Given Yield, Amortization vs. Mega-Formula

See updated formula at: MBS Math Formula. Servicing, CPR, Payment Delay, Default Rate & Loss Severity I think I owe the readers of my blog a better explanation of what I call the “Mega-Formula“. The formula solves for price given yield, CPR, servicing fees, and the number of days delayed in receiving payments, on a […]

Cherry-Picking a Loan Portfolio

I have an updated version of this post. Click here Have you ever wanted to cherry-pick a portfolio of fixed rate mortgages or throw out the mortgages you don’t want from a pool? That is what this spreadsheet called  “Cherry“ is for. A picture of the data needed for the calculations is shown below: Here is […]