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

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