Readers might remember my original post and spreadsheet http://pistulka.com/Other/?p=1473 in August 2015 called Cherry-Picking a Loan Portfolio. That spreadsheet relied upon going through a loan portfolio and placing an X next to the loans you were interested in purchasing. It was one way I used to purchase participation loans from other credit unions. They would show […]

# Author: Don Pistulka

## THE MIRR EXCEL FORMULA WITH DATES (XMIRR)

Most of my 50 year career was in fixed income investments (Bonds, MBS, etc.). We did not use MIRR, but we did have concepts that had some of the same advantages. If you have been following this blog, you have seen terms such as Holding Period Return (HPR) or Horizon Analysis. My posts on bond […]

## Interest Penalty for Overtime Changes in State Law

Disclaimer: I am not an lawyer, and offer this spreadsheet free with no guarantees either written or implied, as to the appropriate use of this spreadsheet, or its accuracy. I am not recommending this spreadsheet to be used for any particular state or municipality. Let us assume that you live in a state […]

## CPR Prepayment Vectors

Note: I have updated this post and spreadsheet with another way to create your own CPR vectors: Annual CPR Vectors Previously, I showed a way to vary the CPR prepayment rates http://pistulka.com/Other/?p=1300 on an amortization schedule. That method did not include ramps. This spreadsheet shows how to ramp CPRs up, down, and flat. In this […]

## HEL (Home Equity Prepayment)

HEL (Home Equity Prepayment) is similar to the PSA prepayment model in that it starts a 2% CPR in the first month and increasing at 2% CPR until month 10. It then remains at 20% CPR for the remaining life of the pool. Increases or decreases to the standard 100 HEL are made the same […]

## MHP (Prepayment Rate for Manufactured-Housing)

I have to admit that I have never purchased or sold manufactured housing loan pools, but am aware that those that do own or trade these pools, have different assumptions when applying prepayment rates. The MHP calculation is very close the PSA ramp, except that the first month starts with a 3.7 CPR, and each […]

## Change to “Calculate Historic CPR and PSA”

In the previous spreadsheet used to calculate historic CPR and PSA, I used a separate sheet in the workbook to run an iteration on an amortization schedule in order to calculate PSA. An iteration is only necessary if the loan pools are 29 months old or less. I have replaced the amortization sheet with a […]

## CMO-Inverse Interest Only (Inverse IO)

A reader asked if I had an example of a CMO Inverse IO, and I did not. Although I have not had access to a Bloomberg to check my assumption, I thought I would give it a try. Actual IO’s may vary from my example, but I think I have captured the essences of an […]

## CMO -IO/PO PAIR

This is my fourth free CMO Excel spreadsheet. This post is going to be very short. You can find a lot of information online, so I am not going to add much. After entering the information on the “Inputs” sheet (yellow cells only), go to the “IO_PO” sheet where you will find ways of analyzing […]

## CMO – PAC

I have posted two other examples (and Excel workbooks) of CMO structures in the past. Floater & Inverse-Floater CMO and Sequential Pay CMO. Now, we are going to look at a CMO PAC (Planned Amortization Class). It is a simple example, with only two tranches, the PAC and Companion (or sometimes called Support) bonds. The PAC […]

## Future Value Bank Interest in Leap Years

Not all banks treat leap years the same, when calculating interest on deposits. Many financial institutions use basically the same method (or a close variation) I used in this formula. Check with your bank before relying on this method. The method I am referring to is: Interest is compounded daily. The balance on any given […]

## Example of using the Future Value Formula on a Commercial Loan

My first post on this blog was in August of 2014 and it was called: No XNFV Excel Function? The idea was a formula for calculating future value with dates. There was a XNPV function for present value, but no function for future value. My formula looks like this: =SUMPRODUCT((1+APY)^((MAX(Dates)-Dates)/365)*Data) Of course there are other workarounds, […]

## Effective Duration & Convexity of a Loan Pool Using My Mega Formula

I have posted previous calculations of effective duration and convexity for a pool of mortgage loans, however past spreadsheets required three amortization schedules, on separate sheets. Using the mega formula for pricing, this spreadsheet (in my opinion) is cleaner and easier to understand. Effective duration is used to analyze mortgage pools, rather then Macaulay or […]

## Mortgage Loan Pool Pricing Table with CPR, CDR, & Loss Severity

Before I added default and loss severity to the mega formula for the price of a mortgage pool, I produced a price/yield table. That old post was http://pistulka.com/Other/?p=707. This post and Excel workbook includes the added variables. As is true with all of my spreadsheets, only the yellow cells are input cells: * Servicing – […]

## A Fourth Way To Bootstrap Spot Rates

In May of 2015 I published a post called “3 -Ways to Bootstrap Spot Rates for the Treasury Yield Curve”. There was also an Excel workbook linked to that post. The workbook link is: http://pistulka.com/Excel_Shared/3_Spot_Rates.xlsm Rather than posting a new workbook, I am leaving the old workbook, but I added a new sheet called “Fourth Method”. […]