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

# Author: Don Pistulka

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

## Mortgage Loan Pool Settlements

When purchasing or selling a residential mortgage pool between credit unions, or other financial institutions, accrued interest must be considered. One way to get around accrued interest is to settle on the 1st of the month. That is not always practical however. The first of the next month might be a weekend, or a large pool might have some loans […]

## Bank and Credit Union Board Presentations

I will start off by saying that this template only works with Excel 2013 or higher, because we need to use slicers with a table. This dashboard template might be of interest to the CFO or financial analyst that prepares the monthly presentations for the board, finance or ALCO (Asset/Liability Management ) committees. Loans and deposits make […]

## Bond Swap – Horizon Analysis – Calculator

A few posts back we looked at Holding Period Return On Option-Free Fixed Income Securities. I thought we could take that one step further, and show horizon analysis for a bond swap. In order to help explain the calculator, I have divided the analysis into three sections and placed a rounded triangle around each one. The blue […]