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

The answer appears below:

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

Don Pistulka

Retired Credit Union CFO - Finance
Background: over 40 years in investments, asset/Liability management, banking, securities trader.
Worked for: California Credit Union, WesCorp, CalFed S&L, Crocker Bank, Carroll McEntee, Federal Home Loan Bank Board (D.C.), Western Asset Management, Security Pacific National Bank.