I am aware that the ranges have changed, but this spreadsheet can be adjusted for different ranges. The purpose is mainly to show the difference between using the tiered method, and interpolating a rate for each individual score. Obviously, one could just use more tiers, or to get the same results, assigning a different rate to each FICO score. The interpolated rates will almost always be higher than the tiered rates, except at the change in the tiered rates. The downside to the interpolated rates is that it might price you out of the market for some loans.

Also, this spreadsheet has not been used in a real world situation, so there can be errors I have not found.

The user starts by picking a loan type and if there are any discounts for direct deposits, automatic payments, etc.

**Each yellow cell is a dropdown box.**

Then the user enters a FICO score and the term. Again, both are dropdown boxes. The rate calculated is not the rate that falls between the FICO tier, but an interpolated rate.

The above information is sufficient to construct a tiered FICO table for that loan type.

The risk manager can compensate for parallel shifts in interest rates, by simply changing the rate on the 12-36 month New Auto rate.

The example above provides an interpolated rate of 12.13%. This compares to the 11.35% taken from the tiered table. The chart below shows difference between the tiered and interpolated rates for this loan type. The interpolating takes place between each change in the tiered rates.

Download: FICO

]]>A few years back, a young man contacted be about calculating the present value of an investment made at his firm. He had just taken over for the previous person in charge of investments and was having trouble calculating the present value of one of the short-term investments. The cash flows looked like this:

He knew that the interest rate was .67% and the notional amount paid for the investment was $181,600,000. Using the Excel formula for present value (XNPV) he calculated the present value to be $181,607,100.75. Assuming the payments were based on LIBOR (Actual/360), we calculated the present value to be $181,601,298.47. It turned out that the investment was based on an actual/360 basis, but was a fixed rate amortizing investment, based on LIBOR, which is an actual/360 basis.

I thought readers might be interested in how to calculate the present value of an amortizing investment with dates, either an amortizing loan or a fixed rate amortizing swap based on LIBOR. I made the day count and base year variable. The day count can be Actual or a 30 day month. The base year can be 360 or 365.

The present value discount factor formula is:

The present value is then the sum of the discount rates times the cash flows.

I also included the future value of the cash flows on the same basis.

Then I created a amortization schedule as a proof that the present value calculation will amortize the cash flows to a zero balance.

The reason the spreadsheet is .xlsm (contains macros), is because I also calculated the same present and future values with UDF in VBA.

Download PVA.xlsm

]]>

Users can add additional yields at:

or click this button on the “Yields” sheet.

Spot rates were calculated from interpolated Treasury coupon rates (to simplify the calculations, the Treasury rates were assumed to be par bonds). Then a forward yield table was developed, with columns of forward rates for various years in the future. Each time a new historic (past) date is entered on the chart sheet, new spot and forward rates are calculated.

Download: PFR.xlsx

]]>

I have hopefully improved the chart and brought the yields curves up-to-date. You can also add daily curves if you like. The Excel file contains VBA, so some firms might not allow downloads.

The yield curves are on a monthly basis from 1962-1989 and daily from 1990 on.

Have fun.

Download:http://pistulka.com/Excel_Shared/TR.xlsm

]]>

If you are involved in commercial lending or perhaps municipal bonds you are probably familiar with the term defeasance. Commercial loans normally have hefty prepayment penalties, while some loan documents prohibit prepayment altogether. Commercial loan defeasance is a collateral substitution method that guarantees the lender the same cash flow as the loan, in return for prepaying the loan. That guarantee usually involves substituting government guaranteed Treasury obligations. This spreadsheet only calculates the penalty, not any other expenses involved with defeasance.

Copy/pasted from the Treasury site by clicking the button:

After the spot rates are calculated out every six months for 30 years, the rates are converted from semiannual to monthly compound equivalents:

In the “Loan Amortization” sheet, the loan information goes into the yellow cells:

The present value of each loan payment is calculated using the spot Treasury rates at each payment. The sum of the present values is subtracted from the loan balance. The difference is the prepayment penalty. In certain conditions, where interest rates have risen substantially since the loan was originated, the borrower may receive a premium to defease the loan. Of course the premium would have to be sufficient enough to pay for the other expenses involved in defeasance, which can be substantial.

Download: http://pistulka.com/Excel_Shared/defeasance_4.0.xlsx

]]>Like my other spreadsheets that require spot rates, I start with a source for current Treasury yields. You can copy/paste them directly into the spot rate sheet in the yellow cells:

Next, because we don’t have a yield at every coupon payment, we need to interpolate a yield at each of these periods. This is accomplished through a linear interpolation. This linear interpolation looks for periods that we do have a yield and the next period that we have a yield and interpolates between the two. The formula below is entered next to the first 6 month yield and copied down:

=FORECAST(F10,OFFSET($C$7,MATCH(F10,$C$7:$C$18,1)-1,1,2,1),OFFSET($C$7,MATCH(F10,$C$7:$C$18,1)-1,0,2,1))

This method differs from a linear interpolation that calculates a straight line through the data points. The chart below shows the difference. Note how the above method follows the actual yields:

Now that we have a yield at each coupon payment (every six months) we can calculate the spot rates. Spot rates are considered zero-coupon bonds. The spot rate calculations assume the given yields represent par bonds. A discount factor is calculated for each coupon payment, that is actually a present value calculation. At each coupon payment all the previous discount factors are summed and multiplied by half the yield (which is also a coupon payment). We can now calculate the present value of all the previous coupon payments. The last cash flow is the 100 par value (or corpus) plus the last coupon payment. Since we now have the present value of the coupon payments and the future value of the last payment, we can calculate the semiannual return or spot rate. Below shows an example of the calculation of spot rates:

Next we consider the math behind forward yields. Implicit in every spot rate curve is a forecast of future yields. Assume that we want to know what the current yield curve is implying about the two year Treasury yield, five years from now.

The calculations for forward yields are actually break even calculations, or what Bloomberg used to call “gap rates”.

If investors are willing to pay 2.4393% for a 5-year note, and forego earning a higher yield of 2.5463% on a seven year note, the five year investors are implying that when the five year note matures, there is a two year yield that the funds can be reinvested at which will equal the total return of the seven year yield. The calculations for that two year yield are shown above and result in a forecast of 2.814% for the two year note, five years from now.

Now we convert the above formulas into a yield curve table:

The columns, going down from the six month to the 30-year represents a yield curve in the future. The column with the one in the dark blue cell, is the forward curve, one year from now. Likewise, the column with a five in the dark blue cell, represents the yield curve five years from now. Each of the years in the dark blue cells are dropdown boxes that allow for any year you wish to view.

The rows on the other hand, represent the forward yield for that particular term. The one year row shows how the one year yield will change at each year going out into the future.

Next to the table are two yellow input cells that allow you to find an implied future yield at some year in the future.

In the chart sheet, you can compare any future yield curve, with the current spot rate curve:

Before you download a spreadsheet, note that there are two spreadsheet versions. One has a format extension of .xlsm. That is because there is a very small VBA program included that formats the Y axis of one of the charts and makes the chart easier to read. Some firms will not allow downloads with VBA included. If you find this to me a problem, you can download a second version with no VBA and adjust the chart manually.

Downloads:

]]>

This post starts by entering the U.S Treasury Yield curve by copy/pasting the rates from the Treasury web site.

The yields are then interpolated in order to calculate spot rates using the bootstrap method.

The spot rates are then linked into sheet “NSS”, and are used to smooth the yield curve using the Nelson-Siegel-Svensson Model.

Excel’s Solver is used to calibrate the model:

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

]]>This post takes the previous post one step further and uses the spot rates to calculate the forward curve consisting of the implied forward monthly rates from one month out to 30 years.

Download “Forward_Price”

I used a smoothing method for the forward rates as I explain below:

I could have use a smoothing method on the initial Treasury yield curve using something like a Piecewise Cubic Spine method. This might have helped with the exaggerated forward curve. Adding to the exaggerated forward curve was the fact that I used the current Treasury curve which is relatively flat and contains unusual patterns. For example, the three year security was one basis point lower than both the two and five year securities. Also, the two month bill was higher than both the one month and three month bills.

Below shows the beginning portion of the amortization sheet that calculates price given the forward one month curve.

Download “Forward_Price”

]]>Start by downloading the Treasury yield curve from the Department of the Treasury’s Resource Center.

Then, using the bootstrapping method, calculates the spot rates:

For more on the bootstrap method for calculating spot rates, see my post “A Fourth Way To Bootstrap Spot Rates.

The inputs for the Treasury yield curve looks like this:

The calculations run down for 360 months, but the first 12 months look like this:

On the Amortization sheet, you have the traditional pricing method, which those that follow my posts will recognize. The input data looks like this:

This is followed by the traditional pricing method, based on one discount rate of 3.00%:

In order to calculate the Z-Spread, enter the price in the yellow cell below and click the button:

This indicates that both the price using the single discount rate of 3.00% and the price using the Z-Spread of .599% will be the same ($103.48275).

You can also calculate price using the Z-Spread, by entering the Z-spread in the yellow cell and clicking the green button:

Download: Z

]]>Download Excel File: http://pistulka.com/Excel_Shared/FHLB_spot_rates.xlsx

I used the formula below to interpolate the rates, and copied it down. The x in the formula is the yield curve range in the market reference curve.

=FORECAST(G8,OFFSET($C$7,MATCH(G8,x,1)-1,1,2,1),OFFSET($C$7,MATCH(G8,x,1)-1,0,2,1))

X in the formula is the market reference curve

The FHLB may use a different method for interpolation and may offer loans between the dates on their market reference curve at different rates, but for purposes of this post the, above formula was used.

Treasury yield curve I have attempted.

This is also the first spot rate post with monthly payments, rather than the Treasury’s constant semiannual payments and a corpus.

Each month of the market reference curve was converted to years.

I used the first and forth methods that I listed in my Treasury spot rate post, to calculate the spot rates. The cash flows are calculated the same way as a loan, using the pmt() Excel function. The two methods I used are both bootstrap methods. Sheet “Method 1” calculates a cumulative discount factor each month, that is used to calculate the present value of all previous cash flows to that point. That present value is then multiplied by the interpreted yield at that maturity. Sheet “Method 2”

]]>

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

]]>