The workbook contains two sheets. One is a sample of my model and the other the BMA model. I have set the BMA model to P & I advanced. In other words, there is a guarantee of principal and interest to the buyer.

Both models use the same inputs as below. The calculations below are for the first month’s payment only.

As I pointed out in my last post, my model uses a sequence of principle paydowns starting with the expected amortization, then prepayments, then defaults and loss severity. Each amortization paydown is dependent upon the calculation of the amortization that precedes it.

The BMA model is also sequential. In this model, defaults are calculated first. Actual amortization needs defaults before it can be calculated. Prepayments need the expected amortization derived from the amortization factors. The calculations from the amortization factors mimic my calculations of needing the actual amortization first. Note that prepayments for the first month are the same for both models (in blue).

Both are sequential calculations but in a different order:

The purist that argues that defaults should come before expected amortization and prepayments, should also insist that prepayments come before expected amortization, which the BMA model does not. Prepayments in the BMA model are calculated after expected amortization, as does my model. In fact, if you set default to zero in both models, they amortize exactly the same.

Default models are primarily used to predict credit risk. They are only as good as the users inputs. Although my model does not conform to the industry standard model, I continue to feel my model can be a useful tool for users that are not required to use an industry standard.

Download:BMA

]]>I felt obligated to defend the model I use. Even though the model I use does not conform to the model adopted by the Bond Market Association (BMA), it does not mean that the BMA’s model is the only way CDR can be applied. The key to the model I use lies in the use of sequential principal balances (see the example below).

In my model, for each month, it is assumed that all the expected principal is paid by the borrowers by the end of the grace period (let’s assume the 15th of each month). That produces a principal subtotal that the percent of prepayments (CPR) are than applied. After the prepayments have been applied, that produces another principal subtotal.

A graphic depiction of the process is on the attached Excel workbook, sheet “Default Model” and below on this page.

During the month (I used the second half of each month in my example to clarify the math, but it can be anytime during the month) the servicer is notified that a certain percent of loans will be in default next month. The servicer reduces that percent from the principal subtotal balance this month. The new ending principal balance for this month, is also the starting principal balance for the next month.

Both models assume the same definition for loan defaults and prepayments (CPR, CDR, PSA and SDA). The BMA’s model assumes the percent is applied to the month’s beginning value, where the model I use assumes the default percentage is applied the principal balance after expected and prepaid principal.

Models are comprised of various assumptions. There are numerous models for predicting defaults. Neither of these two models here have any chance of being a precise predictor of future events. That is why they are called models.

Originators of MBS would need to use BMA’s model to be in compliance with industry standards. The typical reader of this blog, however, would most likely be on the buy side of MBS with guaranteed P&I and not be concerned with defaults. Defaults do increase the prepayments to the owners of guaranteed MBS but increasing the CPR can compensate for that.

Firms that buy and sell whole loan pools between each other, such as banks, credit unions, and underwriters will continue to find my default model a creditable measurement of credit risk.

I have left out guarantee and servicing fees to make the data simpler.

Download: CDR_Model

]]>

https://www.mtgprofessor.com/A%20-%20Amortization/how_does_simple_interest_work.htm

Also, for an explanation of the difference between simple and compound interest, again the “Mortgage Professor” gives a good explanation:

https://www.mtgprofessor.com/A%20-%20Simple%20Interest%20Mortgages/simple_interest_nomenclature.htm

The Excel spreadsheet starts with the input cells in yellow.

I used the standard payment formula in Excel, =PMT(). This function works fine for standard mortgage loans, but for simple interest loans, the actual last payment will most likely differ from the expected final term.

I give the above warning on the spreadsheet, however, although the typical commercial loan will be amortized over a longer period of time (i.e. 30 years), they usually have a balloon payment in 3, 5, 10, etc. years. Therefore, the final payment after, for example 30 years, will never be reached.

I break out the payments into the total term and the balloon term. In the default example, the balloon is due in 10 years (120 months).

Download: SICLA

P.S. Thanks to the people at , where I could check my numbers.

]]>- Cash & Duration Neutral Butterfly (aka Duration-Hedged Barbell)
- Fifty-Fifty Butterfly Swap
- Regression Weighted Butterfly Swap
- Maturity Weighted Butterfly Swap

Note: Although this type of swap shows positive convexity at changes of + or – 25 basis points, it does not necessary display positive convexity for smaller yield changes.

The formula for the Wings of the maturity weighted butterfly swap is the same as the regression weighted butterfly swap. The difference is that instead of using historic spreads to create a regression coefficient, the maturity coefficient is:

C is plugged into the formula for the weighting of the Wings:

Download: Butterfly-W.xlsx

]]>- Cash & Duration Neutral Butterfly (aka Duration-Hedged Barbell)
- Fifty-Fifty Butterfly Swap
- Regression Weighted Butterfly Swap
- Maturity Weighted Butterfly Swap

Note: Although this type of swap shows positive convexity at changes of + or – 25 basis points, it does not necessary display positive convexity for smaller yield changes.

Typically, short-term interest rates tend to move faster than long-term rates. In order to adapt the butterfly weightings to compensate for this, a regression coefficient is needed. On the sheet called coefficient, I calculated the spreads between the Body minus the short Wing and the long Wing minus the Body on a daily basis over the last 5 years. Then with Excel’s built in regression analysis, I calculated the coefficient between the two spreads.

Then using the formula below, I calculated the new weightings of the two Wings. An advantage of the regression weighting, rather than the fifty-fifty ( both of which are not cash neutral) is that the initial cost of the swap can be reduced. Using my default bond data, the fifty-fifty swap required borrowing $4,790,064, while the regression weighted swap required only $3,657,847.

If the number one is substituted for the calculated coefficient, the weighting become the same as the fifty-fifty butterfly swap.

Like other swaps in this series, instantaneous parallel shifts, either up or down, will show a profit (positive convexity).

Download: Fifty-Fifty-R.xlsx

]]>

- Cash & Duration Neutral Butterfly (aka Duration-Hedged Barbell)
- Fifty-Fifty Butterfly Swap
- Regression Weighted Butterfly Swap
- Maturity Weighted Butterfly Swap

Note: Although this type of swap shows positive convexity at changes of + or – 25 basis points, it does not necessary display positive convexity for smaller yield changes.

Things to consider:

- This swap gets its name from both Wings being equally duration weighted against the body. The wings each have the same $duration as half the Body, when weighted by par value. (Curve neutral to steepening and flattening shifts, while the Body yield remains constant.)
- Like other swaps in this series, instantaneous parallel shifts, either up or down, will show a profit (positive convexity).
- Unlike the Cash & Duration Neutral swap, this swap is not cash neutral and requires additional cash to initiate the trade.
- More likely be used to take advantage of arbitraging an expensive Body yield, relative to the Wings.

Allows for analysis of parallel swifts:

Also analysis of steepening or flattening curve movements:

Formulas:

Download: Fifty-Fifty

]]>

1. Cash & Duration Neutral Butterfly (aka Duration-Hedged Barbell)

2. Fifty-Fifty Butterfly Swap

3. Regression Weighted Butterfly Swap

4. Maturity Weighted Butterfly Swap

This Excel spreadsheet shows how to set up a cash & duration neutral butterfly swap. The nomenclature I use to describe the three securities involved in this swap are “Body” for the middle security, and “Wing #1” and “Wing #2” for the other two:

Some Graphic’s:

The formulas for calculating the par value of the two wings is:

Statistics are:

Download: Butterfly

Assume the borrower wants to make payments quarterly, semi-annually, annually, or bi-monthly, instead of monthly, in order to match the borrowers source of income. You might want to oblige the borrower, but you still want to earn monthly compounding.

You would like the option of calculating interest on the loan based various methods:

We start with the input cells in yellow:The cells shown below include the payment per period, the equivalent compound rate and the prorated first period interest. The equivalent compound rate allows the lender to earn interest on a monthly basis, while allowing quarterly payments. All calculations that include the loan rate use this rate. It is assumed that any prorated interest, due between the settlement and first payment, will be considered as the first interest payment. Depending on the length of the an odd period, this can cause positive or negative principal amortization for the first payment. In my default case, the prorated period is exactly one quarter (90 days, given a 30/360 interest method, so the normal interest payments is used.

Besides the amortization schedule, a summery of the loan is as follows:

These two tables allowed me to simplify the calculations rather than doing redundant lookups in various formulas :

Download: CLA

]]>Then on sheet “Vector” enter the CPR for each year of the life of the pool from the year issued:

The year column will ask for as many years CPRs as needed, so enter a CPR only next to a year.

The other calculations on the “Vectors” sheet are needed for the monthly CPRs from issue month and from the current month these CPRs will start to apply. The chart will show the annual vectors and where you are currently:

Back to Sheet “Amortization”, you will see the pool being amortized along with your monthly CPRs.

Download: VectorA

]]>

I used the dates and closing prices. If you use this sheet for another set of data, remember to delete any NA() or blank rows. To calculate the SMA, I placed the formula below into **cell C5 **and copied it down over 8000 rows (although that many prices is probably too many):

=IF(ROW()<Periods+4,””,SUM(OFFSET(B5,0,0,-Periods,1))/Periods)

Periods = Days looking back to average

The Periods +4 was because the data starts on row 5. The formula creates a range going back in time, that is equal to as many prices as the name Period represents and divides the sum of the range by the name Period, or if there are insufficient prices to average the number in Periods, it returns errors that are replaced with blanks.

Next, for the EMA, I put this formula in **cell D5**, and copied it down:

=IFERROR(IF(A5=$J$2,C5,IF(A5>$J$2,(Alpha*(B5-D4)+D4),””)),””)

Alpha = 2/(Periods+1)

This formula will return blanks, unless the date in column A is equal to the starting date ($j$2). The formula then uses the SMA next to that date as the starting point for the first EMA. From that date on to the Ending Date it uses Alpha*(price in column B-previous EMA in column D) +previous EMA in column D.

Now we come to the other input cells:

For the calculations and the chart dates I used the starting and ending dates above in the yellow cells, and for the number of periods to average I used 200. The date cells are dropdown boxes, so that only a date in column A can be entered. You can cause an error by either having starting and ending dates backwards, or using a starting date that does not allow the SMA formula to go back the number of periods needed.

Excel will always default to a zero as the lowest value in a line chart. By clicking the button “Adjust Chart” the chart will adjust to the minimum and maximum prices in the range.

The “Buy” and “Sell” points are not part of the model, they are there just to show you how some traders might use the SMA and EMA when they cross each other.

Download Excel Workbook: EMA

]]>

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”

]]>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.

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: HEL

]]>