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:

‘—-AmortCodes—–:

‘NP=Normal Principal Amortization

‘I=Interest

‘EB=Ending Balance

‘P=Payment

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)

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:

Download spreadsheet “Daily_Interest”