The formula that's in Excel uses the PMT function and it looks like this:
Then it uses the PPMT function to get the principle total:
PPMT(AnnualInterestRate/PaymentsPerYear,A7,Years*PaymentsPerYear,Amount) A7 = which payment number you're on.
Then it closes with IPMT function to get the interest total:
IPMT(AnnualInterestRate/PaymentsPerYear,A7,Years*PaymentsPerYear,Amount) A7 = which payment number you're on.
Those last two look the same, but they aren't.
The initial information in the process is: ANNUAL INTEREST RATE, YEARS TO PAY, PAYMENTS PER YEAR, TOTAL AMOUNT. The goal looks like what's found here: https://www.excel-easy.com/examples/loan-amortization-schedule.html
What I did so far is create a table that's linked by a one-to-many relationship with the vehicle table. In my child table, I have changed the initial information to:
PRINCPLE AMOUNT (LOAN OR LEASE AMOUNT)
NUMBER OF MONTHS TO PAY (replaces years to pay and payments per year)
ANNUAL INTEREST RATE
What I plan to do is calculate the Payment Amount using this calculation in my table:
1. Calculate monthly interest rate = (1+annual interest rate)*(1/12-1).
2. Calculuate monthly payment = (principle*monthly interest rate from #1/1)-1+(monthly interest rate from #1).
3. Calculate principle =monthly payment amount from #2 - (remaining balance which is different after the first month*monthly interest rate from #1)
4. Calculate declining balance due = remaning balance-princple from line #3.
I'm trying to put it all in the table rather than in a datasheet. Then I'll add a field for payment date, but haven't figured out how to make it just advance monthly just yet.