iI +-need a vehicle loan amortization template that I can input the
actually date paymts are received. The template that are provided
amortizes the loan as if the customer makes the payment on the
schedule 30 days but most often this is not true.
Here's a further correction. :-(
If a payment falls short of the interest due that day, the unpaid
interest should actually be added to the balance due. The unpaid interest
wouldn't just be forgiven, right? The prior post didn't account for this.
In addition to the correction, the sheet is rearranged a little to
simplify it.
<><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><>
Here's one way, though there may be no actual loan that works like this.
In A1 put the annual interest rate.
In A4 put the starting date.
In E4 put the loan amount.
In A5 and downward will go the payment dates.
In B5 and downward will go the corresponding payment amounts.
In C5 downward will be the interest due on that date. In C5 put
=IF(A5="","",$A$1*E4*(A5-A4)/365.25)
In D5 downward will be the portion of the payment that goes to principal.
A value can be negative if the payment for that date is less than the
interest due. In D5 put
=IF(A5="","",B5-C5)
In E5 downward will be the remaining balance. In E5, put
=IF(A5="","",E4-D5)
Copy C5:E5 down as far as the list can go.
Modify as necessary.