# Future Value Bank Interest in Leap Years

Not all banks treat leap years the same, when calculating interest on deposits. Many financial institutions use basically the same method (or a close variation) I used in this formula. Check with your bank before relying on this method.

The method I am referring to is:

Interest is compounded daily. The balance on any given day is a combination of the previous days balance and the interest earned on the previous days balance. The rate used to calculate the previous days interest is the APR divided 365, assuming the previous day fell in a normal year, and 366 if the previous day fell in a leap year. This process continues until the funds are withdrawn. This means every day within a leap year earns 366th of the stated APR. This makes sense, as long as the funds are held for the full year, and receive the interest for the extra day, February 29th.

Now to the formula used to count the number of leap year days and non leap days between two dates. Download the workbook here. The formula I came up with uses an array, given the first and last date, plus the APY. Remember to enter the formula with a Ctrl-Shift-Enter to create the array.

={PRODUCT(1+(365*((1+C10)^(1/365))-365)/((–(MONTH(DATE(YEAR(ROW(INDIRECT(C8&”:”&(C9-1)))),2,29)))=2)+365))*C12}

The first step in the formula is to convert the APY to an APR. The APY in this case is in cell C10.

APR = 365*((1+APY)^(1/365))-365

Next, a formula to determining which dates are within a leap year:

=MONTH(DATE(YEAR(C9),2,29))=2

Cell C9 contains a date. After the YEAR function determines the year of the date, the DATE function combines the month, day, and year to look like this: DATE(2012,2,29) or 2/29/2012. The MONTH function of this date is a 2 so the formula will return 2=2 or TRUE. If the DATE function was to evaluate this DATE(2013,2,29) it would not recognize 2/29/2013 as a date, but would evaluate it as 3/1/2013 instead. Then, the MONTH function returns 3, which makes the formula 3=2, which is FALSE. So, this formula results in either a TRUE if a leap year day or FALSE if not.

The cell references C8 and C9 are the starting and ending dates.  Excel recognizes the dates as  numbers. The & signs combine the two date numbers, with the “:” separating the two. This appears to be a range, surrounded by two apostrophes or a text string. The INDIRECT function returns the reference specified by a text string. Then the ROW function will interpret the range as row numbers. The row numbers result in an array of date numbers, so we can now add the leap year formula to evaluate each date for leap year days. Notice that I subtracted 1 from the last date (cell C9). When calculating interest, the last day does not counted. The last date does not earn interest.

The array goes through each date to determine which are leap days. Each date results in a TRUE for a leap day and a FALSE for a non-leap day. Next, the double dash marks (–) convert each TRUE to the number 1 and FALSE to the number 0. Now I add the number 365 to each number 1 or 2 to get a 366 for leap days and a 365 for non-leap days.

Finally, the APR is divided by each day (either 366 or 365) and 1 is added to each. The results are then multiplied by each other using the PRODUCT function. Then multiplied by the present value (\$1,000,000 in my example).

Use the Evaluate Formula option to step through the formula.

The on the “Proof” sheet I have manually constructed a number of rows, representing each date, along with column B to determine if each date is a leap year or not. Column E  is a running total of interest. Cell G6 is linked to the formula to compare the results in cell E3.

Romans 1:17, “For in it the righteousness of God is revealed from faith for faith, as it is written, ‘The righteous shall live by faith.’”

# Example of using the Future Value Formula on a Commercial Loan

My first post on this blog was in August of 2014 and it was called: No XNFV Excel Function? The idea was a formula for calculating future value with dates. There was a XNPV function for present value, but no function for future value. My formula looks like this:

=SUMPRODUCT((1+APY)^((MAX(Dates)-Dates)/365)*Data)

Of course there are other workarounds, such as:

=FV(APY,(MAX(Dates)-MIN(Dates))/365,,-XNPV(APY,Data,Dates))

Fictitious Example: Your firm, First National Bank, is the lead underwriter and servicer of a \$50 million commercial loan. The property is a large apartment complex. The loan is participated out to four other commercial lenders. The combined group of lenders is called The 325 Commercial Development Group. Soon after the loan settled, the borrower stopped making payments. It was learned that the property was run down and the local municipal public works department was threatening to condemn the property. Obviously, there was deception on the part of various persons, to conceal the actual condition of the property. Since the participation agreement stated that all participants were obligated to do their own due diligence on the condition of the property, all five members of the 325 Commercial Development Group are responsible for their portion of the loan.

The 325 Commercial Development Group takes position of the property and sues the commercial broker, property manager, the inspection firm and various other players for a fixed amount. As the servicer for the loan, your firm pays all expenses and collects reimbursements on the property while the lawsuit proceeds. These net expenses will be applied on a pro rata basis to members of the group at the end of the lawsuit.

The 325 Commercial Development Group wins the lawsuit for a set amount. Your firm has paid out a large amount for expenses over the three years to clean up and repair the property . Your job is to calculate how much interest has accumulated on these net expenses, and allocate the interest and expenses to all members of the group. The final distribution of proceeds from the lawsuit including net expenses, and proceeds from the sale of the property, is made on 2/15/19.

There are three years of expense and credits, the first dozen or so look like this:

The interest rate is entered on the data sheet:

# Effective Duration & Convexity of a Loan Pool Using My Mega Formula

I have posted previous calculations of effective duration and convexity for a pool of mortgage loans, however past spreadsheets required three amortization schedules, on separate sheets. Using the mega formula for pricing, this spreadsheet (in my opinion) is cleaner and easier to understand.

Effective duration is used to analyze mortgage pools, rather then Macaulay or Modified durations, due to the options embedded in mortgage loans. The borrower has the option to put the loan back to the lender. Typically, the duration calculation assumes a plus or minus 100 basis point change in interest rates. Any factor that will change the cash flows of a pool, when rates are changed, needs to be considered when calculating effective duration.

The factors that will affect the initial cash flows are entered as follows:

Now comes the factors that will change the cash flows of the pool when interest rates move plus or minus the amount entered above (100 basis points or 1.0%):

The assumptions of factors that will change, given the change in interest rates, are entered in yellow cells. These entries require either an historical bases, or the experienced users best guess as to how the factors might change.

Then the calculations of effective duration and convexity are made, along with the convexity adjustment. The adjustment is needed because of the non-liner relationship between prices and yields.

Lastly, I included a chart, showing the negative convexity of the mortgage pool. Negative meaning that when interest rates fall, prices will rise less than prices will fall, when rates rise the same amount.

# Mortgage Loan Pool Pricing Table with CPR, CDR, & Loss Severity

Before I added default and loss severity to the mega formula for the price of a mortgage pool, I produced a price/yield table. That old post was http://pistulka.com/Other/?p=707 This post and Excel workbook includes the added variables. As is true with all of my spreadsheets, only the yellow cells are input cells:

* Servicing – If servicing is retained by the seller, enter the servicing fee.
If the buyer intends to service the loans, enter zero.

* Market Yield – The discount rate used to calculate the net present value (price) of the cash flows produced by the pool of loans, based on the various assumptions.

*Days Delay (default 30 days) – Mortgage loans pay interest in arrears, meaning that the payments are typically made at the end of 30 days. Servicers will routinely pass through the payments to the mortage holder after the actual payment date, commonly due to a grace period. Depending on the sale agreement, the servicer will collect the payements and pass them on to the mortgage holder on a specific date. For example, the agreement might state that the cash payments will be sent to the buyer on the 15th of of the next month.
That delay in payments will reduce the return to the loan holder, by delaying the reinvestment of the payments. To compensate for this loss, the price is reduced the approperiate amount, depending on the discount rate (market yield). The Days Delay input would then be entered, using the example above of the 15th day of the next month, by entering 44. That is the normal 30 days plus 14 additial days (15 -1). Some MBS producers, for example, might have payment delays for as long as 75 days.

The average life is important, because although prices will vary with the same market yield, the average time you will get that yield could make a big difference. For example, take the top row of the table with an 8.00% market yield. The price ranges between 92.842 and 84.317 for the same 8.00% yield. The average life at 92.842 (using the assumptions provided) is 3.88 years and 6.72 years for a price of 84.317, using another set of assumptions.

# A Fourth Way To Bootstrap Spot Rates

In May of 2015 I published a post called “3 -Ways to Bootstrap Spot Rates for the Treasury Yield Curve”. There was also an Excel workbook linked to that post. The workbook link is:

http://pistulka.com/Excel_Shared/3_Spot_Rates.xlsm

Rather than posting a new workbook, I am leaving the old workbook, but I added a new sheet called “Fourth Method”. As I was looking through some of my old Excel workbooks, I came across this method for calculating spot rates, that I should have used in the original post.

For an explanation of the first three bootstrap methods, see my post “3 -Ways to Bootstrap Spot Rates for the Treasury Yield Curve” at

http://pistulka.com/Other/?p=2089

After viewing the first three methods, come back to this post for an explanation of the forth method.

Method four on sheet “Fourth Method”:

This method may or may not be easier to follow than the first three. The difference between the formulas on sheets “Formula” and “Fourth Method” is that the calculations on the forth method are broken out into two extra columns. As was true with the other methods, the 6-month and 1-year Treasury yields are considered to already be spot rates, in that they are Treasury bills with no coupons, only a final payment of principal.

The first new column are the discount factors. This is a running present value of \$1, calculated using the spot rate and term for each row. The second column is the present value of each coupon payment using the sum of all the previous discount rates multiplied by the semiannual coupon payment.

# Flat Rate Loans

I have to be honest. I had never heard of a flat rate loan, until Rishi asked me to create an Excel spreadsheet for one. According to Wikipedia, flat rate loans are:

“Loans with interest quoted using a flat rate originated before currency was invented and continued to feature regularly up to and beyond the 20th century within developed countries. More recently, they have also come to be used in the informal economy of developing countries, frequently adopted by microcredit institutions”.

In countries where most people do not have access to a calculator or a computer, flat rates simplify the loan payment and interest calculations. That’s not to say that people in developed countries necessarily understand declining balance calculations.

The calculation for monthly payments on a flat rate loan are as follows:

Principal Portion:  Loan Balance/Months to Repay

Interest Portions: Flat Rate/12*Loan Balance

Monthly Payment = Principal + Interest

Principal, Interest, and Payments remain the same throughout the life of the loan. You enter the loan data in the yellow cells, in the spreadsheet called: Flat_Rate:

Comparing the flat rate to an amortized, declining balance loan rate, we need an APR. A rule of thumb, is that a comparable amortizing loan APR is about twice the flat rate. Using Excel’s IRR function we can get an exact APR, which shows that the 5.00% flat rate converts to an 9.105% APR:

Or, because the cash flow is constant, we can use Excel’s Rate function (see spreadsheet for actual formula inputs):

Obviously, a  5.00% flat rate is much more expensive than a 5% amortizing loan. In order to prove that, I have included an amortizing loan schedule. The schedule offer the user an option of using the flat rate as an input or the APR. Below shows the comparison using a 5.00% flat rate:

This shows that a 5.00% flat rate will cost the borrower an additional 11,053.46, compared to an amortizing loan. Notice that I did not use a \$, since anyone interested in a flat rate loan will be most likely be using a different currency.

Changing the dropdown cell to 9.105%, shows that a 5.00% flat rate is equal to a 9.105% amortizing loan, with the present value of each being 500,000.00.

I also included a calculator to convert a Flat Rate to an APR or an APR to a Flat Rate. This could be helpful for those that want to compare loan rates:

# Required Minimum Distribution (RMD) 3.0

This is my third post on RMD, thus the 3 point 0. The other two post are:

http://pistulka.com/Other/?p=2422

http://pistulka.com/Other/?p=2674

Read my first post if you are not familiar with RMD. This Excel workbook came about at the request of a reader who’s firm manages hundreds of retirement plans. I put together a spreadsheet to calculate RMD for multiple accounts.

I also included my declaimer, that I am not a tax adviser, tax attorney, or CPA. Also that this calculator is given “as-is” with no guarantee as to its accuracy or completeness. It covers most situations, but not all.

Each row contains the information for one client. The yellow cells are input cells. The calculator assumes the user wants to calculate RMD for the current year and it updates each year automatically. If you need to keep the current years information and you are getting close to year end, print a PDF copy.

The eligibility information shows which table the calculator will use. The factor comes from that table. It determines if the client is 70 and a half, and if the spouse is 10 years or more younger than the client. The client and spouse’s ages are only used if the table gets the factor from TableII.

# Defeasance 3.0

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

# MBS Amortization With VBA

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:

# Counting Binomial Lattice Paths

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.

# Addition to Required Minimum Distribution

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.

Workbook: Taxes

# Monthly Interest Using Actual Days, Without an Amortization

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:

Function Date_Interest(Remaining_Yrs, Gross_Rate, Starting_Date, Base_Year, AmortMonth, AmortCode)

‘—-AmortCodes—–:
‘NP=Normal Principal Amortization
‘I=Interest
‘EB=Ending Balance
‘P=Payment
BY = Base_Year
lastmonth = Starting_Date
GR = Gross_Rate / 100 ‘Gross Coupon
RM = Remaining_Yrs * 12 ‘ Remaining Months
SB = 100 ‘Principal Balance
TotalMonths = RM
RM = RM + 1
Payment = Application.WorksheetFunction.Pmt(GR / 12, RM – 1, SB * -1)
———————————————————|Amortization Starts
For i = 1 To TotalMonths
thismonth = i ‘ Month to go
RM = RM – 1 ‘ Remaining Months
—————————————————————|Interest Starts
Currentmonth = Application.WorksheetFunction.EDate(lastmonth, 1)
Interest = (Currentmonth – lastmonth) * GR / BY * SB
lastmonth = Currentmonth
‘—————————————————————|Interest Ends
amort = Payment – Interest  ‘Normal Amortization
SB = SB – amort
If i = AmortMonth Then
Select Case AmortCodeCase “NP”
Date_Interest = amort
Case “I”
Date_Interest = Interest
Case “EB”
Date_Interest = SB
Case “P”
Date_Interest = PaymentEnd Select
Exit Function
Else
End If
Next i
————————————————————————–|Amortization Ends
End Function

The UDF uses codes to determine what information is needed:

The only required inputs are the  yellow cells shown below:

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.

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

# Internal Audit – Interactive Report

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.

# Mortgage Loan Pool – Default Recovery

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.

# U.S. Gross Domestic Product Dashboard

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.