Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
need a vehicle loan amortization
+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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
loan amortization loan in months | Excel Worksheet Functions | |||
Loan Amortization | Excel Worksheet Functions | |||
loan amortization template with loan start date AND first payment | Excel Discussion (Misc queries) | |||
loan amortization | New Users to Excel | |||
Loan amortization | Excel Worksheet Functions |