Tag Archives: All Excel Spreadsheets are free

Floater & Inverse-Floater CMO

This is a follow-up to my last post, Sequential Pay CMO. The purpose of this example spreadsheet is to show a simple example of how floating and inverse-floating rate securities can be produced from a fixed rate pool of mortgages. It is not the only way floating rate CMOs can be structured, but for example purposes, the simplest.

We start (sheet “Pool”) with a $10 million pool of fixed rate 30-year mortgages, packaged and guaranteed by a government agency. The WAC (weighted average coupon) is 6.50% with a 50 basis point servicing / guarantee fee, for a net coupon of 6.00%.  The initial assumed prepayment assumption is 100 PSA. This can be changed later.

Both the floater and inverse-floater will have a pro rata collateral distribution. In other words, both will receive principal payments as a percent of the established distribution. The example distribution on sheet “Summary” is 75% for the floater and 25% for the inverse-floater. You only need to enter three yellow input cells to create both the floater and inverse-floater initial stucture:

  1. The percent of the pool that will make up the floater (example is 75%)
  2. The floater’s floor is (.25% in the example, the inverse-floater is set a zero)
  3.  The current index (1-month LIBOR) initial rate (2.50% in the example)
Obviously, since there are only two tranches, the inverse-floater has to have a distribution of the remainder of the pool, or 25%.
Floater’s Cap:
Net Collateral Coupon / Percent of Floater’s Pool
6.00% / 75%
 Floater’s Initial Coupon:
Initial Index + Floor
2.50% +.25%
Inverse-Floater Cap:
Floater % of Pool / Inverse-Floater % of Pool * (Floater Cap – Floater Floor)
75% / 25% * (8.0% -.25%)
3 * 7.75%
Inverse-Floater Initial Coupon:
Inverse-Floater’s Cap – Floater % of Pool / Inverse-Floater % of Pool * Initial Index 
 23.25% – 75% / 25% * 2.5%
23.25% – 3 * 2.5%
 The coupons for both tranche are assumed to reset each month, based on the index. You can enter a monthly change in 1-month LIBOR, to view how potentially volatile the tranches can be. Below I assumed a 25 bases point increase each month. This may seem a steep increase, but historically fast changes in overnight rates are not that unusual.
Under this assumption, the floater will cap out and the inverse-floater will hit it’s floor in month 22. If 1-month LIBOR stayed at or above 7.75% after month 22, the chart below would reflect the coupons on each tranche:
You can test your own assumptions with the monthly change in the index.

Sequential Pay CMO

A reader ask me if I had published a sequential pay CMO example. I had not. I did start a spreadsheet at one time, but never completed it. I worked on it over the weekend and came up with a simple, three tranche and residual sequential. I’m going to assume that the readers already knows what a collateralized mortgage obligation (CMO) is, so I won’t be describing it here.

The spreadsheet starts off with a mortgage amortization that represents a pool of mortgages. It is up to the user to describe the mortgage pool (yellow cells are inputs), including assumed prepayments (either CPR or PSA) and a default rate (CDR). I am assuming this pool is guaranteed by an agency of the government, so although the default rate may be used, the Loss Severity remains at zero. Note that the PSA number is not yellow. That is because the input cell is on the summary sheet, so the user can see what happens if the prepayment assumptions change.

Sequential tranches are, as the name implies, tranches that pay the holders of the first sequential tranche interest and principal, until the principal is paid off and then hands off the principal payments to the next tranche that up until that time was paying the holder only interest. When the principal on the second tranche is paid off, the principal payment starts on the third tranche. This goes on until the last sequential tranche. The residual tranche gets the interest from the sequential tranches, left over after the sequential tranches are given their market rates.

We need a yield curve to price each tranche. I used the Treasury curve below:

Then used the average life of each tranche to interpolate a Treasury yield for that average life:


The Summary sheet collects the data needed to price each tranche. Each one is priced using a spread over the Treasury yield. The pool itself was purchased at 1.25% over Treasuries, for a yield of 3.179% and a price of 102.395.

The pool is broken down into each tranche, depending on the percent of par value you allocate to each tranche. Note that the example break down is 40%, 35%, and 25% for each of the three tranches. Each tranche is priced with a coupon equal to the yield spread over Treasuries, at a price of par ($100). First, we will price all three tranches and the residual at the same yield the pool was purchased at, which was 3.179%. If each of the parts yield the same as the whole (pool), there should be no profit. All the way to the right your will see zero profit:

The intent is obviously to purchase the pool and sell the pieces for more than the cost of the pool. If the pieces could be sold at the yields below, the profit would be around $197K.

If you go to the sheet named “Tranche” you will see how each tranche and the residual works.

Download:     http://pistulka.com/Excel_Shared/CMO-Seq.xlsx


Effective Duration, Convexity, and Convexity Adjustment For Loans

This is an update of a post and spreadsheet I wrote in Oct. of 2014 called: “Modified & Effective Duration, Plus Convexity of a Loan”. 

The previous post was hard-wired for a plus or minus 100 basis point change in yields. These new formulas and inputs allow for changes in yield other than 100 basis points:

First, some repetition of the previous post: The formula for Macaulay Duration of an amortizing loan I posted was “an effort to more accurately measure the term of a loan, than just average life. Macaulay duration of course has limited value for amortizing loans because, unless there are hefty prepayment penalties over the entire term of the loan, amortizing loans have embedded options that will change, given a change in yields and prepayments. For the same reason, average life has limited value for amortizing loans, as does half life.

Next we will calculate another duration of limited value with amortizing loans, Modified Duration. Modified Duration is not intended as a measurement of time, as is Macaulay Duration, but an approximation of  price volatility, given a plus or minus 100 basis point change in yields. See my post “Approximating Duration, Modified Duration, and Convexity with Option Free Bonds” for bonds.

The formula for Modified Duration:

Modified Duration =Macaulay Duration/(1+r/n)

Where: r = yield, n = number of payments per year.

Below is the pool structure now used in the updated spreadsheet:

Modified Duration for this structure would be:

5.404/(1+5%/12) = 5.382

Effective Duration is a more accurate measurement of price volatility for loans and MBS, particularly when combined with convexity and the convexity adjustment. As stated earlier, the previous post was hard-wired for plus or minus 100 basis points. These new formulas and inputs allows for changes in yield other than 100 basis points:

Effective Duration = (% price change when yields fall – % price change when yields rise)/2*100

Convexity =  ((price change when yields fall + % price change when yields rise)-2*Initial price)/(Initial price*change in yield^2))/100

Convexity Adjustments = 0.5*Convexity*100*(change in yield)^2

These will be clearer when you down load the spreadsheet.

There is also a table showing that the estimated percentage price change equals the actual price change, using the duration and the convexity adjustment:


There is also a chart showing the percentage change of prices at each change in yield, along with a polynomial curve to fit the points.:



Simplified Linear Interpolation of Treasury Rates

In August of 2014 I showed a spreadsheet for interpolating a yield, given a range of dollar prices. That post was call Interpolation. I thought of another way to use the same linear interpolation. Lets say you want to know what the Treasury yield would be for an investment. Unfortunately, you only have the current Treasury rates from a website showing only seven points along the curve, like below:


The formula below uses two OFFSET functions to find the months before and after the month you are solving for. Then the FORECAST function does the linear interpolation between the two months:


Below, we want the Treasury rate for a mortgage, under various PSA prepayment rates. We enter the average average life and we get back the interpolated Treasury rate for each PSA:

Interp_2Obviously, linearly interpolating the Treasury yield curve with only seven points is not the most accurate way to get Treasury rates, but for quick estimates it might save time.

Download the spreadsheet “Interpolation

Math for amortization rows with CPR, Servicing, CDR, & Loss Severity

First, let me say that this is my 100th post and spreadsheet. I started in August of last year. If this spreadsheet looks a lot like my last post, it is very close. My last spreadsheet showed how to calculate any row of an amortization schedule, including starting balance, scheduled  payments, interest, principal, servicing, prepayment (CPR) and ending balance.

What I left out was the math to calculate the row of an amortization schedule with all of the above, but including CDR (defaults), and the loss severity (the loss on the defaults). The old spreadsheet was called AllFormula.xlsx and the new spreadsheet is called Allformula2.xlsx.

The inputs are the same, except that you need two more pieces of information, CDR and Loss Severity:

allf2_2You don’t need both spreadsheets, Allformula2.xlsx has all the math that Allformula.xlsx has.

Math for amortization rows with CPR and Servicing


After you read this post, check out my updated post


In my post called The Math Behind Excel’s CUMIPMT() & CUMPRINC() Functions I showed how to calculate a single row of a simple amortization schedule, using math instead of the amortization schedule. In this post and spreadsheet I show how to calculate the starting balance, payment, interest, principal, servicing, CPR prepayment, and ending balance with only math. What application does this have in the real world, you ask? I don’t know, but the math was fun.

The inputs are in yellow cells:

AllF1The named cells are next to the inputs. The “Payment#” is the payment number you want to show the balances. Next are two rows of payment number 16. The top one is a simple lookup of the amortization schedule and the bottom one are the formulas only. The top one is just a check against the formulas.


The formulas were easy after the starting and ending balance formulas where worked out.


Download “AllFormula.xlsx





Calculating Historical CPR

There is a newer post that replaces the amortization schedule to calculate historic PSA with a UDF:



Back in December of 2014 I wrote a post and Excel spreadsheet called Reverse Engineering Constant Prepayment Rate (CPR). I called it that because instead of giving a CPR to a model, we could do the reverse and calculate the historic CPR given either the prepayment cash flows or given just the ending balances. I added a couple of things to the sheet called “Given Ending Balance Only“. Initially the sheet showed how to calculate the CPR from issue through a given month. I added another set of ending balances so the monthly CPR’s could be calculated.

Both of these calculations use Excel built in functions. From issue though a given month uses CUMPRINC (cumulative principal) and the monthly calculation uses PPMT (principal payment for a given month). The reason these functions are used is that we need to know what a normal (no CPR) amortization looks like. We use the normal scheduled ending balances to compare with actual ending balances to determine what CPR it would take to get both equal. You can see when you open the spreadsheet what the formulas look like, but here are examples for both calculations:

From Issue Through a Given Month




You will remember from previous posts that the linear calculation for equating CPR and PSA (with its ramp) for the purpose of forecasting prepayments is:

=CPR*100/6*MAX(1,30/Loan Age)

Although this formula works fine for forecasting, it has limited value when it comes to historic calculations. For example, the default data on the spreadsheet shows a new 30 year pool with a WAC of 4.35%.

Calc_cpr4The CPR calculation shows that from issue through the 36th month, the CPR has been 17.27. If we were to use the CPR to PSA linear equation, the PSA would be 287.86.


PSA uses the historic CPR to calculate historic PSA. What is needed is a PSA rate that will get the historic  balance of a the CPR equal to the balance of the PSA calculation, given the same inputs. To do this I used an amortization schedule on sheet “Amortize”. When the green button is clicked, VBA switches to the amortization sheet, uses the Goal Seek Excel function to run the amortization schedule a sufficient number of times until the PSA ending balance matches the historic ending balance at month 36. If values with higher accuracy are required, set the Maximum Change to more decimal places (File, Formulas, Maximum Change). The PSA requires a loan age, unless it is 30 months old or more.


The chart below shows the historic ending balances and the PSA ending balances at 468.60. Both meet at the 36th month.


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




Certificate of Deposit Ladder Optimizer

There are all kinds of calculators you can find to “optimize” your rate of return by laddering certificates. Of course the term optimize is a misnomer because you don’t know what the reinvestment rate will be on the shorter term CDs.  There are some advantages to laddering such as, if you need the funds earlier than expected, you can wait until a maturity, or if you need it quickly, shorter-term certificates tend to have a lower penalty for early withdrawal. Some general assumptions about this workbook:
1. The CDs are compounded daily with a 30.41666 / 365 day count for month and year. I am not using dates, which would be necessary for  actual / actual or actual/365 calculations.

2. The total return will be calculated over 5 years (60 months), regardless of if you pick the 60-month certificate as one of your investments or not. I would recommend that you do pick the 60-month as one of your terms.

3. The light yellow cells are all input cells. Enter the APR not the APY, which is calculated for you assuming daily compounding. Don’t bother filling in the dollar amounts, that will be done when you optimize. The “Terms” are drop down boxes, so you can include any term, out to 60 months.



4. The user of this spreadsheet will need to make a best guess as to what the average reinvestment rate will be over the 5 year holding period. The one guess is used for the average reinvestment rate after each certificate matures.


5. The workbook uses the Excel Solver function to optimize the amounts that should be invested in each maturity. I have run the program on Excel 13 and Excel 10, but it has not been test on any other versions.

You will need to enter the minimum amount you will invest in each maturity and the maximum. Enter the total dollars to invest and then click the green button.


6. You will see something like below after the Solver runs. Pick OK.



7. The table below is only the first 20 months of a total of a 60 month holding period.  These are monthly rates.


8. In addition, you get the optimized APY, the final dollar value, and a chart:


Download “Ladder







Conforming the “Mega” MBS Formula to Street Conventions

See updated formula at:

MBS Math Formula. Servicing, CPR, Payment Delay, Default Rate & Loss Severity


This adjustment to the mega formula for mortgage-backed securities only affects those that use an odd first payment, by entering an amount other than 30, in the “Payment Days Delay” cell. It was pointed out by Win Smith (The Well-Tempered Spreadsheet) that price given yield, using Bloomberg’s MBS calculator, was slightly different than the mega formula. After looking at an actual cash flow from an amortization schedule, it became apparent that the difference was in the assumption of how to treat the first odd payment. When you enter any number of days delayed, other than 30, you create an odd (fractional) payment.

The difference in how the fractional payment is handled is not new. When I was trading bonds, the U.S. Treasury had a different price formula when auctioning bonds and notes with odd first payments, than the “street” convention formula. The question is, should the fractional payment period be included in the compound present value formula, or should it be handled separately with no compounding.

The original mega formula handles the fractional period with no compounding, while the Bloomberg calculator obviously assumes compounding.  An adjustment to the last part of the original mega formula will allow it to match the street convention:

The original mega formula vs. the street convention formula (see red portion)

_p =(100*(((1+_c/12)^(_n*12)*((1-(1-_j/100)^(1/12))+_c/12-_s/12)*(1-((1-(1-(1-_j/100)^(1/12)))/(1+_y/12))^(_n*12))/(_y/12+(1-(1-_j/100)^(1/12)))+(_s/12-(1-(1-_j/100)^(1/12))*(1+_c/12))*(1-(((1-(1-(1-_j/100)^(1/12)))*(1+_c/12))/(1+_y/12))^((_n*12)))/(_y/12+(1-(1-_j/100)^(1/12))+(1-(1-_j/100)^(1/12))*_c/12-_c/12))/((1+_c/12)^(_n*12)-1))* (1+_y/12)/(1+_d/30*_y/12) )
_p =(100*(((1+_c/12)^(_n*12)*((1-(1-_j/100)^(1/12))+_c/12-_s/12)*(1-((1-(1-(1-_j/100)^(1/12)))/(1+_y/12))^(_n*12))/(_y/12+(1-(1-_j/100)^(1/12)))+(_s/12-(1-(1-_j/100)^(1/12))*(1+_c/12))*(1-(((1-(1-(1-_j/100)^(1/12)))*(1+_c/12))/(1+_y/12))^((_n*12)))/(_y/12+(1-(1-_j/100)^(1/12))+(1-(1-_j/100)^(1/12))*_c/12-_c/12))/((1+_c/12)^(_n*12)-1))* (1+_y/12)/(1+_y/12)^(_d/30) )

The difference is not a matter of the right vs wrong way to handle fractional periods. The mathematical purist might argue for no compounding, but you can’t fight the street calculation if you are going to trade MBS.

I have made changes to two workbooks:   Breakdown and Am_vs_Mega



Chained Returns

Calculating the total rate of return on a chain of returns produces a time weighted rate of return. I covered this in my post “Time & Dollar Weighted Rates of Return Calculator“. In this spreadsheet I have segregate the time weighted return into an easier calculator for someone interested in taking their annual, monthly, quarterly, etc. rates of return over time and calculating the annual rate of return for the total period. There are two formulas I have used in the past to do the calculation. One is an array formula and one is not. This spreadsheet uses the non array formula which incorporates the Excel built in function “FVSCHEDULE”.


The calculator is simple. Just add the rates of return in the yellow cells and tell the calculator what times periods the rates are for, using the drop down box. The formula calculates an APY (annual percentage yield) and then calculates the equivalent APR (annual percentage rate). Clicking the green button clears the yellow data cells:

Chain2 Your choices in the dropdown box are:

Chain3 Download “Chain

Rule of 78

If you normally sell your car before it is paid off, you are going to want to stay away from lenders that still use the Rule of 78 (also known as sum-of-the-digits) to calculate the interest you have accrued. This method was used before the technology was available to calculate a prepayment on the spot. At a time when everyone in the U.S. that deals in consumer finance has access to computers and financial calculators, there is no excuse for using this rule today, except to stick it to the borrower. Nationally, it is against the law to use the rule for loans over 61 months (the longer the loan, the bigger the interest penalty). Currently, 33 states still allow the rule (typically auto loans) for 61 months or less.

The alternate name for the Rule of 78, the sum of the digits, gets it name from the need to calculate the sum of the digits for each month of the loan. The sum of the digits for a one year loan, with monthly payments, is 1+2+3+4+5+6+7+8+9+10+11+12 = 78, thus the name. The formula for the sum of the digits is:



12*(12+1)/2 =78

I set up two payment schedules, one for an amortizing loan (30/360 day count) and a loan that uses the Rule of 78. The amounts needed to pay off the loans at the end of each month are calculated, and the two are compared. In all months an early payoff  using the Rule of 78 is higher than the standard amortized loan, with the highest penalty around a third of the way through the loan (chart below). I had to split the two side-by-side loans in half to fit the comparison on this post:



On the Rule of 78 side there is a column called “Total Interest Refund”. That term seems like a positive thing, but it is included only because lenders using this calculation might say you are receiving an interest refund, when it is actually just the part of the unused total interest.

Besides using the amortizing loan as a comparison to the Rule of 78 loan schedule, it has another use. It is necessary to compute the total interest for the loan, to use in the Rule of 78 loan schedule.

The interest for each month on the Rule of 78 schedule is calculated as follows:

Remaining Months / Sum of the Digits * Total Interest

Due to the interest being higher on the Rule of 78 schedule, there is less principal paid down each month. You can see the remaining math, by looking at the Excel spreadsheet. You can change the comparison by entering new loan data in the yellow cells.

Rule of 72 

I added the rule of 72, only because it can come in handy to estimate the time it will take for a certain interest rate to double your investment. I have two table in the workbook. By simply dividing the interest rate into 72, you get the estimated years to double. I also included the a second table for continuous compounding. Next to the estimate for each interest rate is the actual time. The actual time formulas are below the tables.


Download “78

U.S. Treasury Forward Rate Curves

Note: I have made some changes to the spreadsheet since the original post. The Treasury data source was not pulling in the new rates as I had expected, so I hard wired a table of rates for February of 2016. A link to the Treasury page is still there, so you can manually update the data with a copy/paste into the table.  

Back in September of last year I wrote three posts and spreadsheets about Gap Analysis, Spot Rates, and Forward Rates. I put all three together in this post and Excel workbook. I have connected the Treasury’s daily Treasury yield curve data web page on the sheet called “Market Data”. The default data for this site is a yield curve for each day of the month: 


The next sheet is called “Spot Rates”, where I use the bootstrapping method to calculate the spot rate curve, from the current yield curve. I used nine Treasury yields from the database, ranging from the 6-month bill to the 30-year bond. I interpolated the remaining incremental semiannual periods between these given yields. Below is a small part of a much larger sheet that calculates the spot rates.  The sheet automatically populates the curve and spot rates for the date you choose.


The next sheet creates a table of forward rates. Below is a partial image of the table. Two things to point out. The top row (Years From Now) are drop down boxes. You can change any year in any cell on row four, but keep them in ascending order so that you get an actual yield curve. The NA() cells are due to GAP analysis. In order to calculate a forward rate with GAP, you need a current rate long enough to calculate the shorter years (i.e. we can’t calculate the forward 30-year rate because we don’t have any rate longer. Other forward rate tables you might have seen, such as Bloomberg’s, might have rates for longer maturities. I believe they use some kind of forecasting method for those years, which I chose not to do here. I needed the #n/a rather than blanks to do the charts.


The red cell you see is the answer to the entries below:

t_Curve5 The chart sheet shows the current yield curve and any forward curve you pick from the yellow drop down cell (D9). This shows the current curve (blue) compared with the forward curve in 3 years (orange):



This chart shows the change in rates from the current rate to the forward rate, in this case, 3 years from now:


Download Excel workbook: “Forward_Curves.xlsx”


Treasury Bill Calculator

It occurred to me that my last post Treasury Bill Math with twelve formulas, has little value if you are sitting on a trading desk and need to work quickly. I put together this calculator that will calculate two of the four pieces of information, given two pieces. It looks like this:


The page is protected. The the yellow entry cells are not. You will get a reminder if you enter more than two entries. There is no password, so it can be unprotected. I used the same named letters that I used in Treasury Bill Math . I concatenated the named letters and created a lookup table to find the results I needed:


For example, the data I entered is the discount rate (i) and the money market rate (y). They concatenate to iy and the letters across the top are used in the lookup.

Download “T-Bills2

U.S. Treasury Bill Math

Money market securities mature in less than a year and pay simple interest (as opposed to compound interest). Treasury bills are money market instruments that pay simple interest, but are quoted (sold) based on a discount rate. Securities that use a discount rate can be thought of as paying the interest upfront, as opposed to paying interest at maturity. Paying interest upfront has an advantage over paying at maturity due to the time value of money  which I touched on in the post of the same name. Don’t think the Treasury is doing you any favors however, by paying upfront interest. The discount rate is lower than the money market rate and so is the interest paid. Below is an example of a 241 day bill, quoted at a 5.0% discount rate. The bottom boxes show that the dollar amount paid for $1 million in face value is $966,527.78, which means the interest earned upfront is $33,472.22. The investor gets back the $1 million face value at maturity.

Converting the discount rate to a money market rate (all the formulas are further down this post) we get a 5.17316% money market equivalent rate, which would pay $34,631.41 in interest if paid at maturity. The money market rates of return are both 5.17316%.

Money Market Rate of Return = (ending value / starting value)*360/241


Below are various math formulas to equate discount rates with money market rates. The yellow cells are input cells and the red letters are named values.



In addition are the formulas for calculating the Bond Equivalent Yield (BEY). There are two formulas. The first is the BEY for bills less than or equal to 182 days to maturity. The second formula is for bills over 182 days to maturity. Why two formulas? The first simply converts the day count from actual/360 to actual/365, which Treasury bonds use. The second formula is more complicated due to the bond being compared will have a single coupon payment, which requires reinvesting (compound interest).

t-bills3Note that the yellow cell has a drop down box to enter either 365 or 366, depending upon if you are in a leap year.

Download spreadsheet “T-Bills.xlsx

Financial Ratios – Bullet Charts

In May of this year I offered a template of gages that a board of directors might find useful to spot problems in their credit union or small bank. Read the post Financial Ratio Gauges for more information on why I wrote the financial gages spreadsheet. The gages looked like this:


I have just put together another spreadsheet that does the same thing, but may or may not be easier to use. It is really a matter of taste and which one you think is easiest to understand. Like the gages, the bullet charts are colored green for good or red for a potential problem. The bullet charts have a little more room between red and green, with a yellow neutral. This is what they look like:


The inputs are much the same as the gages inputs. The charts need to be updated (push a button) each time the Chart Range is changed. One other difference is that with the gauges you pushed a button to reverse the red and green. In the bullet charts the colors don’t flip, but the chart range changes with a TRUE or FALSE drop down box, and the update button:

Bullet2 Please read and compare the two spreadsheets. As usual I spent the last few days putting this spreadsheet together, with no actual testing. If you find errors, please let me know.

Spreadsheet “Bullet