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:

Download spreadsheet “Daily_Interest

 

Comments

  1. I apologize if I’m not posting this in the correct place, I found your website and like the area of taxes on IRAs and RMD’s. Unfortunately you didn’t have exactly what I was looking for. I am in the search of an Excel spreadsheet that will show a table for IRA distributions for a sum of money at a fixed rate of return. For example $100,000 invested in IRA at 4% how long will that last with the RMD’s coming out. This would be a great table for you to include in your taxes area.
    Thanks for the help in advance.

    1. Don Pistulka Don Pistulka says:

      James,
      I might not get to it today, but I will work on something and get back to you.

      Don

    2. Don Pistulka Don Pistulka says:

      James,
      Did you receive the attachment?

Leave a Reply

Your email address will not be published. Required fields are marked *

*
*

WordPress spam blocked by CleanTalk.