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

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

Holding Period Return On Option-Free Fixed Income Securities

Back in January of this years I wrote a post (Mortgage Pool – Holding Period Return) accompanied by a spreadsheet, on how to calculate the holding period return and the internal rate of return, when buying and selling a pool of mortgages. This post uses the same logic, but is applied to option-free fixed income notes and bonds. I […]

Financial Ratio Gauges

When I first retired from the position of CFO, I thought I might try and help boards of directors of smaller financial institutions, that may not have a financial background, better understand their obligation to ask management the right questions. Typically, boards are given large board reports with dozens of pages of financial information, with one or […]