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 their portfolio of loans, and we would offer to by a portion of them.

The problem with the process of placing a mark next to good loans was you had to look at each loan separately. If you were looking through thousands of loans, it could get tedious. I used the old worksheet, before sliders could be used on tables in Excel. This new Cherry-Slicer uses slicers to break down the loans into categories. In this manner you can save a lot of time by eliminating loans you are not interested in.. The Excel workbook “CherrySlicer” can be downloaded at the link.

The sample loan portfolio used in the workbook has 1504 loans. My sample does not have all the information you might want to see before picking a subset that you would like to bid on, but this is only a prototype that you can use to make your own model.

First I took the loan data and created a table called “Portfolio”. Then I added two helper columns, one to reduce the individual loan balances to even thousand dollars, and  a column that extracted the year from the maturity date of the loan:

Then I created the four slicers below. In order to include loans that fit the buyer’s needs for length of maturity, size of loans, the interest rate on the loans and servicing.

The chosen loans might look like below. Only the loans that match the buyers requirements are colored. Loans that are eliminated are in white.

All along, as loans are eliminated from the list, a description of the loans chosen appears, along with average weighted data:

The pricing formula is automatically given the average weighted data (blue cells) and the yellow cells are user expectations. For this example, the chosen loans, at a 95% participation rate ($120,680,793), would be worth $117,293,283 at a yield to the buyer of 3.5%.

Download: “CherrySlicer”

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 swap analysis and MBS holding period returns are examples of these calculations. Take the bond swap analysis for example. If you are swapping one bond for another bond with a high yield to maturity, you have a problem from the start. Both yields use an IRR, so you have two reinvestment rates assumed, and you can’t assume two different reinvestment rates. You need to assume only one reinvestment rate, and that is where these alternative examples comes in.

Enough about me. As I pointed out earlier, this post is not meant to explain MIRR (you can find a lot of examples of the pros and cons of MIRR on the internet). This post is to answer a few questions about how a few adjustments can make the MIRR more useful.

You can download the Excel workbook here: MIRR

The first question I run across is “can I use MIRR for periodic cash flows that are not yearly, but are maybe monthly or quarterly? The answer is yes. Like other Excel formulas, dividing the Finance and Reinvestment rates by the number of periods in a year, and multiplying the results by the same number of periods per year, results in an equivalent APR that can be converted to an APY. See sheet “Convert MIRR to Periodic”

Next, I break down the MIRR formula to show how this formula works:

The process starts by breaking down the cash flows into two columns. In the first column are the negative cash flows and the other column, positive cash flows. Take the Present Value (PV) of the negative cash flows and the Future Value (FV) of the positive cash flows.
Then divide the FV by the PV and covert the results to APR and APY. See sheet “MIRR”

First, you have the input cells in yellow. The example assumes the periodic cash flow is monthly.

The cash flows are entered in the yellow cells (the numbers will automatically adjust to fit the cash flows.

Next is are the calculations to get the APR and APY:

I called the last sheet “XMIRR”. Excel doesn’t have an XMIRR formula, but this is a work around for cash flows with dates.

After showing that the PV and FV calculations on sheets “Convert MIRR to Periodic” and “MIRR”   would result in the same APY as the MIRR Excel function (12.310%), I needed to convert a few things to prove that my XMIRR calculations would result in the same APY and APR.
1. I used dates which were all 30 days apart.
2. I needed an option in the PV and FV formulas to use a 360 day base year (XIRR assumes actual/365)
3. Both Finance and Reinvestment rates on the “MIRR” sheet were converted from APY to APR in the PV and FV formulas by choosing monthly periods. For this sheet, I needed to convert those APRs back to Annual Equivalents.
The two cells (I2 and I3) are dropdown boxes. If you want to use this XMIRR calculator, I would change Monthly to Annual and cell I3 to 365 in order to be consistent with the XIRR Excel function.

You can download the Excel workbook here: MIRR

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 that has recently clarified the way overtime should be calculated. Now you find out that you need to go back and recalculate each overtime payment for certain employees. After calculating the additional amounts owed to the employees, for each occurrence, you now need to include interest in the additional amounts owed.

After completing your Excel worksheet with the date of each occurrence and the additional amount owed, you look for a future value formula (using dates) in Excel to determine the total owed each employee, plus interest. Not finding a built-in formula for future value with dates, you need a workaround.

The Excel workbook can be downloaded here:  ipenalty.xlsx

Two future value with dates formulas are shown on sheet “Future Value”. Sheet “Many” gives a format to calculate the total owed each employee (future Value) and the interest broken out separately. The interest rate and the ending date used to calculate the amounts owed are entered once. Two examples are entered to show the format.

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 manner you can copy various models or make your own. It is a little tricky, so I included examples on a separate sheet. Although I have posts that have the PSA prepayment model built in with formulas, I will use the PSA model to show how you might construct that model below:

First the inputs:

As always, only the yellow cells are input cells. You need to include the remaining payments and the age of the loan in months.  The chart represents the CPRs used in the amortization schedule, starting at month (in this case) 11. The Prepayment Speed is the multiplier for increasing or decreasing the speed of prepayments. Entering 200 will double the models speed of prepayments.

Then you construct the prepayment model in the table below, in the yellow cells. The table represent the starting CPR from when the loan was new. Ramp 1 always starts at month one. PSA starts with a CPR of .2 in the first month and ramps up at .2 each month until the 30th month. Then from month 31 on to month 360, it is flat at a CPR of 6. The 6 CPR is not entered, because after 30 months of ramping up at .2, it will be at 6 CPR at month 30. Ramp 2 CPR is entered as 0, to represent a flat ramp. Remember that the ending month will be the last month of the original maturity of the loan you are analyzing,
The chart shows the 10 month old loan, starting on the 11th month, will have a CPR of 2.2. It will continue the ramp of plus .2 CPR until it peeks at a CPR of 6 in month 30. It remains a 6 CPR until month 360.

Now lets take a look at another model to construct, MHP (Prepayment Rate for Manufactured-Housing). In this case, the CPR starts a 3.7 in the first month and increases by .1 CPR each month through month 24. It then flat lines at 6 through month 360:

This time the starting CPR (3.7) is different from the ramp rate of plus .1 CPR each month until month 24, when it reaches 6 CPR. We are still assuming a 360 month loan that is 10 months old. This time the chart shows that starting in month 11, the current CPR is 4.7. The CPR continues up the ramp to 6 CPR in month 24 and then flat lines.

Taking the above model, lets assume that instead of flat lining at a 6 CPR in month 24, we want to assume the CPR starts dropping in month 25 at a rate of .1 CPR per month until month 60 and then flat lines.

In each of the examples above, the monthly CPR (SMM) is derived from  the column “Current CPR”. You will want to keep an eye on the average life.

Download: http://pistulka.com/Excel_Shared/Vectors.xlsx

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 way as the PSA. For twice the speed, enter 200 in the HEL input cell.

Download: HEL

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 subsequent month steps up 10 basis points, until the 24th month. After that the CPR flat lines at 6% CPR. Like the PSA model, you will need to know the age of the pool (months).

Download: MHP.xlsx

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 user defined function (VBA). This should prove easier to use when calculating PSA on numerous pools. I left the old spreadsheet and post up, and directed the old post to this one. Read the old post first for more details.

Download CalCPR2.xlsm

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 IO CMO.  The assumptions I used are as follows:

1. My example is made up of two tranches, a Floater and an Inverse IO.
2. The Floater retains all the principal payments.
3. The Floater rate equals 1 month LIBOR, plus the margin.
4. The IO rate equals the APR collateral rate, minus the Floater margin and minus 1 month LIBOR
5. Assumptions 3 and 4 guarantee the sum of both tranches rates are equal to the collateral rate.
6. The IO rate =MAX(0, IO Index-LIBOR)
7. The Floater rate =MIN(Collateral Rate, LIBOR + Floater Margin)
8. A LIBOR based interest payment is based on an actual/360 basis. For simplicity, interest for this example is assumed to be paid the same as the underlying MBS, 30/360.
9. Only the yellow cells are variables.

Download “CMO-InverseIO”

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 POs and IOs by changing the PSA of the collateral, and the discount market rate. Don’t forget to check the dropdown cell to let the calculations know if the discount rate is annual, semiannual, or monthly compounding.

Download: IO_PO

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 structure differs from the sequential, in that  PAC bonds are designed to produce more stable cash flows. In my example, I assume a $100 million pool of mortgages backed by a government agency. The WAC (weighted average coupon) is 4.5% and the servicing  fee (including the guarantee fee) is 50 basis points. That makes the net interest rate 4.00%. The WAM (weighted average maturity) is 360 months.  As always with my spreadsheets, only the yellow cells are input cells. Other colored cells are formulas or cells that are not used for this example.  

The reason the PAC bond is more stable than other structures is that the cash flows are determined by a high and low collar of  prepayment rates. In this case the prepayment rates are PSA ( Public Securities Association) rates. If you are not familiar with PSA, do a search on my blog for examples and Excel spreadsheets. It is assumed that the mortgage pool’s expected PSA will be 100. The mortgage pool of loans is referred to now on as the “collateral”. 

The initial collar I chose for my example is a low PSA of 50 and a high of 200. My assumption for the initial PSA on the collateral is 100 PSA. As long as the collateral PSA remains within the initial collar, the PAC will get the fixed principle payments, set out in my example.

The chart above shows the principal payment amounts over the life of the structure. The  PAC (orange) line equals the lowest principal payment , 50 PSA (blue) line, until the black 200 PSA line becomes the lowest principal payment at month 131. The PAC principal payment then follows the 200 PSA line until the 360th month, and therefore the PAC receives the lowest principal amount.

Download the workbook called PAC_CMO. There are five sheets in the workbook. The first “Inputs” sheet contains the yellow input cells and the collateral’s amortization schedule. The “Low PSA Collar” and “High PSA Collar” sheets that contain amortization schedules for the collars are hidden. The forth sheet is the “Tranches” sheet. The cash flows are for the Collateral, PAC, and Companion structures.  The collateral structure represents its the cash flow, given the 4.00% net yield and PSA of 100, as I set out in my example. The PAC Bond structure has the principal cash flows taken from the Low PSA and High PSA collars sheets. To the right of those two columns, is a “Minimum” column. That column provides the PAC with the lowest principal of the other two columns, provided the collateral PSA stays between the low and high PSA. The $64,680,507.37, above the “Minimum” cell, is the sum of the minimum principal cash flows and the amount allocated to the PAC.

The Companion bond gets the difference in principal between the collateral and the PAC bond. The $35,319,492.62 is the sum of all principal flowing to the Companion bond.

What happens if the collateral PSA lands outside the collar? As long as the Companion bond has not paid off, the PAC is ensured of prepayment protection.

This table shows the consistent average life of the PAC given an instant change in collateral PSA levels. It uses VBA to calculate your the new numbers, if you changed the assumptions.

Click the button to calculate new numbers.

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 day is a combination of the previous days balance and the interest earned on the previous days balance. The rate used to calculate the previous days interest is the APR divided 365, assuming the previous day fell in a normal year, and 366 if the previous day fell in a leap year. This process continues until the funds are withdrawn. This means every day within a leap year earns 366th of the stated APR. This makes sense, as long as the funds are held for the full year, and receive the interest for the extra day, February 29th.

Now to the formula used to count the number of leap year days and non leap days between two dates. Download the workbook here. The formula I came up with uses an array, given the first and last date, plus the APY. Remember to enter the formula with a Ctrl-Shift-Enter to create the array.

={PRODUCT(1+(365*((1+C10)^(1/365))-365)/((–(MONTH(DATE(YEAR(ROW(INDIRECT(C8&”:”&(C9-1)))),2,29)))=2)+365))*C12}

The first step in the formula is to convert the APY to an APR. The APY in this case is in cell C10.

APR = 365*((1+APY)^(1/365))-365

Next, a formula to determining which dates are within a leap year:

=MONTH(DATE(YEAR(C9),2,29))=2

Cell C9 contains a date. After the YEAR function determines the year of the date, the DATE function combines the month, day, and year to look like this: DATE(2012,2,29) or 2/29/2012. The MONTH function of this date is a 2 so the formula will return 2=2 or TRUE. If the DATE function was to evaluate this DATE(2013,2,29) it would not recognize 2/29/2013 as a date, but would evaluate it as 3/1/2013 instead. Then, the MONTH function returns 3, which makes the formula 3=2, which is FALSE. So, this formula results in either a TRUE if a leap year day or FALSE if not.

The cell references C8 and C9 are the starting and ending dates.  Excel recognizes the dates as  numbers. The & signs combine the two date numbers, with the “:” separating the two. This appears to be a range, surrounded by two apostrophes or a text string. The INDIRECT function returns the reference specified by a text string. Then the ROW function will interpret the range as row numbers. The row numbers result in an array of date numbers, so we can now add the leap year formula to evaluate each date for leap year days. Notice that I subtracted 1 from the last date (cell C9). When calculating interest, the last day does not counted. The last date does not earn interest.

The array goes through each date to determine which are leap days. Each date results in a TRUE for a leap day and a FALSE for a non-leap day. Next, the double dash marks (–) convert each TRUE to the number 1 and FALSE to the number 0. Now I add the number 365 to each number 1 or 2 to get a 366 for leap days and a 365 for non-leap days.

Finally, the APR is divided by each day (either 366 or 365) and 1 is added to each. The results are then multiplied by each other using the PRODUCT function. Then multiplied by the present value ($1,000,000 in my example).

Use the Evaluate Formula option to step through the formula.

The on the “Proof” sheet I have manually constructed a number of rows, representing each date, along with column B to determine if each date is a leap year or not. Column E  is a running total of interest. Cell G6 is linked to the formula to compare the results in cell E3.

Romans 1:17, “For in it the righteousness of God is revealed from faith for faith, as it is written, ‘The righteous shall live by faith.’”

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, such as:

=FV(APY,(MAX(Dates)-MIN(Dates))/365,,-XNPV(APY,Data,Dates))

Fictitious Example: Your firm, First National Bank, is the lead underwriter and servicer of a $50 million commercial loan. The property is a large apartment complex. The loan is participated out to four other commercial lenders. The combined group of lenders is called The 325 Commercial Development Group. Soon after the loan settled, the borrower stopped making payments. It was learned that the property was run down and the local municipal public works department was threatening to condemn the property. Obviously, there was deception on the part of various persons, to conceal the actual condition of the property. Since the participation agreement stated that all participants were obligated to do their own due diligence on the condition of the property, all five members of the 325 Commercial Development Group are responsible for their portion of the loan.

The 325 Commercial Development Group takes position of the property and sues the commercial broker, property manager, the inspection firm and various other players for a fixed amount. As the servicer for the loan, your firm pays all expenses and collects reimbursements on the property while the lawsuit proceeds. These net expenses will be applied on a pro rata basis to members of the group at the end of the lawsuit.

The 325 Commercial Development Group wins the lawsuit for a set amount. Your firm has paid out a large amount for expenses over the three years to clean up and repair the property . Your job is to calculate how much interest has accumulated on these net expenses, and allocate the interest and expenses to all members of the group. The final distribution of proceeds from the lawsuit including net expenses, and proceeds from the sale of the property, is made on 2/15/19.

There are three years of expense and credits, the first dozen or so look like this:

The interest rate is entered on the data sheet:

Download: http://pistulka.com/Excel_Shared/FVFormulaExample.xlsx

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 Modified durations, due to the options embedded in mortgage loans. The borrower has the option to put the loan back to the lender. Typically, the duration calculation assumes a plus or minus 100 basis point change in interest rates. Any factor that will change the cash flows of a pool, when rates are changed, needs to be considered when calculating effective duration.

The factors that will affect the initial cash flows are entered as follows:

Now comes the factors that will change the cash flows of the pool when interest rates move plus or minus the amount entered above (100 basis points or 1.0%):

The assumptions of factors that will change, given the change in interest rates, are entered in yellow cells. These entries require either an historical bases, or the experienced users best guess as to how the factors might change.

Then the calculations of effective duration and convexity are made, along with the convexity adjustment. The adjustment is needed because of the non-liner relationship between prices and yields.

Lastly, I included a chart, showing the negative convexity of the mortgage pool. Negative meaning that when interest rates fall, prices will rise less than prices will fall, when rates rise the same amount.

Download: EffectiveDurationFormulas.xlsx

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 – If servicing is retained by the seller, enter the servicing fee.
If the buyer intends to service the loans, enter zero.

* Market Yield – The discount rate used to calculate the net present value (price) of the cash flows produced by the pool of loans, based on the various assumptions.

*Days Delay (default 30 days) – Mortgage loans pay interest in arrears, meaning that the payments are typically made at the end of 30 days. Servicers will routinely pass through the payments to the mortage holder after the actual payment date, commonly due to a grace period. Depending on the sale agreement, the servicer will collect the payements and pass them on to the mortgage holder on a specific date. For example, the agreement might state that the cash payments will be sent to the buyer on the 15th of of the next month.
That delay in payments will reduce the return to the loan holder, by delaying the reinvestment of the payments. To compensate for this loss, the price is reduced the approperiate amount, depending on the discount rate (market yield). The Days Delay input would then be entered, using the example above of the 15th day of the next month, by entering 44. That is the normal 30 days plus 14 additial days (15 -1). Some MBS producers, for example, might have payment delays for as long as 75 days.

The average life is important, because although prices will vary with the same market yield, the average time you will get that yield could make a big difference. For example, take the top row of the table with an 8.00% market yield. The price ranges between 92.842 and 84.317 for the same 8.00% yield. The average life at 92.842 (using the assumptions provided) is 3.88 years and 6.72 years for a price of 84.317, using another set of assumptions.

Download: http://pistulka.com/Excel_Shared/PricingTable.xlsx

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”. As I was looking through some of my old Excel workbooks, I came across this method for calculating spot rates, that I should have used in the original post.

For an explanation of the first three bootstrap methods, see my post “3 -Ways to Bootstrap Spot Rates for the Treasury Yield Curve” at

http://pistulka.com/Other/?p=2089

After viewing the first three methods, come back to this post for an explanation of the forth method.

Method four on sheet “Fourth Method”:

This method may or may not be easier to follow than the first three. The difference between the formulas on sheets “Formula” and “Fourth Method” is that the calculations on the forth method are broken out into two extra columns. As was true with the other methods, the 6-month and 1-year Treasury yields are considered to already be spot rates, in that they are Treasury bills with no coupons, only a final payment of principal.

The first new column are the discount factors. This is a running present value of $1, calculated using the spot rate and term for each row. The second column is the present value of each coupon payment using the sum of all the previous discount rates multiplied by the semiannual coupon payment.