Saquib Ahmad Shuvo
If you are looking for some special tricks to create a car loan amortization schedule with extra payments in Excel, you’ve come to the right place. There is one way to create a car loan amortization schedule with extra payments in Excel. This article will discuss every step of this method to create a car loan amortization schedule in Excel. Let’s follow the complete guide to learn all of this.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article. It contains all the datasets in different spreadsheets for a clear understanding. Try yourself while you go through the step-by-step process.
Overview of Loan Amortization
An amortizing loan is a loan where the principal is paid down throughout the life of the loan according to an amortization plan, often by equal payments, in banking and finance. An amortizing bond, on the other hand, is one that repays a portion of the principal as well as the coupon payments. Let’s say, the total value of the car is $200000.00, the annual interest rate is 10%, and you will pay the loan within 1 year. A Loan Amortization Schedule is a schedule showing the periods when payments are made toward the loan. Among the information found in the table is the number of years left to repay the loan, how much you owe, how much interest you are paying, and the initial amount owed.
Step-by-Step Procedure to Create Car Loan Amortization Schedule in Excel with Extra Payments
In the following section, we will use one effective and tricky method to create a car loan amortization schedule with extra payments in Excel, it is necessary to make a basic outline and calculations with formulas and calculate the final balance. Here, we will use PMT, IPMT, and PPMT financial formulas to create a car loan amortization schedule with extra payments. PMT stands for payment, IPMT is used to get the interest of payment, and PPMT is used to get the principal payment. This section provides extensive details on this method. You should learn and apply all of these to improve your thinking capability and Excel knowledge.
We will use apply these financial functions to calculate the car loan amortization. We use the Microsoft Office 365 version here, but you can utilize any other version according to your preference. Here is an overview of a car loan amortization schedule with extra payments in Excel.
Step 1: Calculate Total Payment of Loan Amortization
First of all, we are going to calculate the payment by utilizing the PMT function. Then we will add this payment with an extra payment to get the total payment. One can easily calculate one’s payment every week, month, or year by using this function. Let’s walk through the following steps to calculate the total payment of loan amortization.
- First of all, you have to type the data of annual interest rate, years, number of payments per year, and original balance as shown below.
- Next, to calculate payment, we will use the following formula in the cell D8:
=PMT(D$4/D$6,D$5*D$6,D$7)
Here, D$4 is the annual interest rate, D$5 is the number of years, D$6 is the number of payments per year and D$7 is the original price of the car.
- Then, press Enter.
- As a result, you will get the payment as shown below.
- Next, input the payment value on the Payment column for every month as shown below.
- Then, enter the value in the Extra Payment column.
- Now, are going to add payment with extra payment to get the total payment value. To calculate the total payment, we will use the following formula in the cell E12:
=C12+D12
- Then, press Enter.
- Consequently, we will get the total payment for the first month as shown below.
- Next, drag the Fill Handle icon to fill the rest of the cells in the column with the formula.
- Therefore, by following the above method, you will get the total payment for twelve months of the loan as shown below.
Read More: Create Loan Amortization Schedule with Moratorium Period in Excel
Step 2: Evaluate Interest for Each Month
Now, we are going to calculate the interest of payment by using the IPMT function. Let’s walk through the steps to calculate the interest of the payment.
- First of all, to calculate interest of the payment, we will use the following formula in the cell F12:
=IPMT(D$4/D$6,B12,D$5*D$6,D$7)
Here, D$4 is the annual interest rate, D$5 is the number of years, D$6 is the number of payments per year and D$7 is the original price of the car. B12 is the number of months.
This function will return the value in red with parentheses. This is because it was selected as the default currency subtype for accounting purposes. If you want to change this, you have to right-click on the cell, select format cells, and choose the subtype that you want.
- Then, press Enter.
- As a result, you will get the interest for the first month as shown below.
- Next, drag the Fill Handle icon to fill the rest of the cells in the column with the formula.
- Therefore, by following the above formula, you will get the interest for twelve months of the loan as shown below.
Read More: Excel Simple Interest Loan Calculator with Payment Schedule
Step 3: Estimate Principal Amount
Now, we are going to calculate the principal of payment by using the PPMT function. Let’s walk through the steps to calculate the principal of the payment.
- First of all, to calculate interest of the payment, we will use the following formula in the cell G12:
=PPMT(D$4/D$6,B12,D$5*D$6,D$7)
Here, D$4 is the annual interest rate, D$5 is the number of years, D$6 is the number of payments per year and D$7 is the original price of the car. B12 is the number of months.
This function will return the value in red with parentheses. This is because it was selected as the default currency subtype for accounting purposes. If you want to change this, you have to right-click on the cell, select format cells, and choose the subtype that you want.
- Then, press Enter.
- As a result, you will get the principal for the first month as shown below.
- Next, drag the Fill Handle icon to fill out the rest of the cells in the column with the formula.
- Therefore, you will get the principal payment for twelve months of the loan as shown below.
Step 4: Calculate Balance of Loan Amortization
After completing the payment per month, the interest payment per month, and the principal payment per month, we are going to calculate the balance of the loan by using those values. Let’s walk through the steps to calculate the balance of the loan.
- First of all, you have to enter the original balance in cell H11.
- First of all, to calculate the balance of the loan, we will use the following formula in the cell H12:
=H11+G12
- Then, press Enter.
- As a result, you will get the balance for the first month as shown below.
- Next, drag the Fill Handle icon to fill the rest of the cells in the column with the formula.
- Therefore, you will get the balance of the loan for twelve months of the loan as shown below. After the 12th month, you will be able to pay the loan which has been given in the below screenshot. This is how you will be able to create a car loan amortization schedule with extra payments in Excel.
Read More: Student Loan Payoff Calculator with Amortization Table in Excel
Conclusion
That’s the end of today’s session. I strongly believe that from now you may be able to create a car loan amortization schedule with extra payments in Excel. If you have any queries or recommendations, please share them in the comments section below.
Don’t forget to check our website Exceldemy.com for various Excel-related problems and solutions. Keep learning new methods and keep growing!
Related Articles
- SBI Home Loan EMI Calculator in Excel Sheet with Prepayment Option
- Excel Loan Calculator with Extra Payments (2 Examples)
- Home Loan EMI Calculator with Reducing Balance in Excel
- Create Home Loan Calculator in Excel Sheet with Prepayment Option
- Create Home Loan EMI Calculator in Excel Sheet with Prepayment Option
Your formula above does not apply the extra payment to the balance. How would I change the formula to account for that extra payment being applied?
Hi MICHELLE,
Greetings and thank you for your inquiry.
To apply the above formula to the extra payment, you need to change the formula in cell H12 as follows:
=H11+G12-D12
This means (Original Balance-Principal-Extra Payment).