need a vehicle loan amortization

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.
HELP!

+AD4- iI +-need a vehicle loan amortization template that I can input the
+AD4- actually date paymts are received. The template that are provided
+AD4- amortizes the loan as if the customer makes the payment on the
+AD4- schedule 30 days but most often this is not true.

Here's one way, though there may be no actual loan that works like this.

In B1 put the loan amount.

In B2 put the annual interest rate.

In B3 put the starting date.

In A6 and downward will go the payment dates.

In B6 and downward will go the payment amounts.

Next,
C6 downward will be the portion of the payment that goes to interest.
D6 downward will be the portion of the payment that goes to principal.
E6 downward will be the remaining balance.

In C6 put
+AD0-IF(A6+AD0AIgAi-,+ACI-+ACI-,MIN(+ACQ-B6,+ACQ-B+ACQ-2+ACoAJA-B+ACQ-
1+ACo-(A6-+ACQ-B+ACQ-3)/365.25))

In D6 put
+AD0-IF(A6+AD0AIgAi-,+ACIAIg-,B6-C6)

In E6 put
+AD0-IF(A6+AD0AIgAi-,+ACIAIg-,+ACQ-B+ACQ-1-D6)

In C7 put
+AD0-IF(A7+AD0AIgAi-,+ACIAIg-,MIN(+ACQ-B7,+ACQ-B+ACQ-2+ACo-E6+ACo-(A7-
A6)/365.25))

In D7 put
+AD0-IF(A7+AD0AIgAi-,+ACIAIg-,B7-C7)

In E7 put
+AD0-IF(A7+AD0AIgAi-,+ACIAIg-,E6-D7)

Copy C7:E7 down as far as the list can go.

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.

For some reason, the formulas didn't turn out right in the newsgroup.

Below is a correction.



Here's one way, though there may be no actual loan that works like this.

In B1 put the loan amount.

In B2 put the annual interest rate.

In B3 put the starting date.

In A6 and downward will go the payment dates.

In B6 and downward will go the payment amounts.

Next,
C6 downward will be the portion of the payment that goes to interest.
D6 downward will be the portion of the payment that goes to principal.
E6 downward will be the remaining balance.

In C6 put
=IF(A6="","",MIN($B6,$B$2*$B$1*(A6-$B$3)/365.25))

In D6 put
=IF(A6="","",B6-C6)

In E6 put
=IF(A6="","",$B$1-D6)

In C7 put
=IF(A7="","",MIN($B7,$B$2*E6*(A7-A6)/365.25))

In D7 put
=IF(A7="","",B7-C7)

In E7 put
=IF(A7="","",E6-D7)

Copy C7:E7 down as far as the list can go.

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.