Download Birthdays.xlsx

]]>- The end of the pay period (there was a five day delay between the end of the pay period and the day we were paid).
- The dates I was paid.
- The current day.

The first calendar is a yearly calendar. The whole year of payroll periods are set out on one Excel sheet, using conditional formatting. The only two pieces of information needed are any end of pay period date, and a pay day date. The calendar is perpetual after the two dates are entered. At the time of this post we are in 2019, however you will notice that I entered two dates in 2010 to start the calendar. On each first day of the year, the calendar will recalculate the new dates for that year. I some cases today’s date will be the same as a pay or period date, and today’s date overrides the other.

On the second sheet is a monthly calendar that uses the dates entered on the yearly calendar and shows the current month. Also, I have includes the holidays my firm observed and the holidays appear on the calendar. Like the yearly calendar, it is perpetual. The only changes you might make are to add or subtract holidays in the table. Note that each holiday is a formula, so that the holidays are perpetual also.

Download Payday.xlsx

]]>The assumption for this model is that all CDs maturing in the next 12 months will roll over into the same term. The interest rate (reinvestment rate) for these CDs will is determined by a lookup table of interest rates. The rates in the table are the organizations forecast of interest rates on CDs over the next 12 months.

The model will then take all CDs that mature in the next 12 months and do a lookup from this interest rate table. For example, a 24 month CD that mature in March will be reinvested at 2.35% for the remaining time of the budget period. CDs that do not mature over the next 12 months are also calculated, so interest rate expense for all CDs is given.

The example below shows the value of each CD that matures during the next 12 months, including interest. Then that balance is reinvested until the end of the period at the new reinvestment rate:

Because the model is limited to one rollover during the budget year, 3-month and some 6-month CDs will mature and be reinvested at a new rate once, and will assume to earn that rate until the remainder of the year.

Download: cds.xls

]]>I have not used the linked spreadsheet in any real-world applications, so there may be errors I am not aware of.

For a mortgage or commercial loan, this would normally mean modifying the original loan. Amoung other things, the modifications could be:

Skip one or more payments

Change the interest rate for one or more payments

Pay interest only for one or more payments

Increase the number of payments

Require a forbearance amount be paid

TRD modifications can produce a loss of income for the lender. One way to recognize the potential losses (impairment), is to calculate the present value of the differences in cash flows between the modified loan, and the original loan. The discount rate used could be the rate on the original loan, or the prevailing market rate.

First an amortization schedule of the original loan is produced using the inputs in the yellow cells:

Next, the inputs in yellow are used to create another amortization schedule along with the modifications. Again, only the yellow cells are inputs. The modified loan can use any number of months to calculate the payments on the modified loan, but defaults to the months remaining in the original loan. To use a different number of months for this calculation, enter the months in the yellow cell to the right. Next, if the lender agrees to allow interest only payments to the first few months, enter the number of months in the “Interest Only Months” yellow cell. The “New Term Months” are not an input, but is the sum of the terms in the “Modified Rates” table. Hopefully, the rest of the inputs are self-explanatory:

P.S. The light colored letters next to some cells are the named values used in the calculations.

The two amortizations and the differences in cash flows, are represented on the “Present Value” sheet:

Download: TDR

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

]]>