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

# Category: Excel Spreadsheet

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

## Credit Union Certificate Analysis

You will need Excel 7 or higher, due to the use of a “table” in this spreadsheet. This post is aimed at smaller credit unions and banks that don’t have a staff of financial analysts. You do need access to four pieces of information about each of your certificates. You need the current balance, maturity, original term, and […]

## Over Six Decades of Yield Curve Animation

Important An improved version of this spreadsheet can be found on post: http://pistulka.com/Other/?p=3510 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 […]

## Mortgage Pool Pricing Tool

In April I publish an amortization schedule and I did a poor job explaining what it could be used for. Amortization Model – All Variable was the post and AllVary is the spreadsheet. I added some more options to the spreadsheet, like: 1. A Yield/Market Value/Price table. 2. Interest Only 3. Amortize out to 40 years. […]

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

## Simple Amortization Schedule

A reader asked for a simple amortization schedule that could handle both interest only and a balloon payment. You can find it here: “SimpleA“.

## Breaking Down the Mortgage Mega-Formula

Additional update to formula: MBS Math Formula. Servicing, CPR, Payment Delay, Default Rate & Loss Severity http://pistulka.com/Other/?p=2384 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 […]

## Defeasance 2.0

Important This post and spreadsheet has been updated. Please click this link to Defeasance 4.0 A reader was asking about my original post on defeasance where I used the Treasury strip yields to discount a stream of loan payments to provide an estimate of the prepayment penalty on a loan that has a defeasance clause […]

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

## Certificate Pricing for Credit Unions

The CEO at the credit union where I worked, told the story of how he would price certificates for the small credit union he came from, back in the day. I paraphrase, because like all good stories, it changes each time it is told. The CEO’s credit union was in the same building as another small credit […]

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

See updated formula at: MBS Math Formula. Servicing, CPR, Payment Delay, Default Rate & Loss Severity http://pistulka.com/Other/?p=2384 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 http://pistulka.com/Other/?p=3009: 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 […]