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.

Prior to this post and spreadsheet, I had two other posts on defeasance. The other two are Defeasance and Defeasance 2.0. The first spreadsheet ( Defeasance) used the Treasury strip market. The idea was that strips were preferable to Treasury securities with coupons, because they represent spot rates, with no reinvestment risk. The problem with the Treasury strip market was that the rates had to be copied and pasted into the spreadsheet from the Wall Street Journal each time an estimated prepayment penalty was required.

The next spreadsheet (Defeasance 2.0) used the “on the run” Treasury market rates from Yahoo. The rates can be easily pulled into Excel as a data table and refreshed at any time. These rates where interpolated for each month from month one to month 360. This method seemed to give a reasonably good estimate of the cost of prepaying a loan. The problem is that Yahoo does not always update their yields, or the table is not available at all.

This spreadsheet (Defeasance 3.0) gives the user three sources for Treasury yields, used to calculate defeasance. They are The U.S. Treasury (yields from the previous day), Google Finance, and Yahoo Finance.

Go to the sheet “Pick A Source” and pick from yellow drop down cell.

Click the green refresh button, then go to a loan type sheet and enter loan data.

Download: Defeasance 3.0

If you search my blog, you will find dozens of amortization schedules produced by formulas. So why would you need an amortization produced with VBA? Every time you change any inputs, you have recalculate the schedule, while all my other amortizations are automatic.

I created this spreadsheet years ago, just to practice my VBA. Even though my VBA is not very pretty, someone might have a reason to use VBA for their amortizations, or you might just want to see what the VBA looks like.

The input cells are yellow:

Don’t’ forget to click the red “Calculate” button each time you change an input cell. The amortization includes both Servicing and CPR.

A yearly schedule is included to the left of the inputs:

Down load spreadsheet: Mortgage_VBA

This is not an academic or financial post, but a fun look at the large number of possible paths available in the binomial lattice used in certain types of financial models. Although there is a small model in the accompanying spreadsheet, this post is mostly concerned with calculating the number of paths and the probability of randomly choosing a particular path. One way to count the paths is to start with Pascal’s Triangle. Pascal’s Triangle is defined as a triangular array of binomial coefficients. Below is Pascal’s triangle from rows zero to 10:

Spreadsheet – Lattice          Sheet – “Pascal’s 10X10 Vertical”

To construct the triangle, start by entering the number one in the cell at the top (zero) row. Then enter the number 1, one cell to the left and one down. Continue this down to the last row. Then do the same on the right hand side. Then, wherever there is a cell inside the triangle with numbers to the upper left and upper right cells, add those two numbers together in that cell.

Pascal’s Triangle has a number of interesting mathematical uses, however for purposes this post we will only be concerned with combinations. Take a look at the 6th row and the 5th column and you will see the number 20. The number 20 tells you that starting from the top of the triangle and moving either left or right, and only down, there are 20 paths that can be taken to get to that cell. That is true of any cell in the triangle with a number.

Sheet – “Pascal’s 10X10 Horizontal”

This is the 10X10 triangle, turned Horizontally. Instead of the paths going from top to bottom, this version goes from left to right. This is the way many lattice models look. It also makes it easier for me to add descriptions and analytics to the triangle on the right hand side. You will notice that this triangle only has the number of paths in the 10th row. It shows that you don’t need to calculate each row’s values before the last row. It can be done mathematically, however Excel provides an easier way. The function  =COMBIN(Number,Number_chosen). For example, to get the center column at row 10 (number 252), enter =COMBIN(10,5). Remember that the triangle is turned on it’s side so 10 is the row and 5 is the column.

Now we will expand the triangle to 60 rows and 120 columns (see chart below). Obviously there are many more paths to be taken to get to row 60. The green and red cells represent a path that is derived from starting with the top cell and randomly choosing if the next cell going down  is going right or left. The letter “D” means go to the left, and “U” means go to the right. There is a 50/50 chance of going either direction. Each time you push the F9 key it calculates a new path. You can push the F9 key all day long and never get a path that goes all the way down the left or right had side of the triangle. We will see why later in the post.

Sheet – “Vertical 60 Rows”

The triangle below has the same 60 rows as the one above, but turned on its side.  It makes it easier to show the very large number of paths and probabilities.

Sheet -“Vertical 60 Rows”

Now we get to the subject of the title of this post. Below is the data for the above chart that is on the right hand side in the spreadsheet, enlarged to see the paths and probabilities. Remember when I said you could push F9 all day and never get the path all the way to the left or right of the triangle? That is because there is only one path on each side of the triangle. The probability of getting one of the two path on the sides of the triangle is .000000000000000008674% or 1 in 1,152,921,504,606,850,000.

Think of a 60 row triangle as rolling over a one month investment (CD, Treasury bill, etc.) for five years. Each month the investment rolls over and has a 50/50 chance of rolling into higher or lower rate.  The number of paths that could get you to the center cell after rolling over each month for five years is 118,264,581,564,861,000 with a 10.26% chance of that happening. The number of paths and the probability of ending up on or near the center of the triangle is extremely high. The total number of paths to all cells at row 60 is 1.15 quintillion.

Sheet “Horizontal 60 Months”

This is a small model that shows the average rate of rolling over a one month investment each month for five years. This model uses random paths by pushing the F9 key. The triangle is horizontal and zooms in on the center path. There are two yellow input cells on this sheet. The increment cell at the top has the change in rate at each rollover (I used 5 basis points). The other yellow input cell is the starting interest rate (I used 5.00%). The number in the orange cell is the average interest rate the path landed on over the 5 years.

This is not meant to be a sophisticated model, but a fun game that shows the extreme amount of paths that are possible to take in a random up or down, 50/50 chance, 60 row Pascal’s Triangle.

p.s.

I also threw in a sheet that shows a 360 row triangle (think of a monthly mortgage for 30 years). The possible number of paths for a 360 row triangle is 2.35E+108, or 2.35 with 108 zeros to the right.

Download Spreadsheet: Lattice

James was searching the web, when he ran across my Taxes workbook and the sheet called Required Minimum Distribution (RMD). He was looking for an Excel spreadsheet that calculated the net balance each year on tax differed accounts (IRR, 401k, etc.) after the return on investments and the withdrawals due to RMD.

Rather than starting over, I added the table below on the same RMD sheet. The table calculates the balances of your accounts, out to age of 100 (good luck). The example below is truncated and show only to the age of 84.

Post: Required Minimum Distribution (RMD)

Workbook: Taxes

A reader came across my post called  Loan Pool Named Formulas, Without An Amortization Schedule . This post and spreadsheet showed how, by using mathematical formulas, any month in a mortgaged backed security amortization schedule can be calculated, without creating the whole amortization schedule.

The reader asked if the formulas could be adjusted to calculate the interest on a loan for any given month, but using the actual days between monthly payments to calculate interest. Most commercial loans use this method to calculate interest. The only way I could see to accomplish this was to use VBA in the form of a UDF (user defined function).

My UDF looks like this:

The UDF uses codes to determine what information is needed:

In the above example, the user wants to know what the interest will be on 3/1/2025 (or the 119th monthly payment). The “Days in Base Years” is used to determine if the interest calculation assumes a 360 or 365 day year.

The answer appears below:

As a proof, an actual amortization schedule is used to compare the results:

Download spreadsheet “Daily_Interest”

First a Warning!!

To use this spreadsheet, you need excel 2013 or higher, due to the use of table slicers (not to be confused with pivot table slicers).

Internal Audit (IA) frequently reports to the board or supervisory committee. Typically, these groups are concerned with the areas of the firm that have the highest risk to the credit union. At the same time, they need to know the likelihood of these high-risk areas having a major problem. If the high-risk areas are well managed, the likelihood of a major problem is reduced. This spreadsheet can provide IA with an interactive dashboard, that can be used to display the degree of risk and at the same time, the likelihood of a problem developing for each department. There is also a third dimension that the board or committee might be concerned with, and that is how much time IA has allocated to review each department.

To accomplish this, the spreadsheet uses a 3D bubble, X,Y scatter chart. Risk is represented along the horizontal axis and the likelihood of a problem is along the vertical axis. The chart is divided into quarters. The upper right hand quarter contains the departments with the highest risk and the highest likelihoods of a problem existing. Likewise, the departments with the lowest risk and the lowest likelihood are in the lower left hand quarter. The size of the each bubble shows the relative time (in days) IA expects  spending on reviewing each area.

I assume that the user of this spreadsheet has some basic knowledge on how to use splicers. If not, do a Google search and watch some videos on splicers. There are five splicers on the Bubble Chart sheet. The risk is at the top of the chart (horizon)  with degrees of risk 1 through 10. How you determine the risk level for each department is up to you. Along the left hand side of the chart is the Likelihood splicer. This splicer is 10% to 90%. Over on the right hand side of the chart is the Days to Complete splicer (in blue). The more expected days to complete the work on a department, the larger the bubble. In this example chart, GL (general ledger) is the largest bubble with an estimated 7 days to complete. To the right of that are two more slicers for the abbreviations of each department that are shown on chart and the corresponding full name of the department.

If we want to show just the departments that are in the upper right hand quarter (highest risk and likelihood of a problem) click on the Risk splicer the number five and while holding the Shift key, click the number 10. Now on the Likelihood splicer click the .5 number and while holding down the Shift key, select the number .9.

The chart should like the above. Due to some large bubbles, it is difficult to see the abbreviations on each bubble. On the right hand side of the sheet however, you can see the abbreviations and names of the seven most risky departments with the highest Likelihoods of a problem.

To look at just Commercial Lending click the name on the far right hand side and the chart will look like this:

You see where Commercial Lending is on the chart and that it will take 5 days to complete the review. If the slicers get stuck, click the Rest Database button.

Other areas a chart like this might be useful would be in investments (risk vs return) or asset liability management.

 Download:  Audit_Bubble

For those that follow my blog, you know that I have used amortization schedules extensively in my analysis. This post and spreadsheet takes a closer look at the default and loss severity assumptions. In previous spreadsheets, the defaults and loss severity have taken place in the same month. In other words, there has been no delay in recovering the net portion of the default principal. When using this type of analysis as a pricing tool for a pool of mortgages, a delay in recovery of the defaults can make a significant change in the price you might what to pay for a pool of whole loans, or when analyzing a pool in your own portfolio.

As you might recall, pricing a pool of mortgages using an amortization schedule,  requires taking the present value of the various cash flows produced by the pool. Let’s assume that we are interested in purchasing a pool of whole loans with servicing retained by the seller. The inputs cells (yellow) might look like this:

 If you are not familiar with the inputs, take a look at some of my past posts. The new entry in the red circle is an estimate of how long it will take for the defaulted properties to be repossessed, fixed up, and sold. Depending on which state the property is located, it could take three to six months, just to take the property back.

Comparing the present value of the cash flows at a discount rate of 4.75%, first without considering the delay:

And then assuming a six month delay:

The delay in this case would reduce the value of the pool by approximately $66K.

The method I used on the spreadsheet was to calculate the net return after losses in a separate column, delayed by the number of months entered. Then use this column to calculate the present value of defaults, less losses. I used the formula below to capture the cash flows, in this case four months prior:

=IF(K19<=$E$14,0,INDEX(($H$19:$H$378-$I$19:$I$378),ROW()-$E$14-18,0))

I used the index function to find the proper row (row one will always be the 19th row). So we subtract the losses from the default for each month ($H$19:$H$378-$I$19:$I$378), starting with the current row (row()), minus the number of months delayed, minus 18. If the current month is <= the months delayed, the recovery for that month is zero.

The first few rows look like this:Download Spreadsheet “DelayRecovery.xlsx“

First a warning. To use the linked spreadsheet, you need Excel 2013 or higher, due to the use of table slicers (not to be confused with pivot table slicers).

This spreadsheet is an Excel dashboard that can be used to analyze U.S. Gross Domestic Product. The percentage change in GDP from the preceding period is the headline number most widely reported. The raw data is a download from the Bureau of Economic Analysis. The link is on the “Data” sheet and the download you need is “Percent change from preceding period” as shown below:

Download the data into a clean sheet and then copy/paste the “GDP percentage change based on chained 2009 dollars”. Each time you update the data with a new quarter, copy/paste the whole series from 1947q2 to the end. Paste only the two columns, dates and GDP. If you only type in the new quarter and date, revisions could have taken place and you may not have all the correct data.

Pasting the new data starts at cell B10. Row 10 may not be visible. To make it visible, go the chart sheet and click this symbol in the upper right hand corner of the slicer. This will select all data years and make cell B10 on the data sheet visible:

There is also a link to the recession periods, when and if you need to update them. Recessions need to be typed in. Recession periods are represented by a “1” and non-recession periods are “0”.

The chart sheet shows the “Year” slicer with the last eight years selected. If you are not familiar with slicers, choose the period by picking either the first or last year in the period you are interested in. Then while holding down the shift key, pick the other year in the period. All other dates should be inclusive, as below:

The chart shows the quarterly GDP changes for those years.

The gray background shows recessions. Showing recessions, data labels, and a moving average can be toggled on and off. The yellow input cell is the number of quarters in the moving average.

I have compared the last eight years to all quarterly growth rates since 1947, showing various data:

Be sure to click the blue refresh button, if the data has been changed. The yellow input cells are used to adjust the output data on either side.

At this point, someone might conclude that the first two quarters in 2009 where still part of the recession, and should not be included in the recovery data. In that case go to the second slider called Quarters. All of the quarters in the years chosen are shown in red.

To take the first two quarters of 2009 off the chart and out of the comparison data, click the quarters to be eliminated:

The chart and the comparison data are then corrected for these quarters:

There is also another chart. Below the cluster column chart is a histogram of the distribution of quarterly changes.

Download “GDP“

I was recently asked to provide a spreadsheet that would calculate the add-on interest rate (APR) that would allow the originator of a mortgage to pay for the origination expenses (title, escrow, etc.) of the borrower. Typically, the borrower would just add the expenses to the loan balance, but there may be reasons why the borrower does not want to increase his/her debt load.

This approach can obviously be risky to the originator, if the right prepayments are not put in place. The linked spreadsheet allows for numerous combinations for a fixed rate loan. The default example is a $450,000 mortgage that is originated at 4.50% to be amortized over 30 years. The total expenses the borrower wants the originator to finance, by increasing the APR, is $6,285.

The originator decides to give the borrower a 30-year loan, with a prepayment period of 60 months (5 Years). At or after 5 years, the borrower can refinance or sell the property without a prepayment penalty. It is important that the loan agreement states that any reduction in principal value, including additional payments, will trigger a prepayment penalty over the prepayment period. The calculation shows that increasing the APR from 4.50% to 4.824% will allow the lender recover the $6,285, plus earn 4.50% interest on the expenses. This is an increase of 32.4 basis points. It should be noted that the calculated new rate is actually carried out more decimal places and not rounded for purposes of this spreadsheet.

In addition, the monthly prepayment penalty is calculated. The example shows the prepayment on month 40 would be $2,204.12:

In addition, a proof on this input sheet shows that the repayment of the loan, including the prepayment penalty at month 40, will have returned enough over the 40 months to equate, on a present value basis, the original $450,000 in principal, plus the $6,285 in expenses, discounted at the original loan rate of 4.50%:

There are two additional proofs on the “Calcs and Proofs” sheet.

After recouping the expenses with the higher interest rate, you might want to include in the loan agreement that the interest rate will revert back to the original market rate (in the case of this example 4.50%).

This spreadsheet has not been tested in the real world. If you find problems let me know.

Download:  RateAddOn.xlsx

A few weeks ago, I wrote a post called “Valuing a Mortgage Portfolio With Cash Flow Analysis” along with a spreadsheet using VBA to calculate the cash flows of a loan portfolio. Ideally, if an amortization schedule for each loan could be created and the cash flow matched, this would be a proof that the VBA cash flows are correct. The example portfolio that I sent out with the original post however, had 800 loans. So I built another spreadsheet that takes the same portfolio information and creates 800 amortization schedules (or as many as your system can handle) and the cash flow is exactly the same as the VBA cash flows. The file was so large however, that it was not practical to allow downloading it.

Instead, for this post, I used the first 20 loans in the previous post and compared the two spreadsheets. If you have already downloaded the VBA spreadsheet, do it again. I tweaked a penny rounding problem. If you want to copy/paste all 800 loans, go ahead.

If you want to use this spreadsheet to create your own amortization schedules, do the following:

After the new loan data are entered, clear any loans that have already been created by clicking . Then enter your loans and  create your amortization schedules by clicking the  button. If you do add all 800 loans and then create 800 amortization schedules, it will take awhile to create them all. That is why the VBA spreadsheet is preferable, in that it takes only a few second to calculate 800 loans.

However, there are a couple of advantages in using this spreadsheet. First, all of the loan data are linked to the individual amortization schedules. Therefore, if you change any input data you don’t have to run the amortizations again. The amortization schedule is instantly recalculated. Also, each amortization sheet is named with its loan number. Next, if you take a look at the loan numbers (under the column header “Codes”), each is hyperlinked to that amortization schedule. You can click the loan number and go directly to that amortization. Each amortization schedule also has a button  that returns you to the data input sheet.

This is the amortization input sheet:

Next is the VBA version, with the same 20 loans. Note that there is one balloon and two interest only loans, just to test those assumption also.

The results are exactly the same, an IRR of 3.4082% and each cash flow (I cut it off at the 20th cash flow) is also exactly the same.

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

You can tell that I am running out of ideas to create new spreadsheets. I’m back to creating tax calculators. This one is concerned with the required minimum distribution (RMD) that everyone with an IRA or 401K will some day need to use. When you reach 70 1/2 (not 70 years old but 70 1/2 years old) the IRS requires you to start taking out a portion of your retirement savings that were tax deductible when you invested the funds. I don’t expect a lot of excitement over this entry. After all, how many 70 year olds are sitting around waiting for an Excel spreadsheet in their retirement, or even have Excel.

The RMD tables and calculations are simple. The factor needed comes off one IRS table, unless you have a spouse that is more than 10 years younger than you and is the sole beneficiary. Then the factor you need comes off a different table. The first year you must start the RMD can be confusing, because of the 70 1/2 year old rule. If your birthday is before July 1st of the year you turn 70, your have to start the RMD that year. If you were born after June 30th, you can wait until the next tax year.

All you need for input to this calculator is the value of all your IRAs and 401Ks on the last day of the previous year, your birthday, and the birthday of your spouse if he/she is more than 10 years younger than you and the sole beneficiary. I use the spouse’s birthday just to make sure of the 10 year difference. You don’t need your spouse’s birthday unless you answer YES to both the 10 years younger and beneficiary questions. If you do, a new box appears asking for the spouse’s birthday. After downloading the spreadsheet, select the RMD sheet:

The spreadsheet opens to a big disclaimer sheet. I am not a tax guru. I do my taxes with Turbo Tax. Always consult with your tax consultant on any of my calculators.

Download Taxes

I added another calculator to the Taxes spreadsheet. The sheet is call “Withdrawals”. It is a simple calculator, but someone might find it helpful. I was asked by a friend (over 59 years old) how much he would need to withdraw from his IRA to pay for his monthly rent, net of taxes. So I decided to make a calculator.

The formula is simple,

Withdrawal = Amount Required/(1-Effective Marginal Rate)

but you do need to have your marginal tax brackets. This is usually a guess for the current year compared to last year, because there may be changes in your income and deductions or changes in the tax code. You need to know if nondeductible contributions were made to the account, and how much. Also, if there were nondeductible contributions, you need to know the market value of the account at the end of the previous year.

It is also important to know that if you don’t itemize on your tax return, you won’t get the benefit of deducting state and local taxes from federal taxes, which could increase the amount of taxes you will pay.

As usual in my spreadsheets, the light yellow cells are the only input cells. The rest should be self-explanatory:

Download Taxes

This Excel spreadsheet could help small to medium size banks, credit unions, and mortgage investors, to evaluate their mortgage portfolio. It could also be used for commercial and consumer loans as long as the user understands that the model uses a 30/360 mortgage day count.

The difference between valuing a portfolio of mortgages and mortgage backed securities (MBS) is that MBS contain a pool of mortgages with the same or close to the same interest rates and maturities.  That way the average weighted coupon and maturity can be entered into an internal rate of return calculator (see Market Price given yield or Market Yield given price) with  a high degree of assurance. In the case of a portfolio however, some of the loans can have rates and maturities spread over a wide range. A simple average weighted two factor rate

=SUMPRODUCT(Rate ,Balance)/SUM(Balance)

doesn’t take into consideration that some of the shortest maturities might have the highest rates, or the lower balances might have lower rates, or some combination that reduces the reliability of the average rate. This can be overcome somewhat with the use of a three factor average weighted rate, using the additional factor of term:

=SUMPRODUCT(Rate,Balance,Term)/SUMPRODUCT(Balance,Term)

This increases the accuracy of the rate somewhat, however I personally prefer having the internal rate of return of the actual cash flows of the entire portfolio.  In addition, the three factor formula does not consider different Constant Prepayments (CPR), defaults (CDR), loss severity, balloon payments, and periods of interest only.

The inputs to the spreadsheet are in yellow cells and look like this:

 I included 800 loans using the randbetween() function for rate (APR), term, and amount. Theoretically, over a million loans could be entered using Excel 2007 – 2016, but the VBA subroutine will get slower the more loans you use. The 800 loans, with terms as long as 360 months, takes about 10 seconds to calculate the total cash flow for all loans, using my computer. That includes having other programs running. A portfolio of 800 shorter term loans (less than 84 months) took about two to three seconds. Your results will vary.

Remember that neither a balloon or interest only month can be longer or equal to the term. If you enter a longer or equal month for either one, conditional formatting will change the row to a dark red fill.

To the right of the input data are the cash flow results, with the internal rate of return. In this example 3.3826%. This translates to a dollar price of $100.  The green cells with cash flow will be filled until the last payment of the longest maturity. In this case, 360 rows.

After changes are made to the yellow input cells, click the “Clear” button to clear the cash flows and then “Run” button to calculate the cash flow.

The 3.3826% internal rate of return in this example, is based on the total current balance of the portfolio and, as I mentioned before, would be expressed as a price of par ($100). Even though you might have entered risk factors (defaults and loss severity) for each loan, market rates may have change. You can enter the days delayed (default is 30) and the current market yield, to get a market price. This would be particularly helpful if you were looking to purchase or sell this portfolio.

Download “vba_many_loans“

I have made adjustments to the MBS mega formula that calculates the price of a mortgage-backed security. It now allows for default rates (CDR) and loss severity.

In the past I have made other adjustments and I have included a review of these adjustments in the spreadsheet linked below.  I’m not sure who will use this new formula, since I have published a number of spreadsheets that allow for Default Rate (CDR) and Loss Severity when calculating the price of Mortgage-Backed Securities. They include both VBA functions and amortization schedules.

A math formula allows for the calculation of an MBS price in one Excel cell, however the formula is much larger than the original mega formula:

Given:

Download “TotalMega“

In 2014 I wrote a post and Excel workbook called Tax Calculators, in which I described some of the calculations I had run across during my career. One of the sheets was a taxable equivalent sheet. The sheet was full of tables describing various equations for different equivalent rates. The sheet was bloated and even I had trouble looking for the right calculation. I ran across a calculator by Jonathan Ping, that was much simpler and easier to understand. Also, my calculations all assumed the reader itemized their tax returns and Jonathan’s gave the option for not itemizing. I replaced the old sheet with a new calculator. The new calculator looks like this:

Download: Taxes