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)
‘NP=Normal Principal Amortization
‘EB=Ending Balance
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
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.

The answer appears below:

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

Download spreadsheet “Daily_Interest


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.

 Download:  Audit_Bubble

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:


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

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.

Download “GDP


Add-On Interest Rate to Compensate for Financing Expenses

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


Testing my VBA Mortgage Portfolio Cash Flows

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.



Required Minimum Distribution (RMD)

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



How much to withdraw from a taxable account (IRA, 401K, etc.)

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

Valuing a Mortgage Portfolio With Cash Flow Analysis

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:


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



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

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:


Download “TotalMega



Tax Equivalents

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

Average Life VBA Function For MBS

This is the fourth  in a series of VBA, User Defined Functions for Mortgage Backed Securities (MBS). The other three are, solve for price given yield , yield given price and Macaulay Duration . Actually the seventh UDF, if you include the functions solving for the market value of Servicing, IO, and PO). As was true of the previous three functions, it allows for a number of option. It also, as before, includes an amortization schedule as a proof of the function’s calculations.

Average Life is the average number of years that each dollar of unpaid principal remains outstanding, Computed at the average time to the receipt of all future principal payments due to be paid.

Neither Servicing Fee nor Loss Severity are asked for in this function. They do not affect average life.

Download: MortgageAvLife.xlsm

Macaulay Duration VBA Function For MBS (or single loans), Given Market Yield

This is the third in a series of VBA, User Defined Functions for Mortgage Backed Securities. The other two solved for price given yield and yield given price. Actually the sixth if you include the functions solving for the market value of servicing, IO, and PO). As was true of the previous two functions, it allows for a number of option. It also, as before, includes an amortization schedule as a proof of the function’s calculations.


Market Yield VBA Function For MBS (or single loans), Given Price

If this post looks familiar, it is supposed . My last post was “Market Price VBA Function For MBS (or single loans), Given Yield“. This Excel spreadsheet is almost the same function, except that this function solves for yield given price rather than price given yield. The main difference in the two functions is that yield can’t be solved directly through a formula, it needs an iteration. Iteration means obtaining successively closer approximations to the solution, or in this case the approximations are yields.

My VBA leaves much to criticize, particularly by the purists.  I didn’t declare my variables, and I did something you don’t see very often these days in VBA code. I wanted to maintain the same code I used in the price give yield function, without changing any of it (because I am basically lazy), by wrapping the old code inside the iteration.  I ran into a problem however, with the balloon portion. I needed a way to complete the iteration from the balloon portion, that exited the function once the balloon payment was reached. The answer I used was to number the line of code that I wanted to jump to and used a “goto” statement.

I was self taught a computer language in the late 60’s call “Basic”. The modern VBA was originally built upon Basic. Code lines were numbered in Basic and that was the way you jumped around within the code. Numbering lines of code is no longer needed in todays VBA, but it still exists. I doubt they even teach numbering  lines of code in VBA classes any longer.

Below are the input cells in yellow. Everything looks the same as the price given yield function, except now you enter the market price and the function returns the yield. As I did in the previous spreadsheet, I included an amortization as a proof to check the yield, but it is not necessary to run the function.

God Bless

Download “MortgageYield.xlsm


Market Price VBA Function For MBS (or single loans), Given Yield

I now also have another function to calculate yield given price see:

Market Yield VBA Function For MBS (or single loans), Given Price

Many of my previous posts and Excel spreadsheets have included amortization schedules that can calculate the price of MBS pools or single loans, given a discount rate (market yield). This spreadsheet includes a UDF (user defined function) that allows the user to calculate the price, without having to construct an amortization schedule. I do include an amortization schedule as a proof however, that the function is working correctly. My posts on the mega-formula for calculating price, given yield gave the same basic results, but it did not have all of the inputs allowed with this function.

Below, you can see all the variables that can be included in the calculation, however only the market yield, remaining months, and gross rate are required inputs. The rest are optional. Optional inputs include, servicing fee, prepayment rate (CPR), balloon months remaining, interest only months remaining, default rate (CDR), loss severity, and payment delay (days). By default, I included a value in each of the input cells, only to show how each of the inputs should be formatted. Most users will not use all the possible inputs for any given pool.

On the input sheet called Function, next to the input cells, are explanations of how some of the inputs should be used. For example, if you have a loan with a 60 month balloon and the loan is interest only, the interest only should be 59 months, not 60 to allow for the last payment which is all principal. The same would hold for any interest only loan. Also, mortgage payments have 30 days of accrued interest at each payment, so the default delay is 30 days. If payments are assumed to be paid at end of a grace period, for example (15th of the month rather than the 1st) the delay would be 44 days. Some agency MBS have longer delays.

The UDF is called “Mortgage_Price”. Below I show that the function arguments are fairly self-explanatory:

The “Proof” sheet has an amortization schedule used to proof the results of the UDF. There are no input cells on this sheet. It also shows a break down of the present value of each of the components of the loan pool, to get to the final price.

On the sheet called “VBA”, I included the function’s VBA. I divided up the calculations to show the steps involved. The logic was the same as I would use to construct an amortization schedule. As the “For I = 1 to rm”  line loops through each payment, it accumulates the present value of each cash flow that will be paid (based on $100 in par value) to the holder of the pool. That accumulation is the market price.

I must include a disclaimer, in that I have not compared all combinations of input. I do not have access to Bloomberg or other systems to confirm my results. Therefore use at your own risk. No guarantee is either stated or implied.

God Bless

Download Excel Spreadsheet: