This article shows how to make an Amortization table in Excel that shows the periodic payments on a loan over a period of time. This table breaks down each periodic payment into the amount of interest and principal paid and the amount of the remaining balance. This illustration will show you how to make your own amortization table.
Download the Practice Workbook
Download this practice workbook to exercise while you are reading this article.
4 Different Methods to Create an Amortization Table in Excel
1. Create Amortization Table for Loan Schedule in Excel Using the PMT, PPMT, IPMT Functions
To create a loan amortization schedule in Excel we need to use several functions to calculate the payment per period, the interest paid per payment, and the principal paid per payment to show the remaining balance after each payment until it reaches a null value. Let’s follow the steps to make it happen.
1.1 Initial Set-up for Amortization Table
At first, we need to set up the data for our amortization table. We’ll calculate a loan schedule for an amount of 500,000 dollars at an annual rate of 5% over a time period of 10 years. This is the amortization table, we are going to fill up with loan schedule data.
1.2 Calculate the Payment per Period
We’ll use the PMT function to calculate the Payment per Period using the following data:
C4: Loan Amount (pv) = $ 500,000
C6*E6 = Number of Payment Periods (nper) = Loan Period * Payments per Period= Cell 10*1=10
C7 = Interest Rate (rate) = Annual Interest Rate/ Payments per Period = .05/1 =.05
In cell C9 put the following formula:
=PMT (C7, C6*E6,-C4)
We used a negative sign before the pv amount as we want the payment per period as positive.
As we know this loan payment per period is the same throughout the payment period, just copy the value in the Payment column in the amortization table.
1.3 Calculate the Interest
We can use the IPMT function which is used to find the interest payment of a loan amount for a certain period of time. The function takes rate, per, nper and pv as arguments. Among them rate, nper and pv are the same as what we used for the PMT function. And the per argument is the period number like 1, 2, 3 until it reaches 10, in this example, for which we are calculating the interest payment. Let’s follow the steps below:
Steps:
- In cell E13 put the following formula:
=IPMT($C$7,B13,$C$6*$E$6,-$C$4)
Here we need to use absolute cell references for rate, nper, and pv as they remain unchanged over the time period of 10 years. But the per argument which represents the period number in column B uses relative cell reference so that it can adjust the value.
- The above formula calculates the interest payment for the first loan payment. The result is $25000. We have this value positive as we put a negative sign in front of the loan amount in the formula.
- To get all the interest payments for 10 consecutive periods, locate the fill handler drag it down, and then release the mouse.
- Finally, we get the interest payments for each of the payments.
1.4 Calculate the Principal Payment
The PPMT function(rate, per, nper,pv) calculates the periodic principal payments to pay a loan or to invest an amount on a fixed interest rate over a certain period of time. In this example, we used the function in our amortization table to find out the principal payment values. Let’s follow the steps:
Steps:
- Put the following formula in cell F13.
=PPMT($C$7,B13,$C$6*$E$6,-$C$4)
The PPMT function takes interest rate (rate), payment period number (per), number of total payment periods(nper), and the loan amount(pv) as its argument. Here we need to use absolute cell references for rate, nper, and pv, and relative cell references for per argument.
- Now to get all other values, locate the fill handler, hold and drag it down to the end and release it.
- The final result is here in the below screenshot.
1.5 Find Out the Remaining Balance
In the first cell of the balance-end column, we need to subtract the principal amount from the balance-start amount i.e. C13-F13.
With the above formula, we’ve got the remaining balance after the first payment is done. To find out the other values of the remaining balance follow the steps below:
- In cell C14, write the following formula
=I13
The remaining balance after the first payment is the starting balance of the second payment.
- Locate the fill handler of cell I13 at the bottom right corner of the cell and drag it down.
- The output values are not correct as the values of the balance-start column are still empty.
- To correct the remaining balance values let’s copy the formula of cell C14 to cells C15:C22 using the Fill Handler.
Finally, our amortization table for the loan schedule is completed with the remaining balance as $0,00.
Read more: How to Make a Table in Excel
2. Make an Amortization Table in Excel Manually
In the first method, we used the IPMT and PPMT functions to find out the interest and principal payments in the amortization table. Let’s find out the values without using these functions.
2.1 Find Out the Interest
The interest in each periodic payment is the product of the remaining balance and the interest rate per period. Let’s put the formula below to get the interest payment of the loan payment.
=C13*$C$7
Here we used absolute cell reference for cell C7 which holds the value of the rate per period, as it doesn’t change throughout the calculation.
2.2 Find Out the Principal Payment
We just need to subtract the interest from the periodic payment to get the principal. Let’s do it to get the principal value for the first loan payment. The formula is:
=D13-E13
Read more: Create Table in Excel Using Shortcut
Similar Readings
- How to Make a Table in Excel (2 Simple Ways)
- Update Pivot Table Range (5 Suitable Methods)
- How to Insert A Pivot Table in Excel (A Step-by-Step Guideline)
- Insert or Delete Rows and Columns from Excel Table
3. Modify an Excel Amortization Table for Loan Schedule with Additional Payments
In this example, we’ll illustrate the amortization table with additional payments. Keeping all other values the same as before, we just add another column in our table named additional payments.
Now to complete the amortization table for the loan schedule with additional payments do the following:
For convenience, we named some of the cell values so that it would be easier to write the formulas.
C4 = InterestRate, C5 = LoanTerm,C6 = PaymentsPerYear,C8 = LoanAmount,C9 = ExtraPayment,C10 = ScheduledPayment.
3.1 Scheduled Payment
We used the IF function to set up the value for the Scheduled Payment.
Logical test: Scheduled Payment > Balance-Start (Remaining Balance)
If true:Â value = Scheduled Payment
If false: value = Remaining Balance + Remaining Balance
Put the following formula in cell D13
=IF(ScheduledPayment<=C13, ScheduledPayment, C13+C13*InterestRate/PaymentsPerYear)
3.2 Interest
We used the IF function to set up the value for the Scheduled Payment.
Logical test: Scheduled Payment > 0
If true:Â value = Remaining Balance * InterestRate / PaymentsPerYear.
If false: value = Zero
Put the following formula in cell H13.
=IF(D13>0, InterestRate/PaymentsPerYear*C13, 0)
3.3 Principal
We used the IF function to set up the value for the Scheduled Payment.
Logical test: Scheduled Payment > 0
If true:Â value = Min of Scheduled Payment minus Interest or the Remaining Balance.
If false: value = Zero.
Put the following formula in cell G13.
=IF(D13>0, MIN(D13-H13, C13), 0)
3.4 Additional Payment
We used the IF function to set up the value for the Scheduled Payment.
Logical test: Additional Payment < Remaining Balance – Principal
If true:Â value = Additional Payment.
If false: value =Remaining Balance – Principal.
Put the following formula in cell E13
=IF(AdditionalPayment<C13-G13, AdditionalPayment,C13- G13)
3.5 Total Payment
Total Payment = Scheduled Payment + Additional Payment
3.6 Remaining Balance
We used the IF function to set up the value for the Scheduled Payment.
Logical test: Scheduled Payment > 0
If true: value = Remaining Balance – Principal – Additional Payment
If false: value = Zero
Put the following formula in cell I13
=IF(C13 >0, C13-G13-E13, 0)
In cell, C14 write the formula =I13. Now copy these formulas to all the cells of the table and here is the result.
Read more: How to Edit a Pivot Table in Excel
4. Use of Excel Template for Amortization Table
There is a free and downloadable loan amortization table that can easily be modified according to our requirements. Follow the steps below:
Steps:
- Go to the following link:
https://templates.office.com/en-us/loan-amortization-schedule-tm03986974
- In this template put our own data that we used in the previous method.
- The formulas needed to calculate the interest, payment, principal, remaining balance are already given in the template. Our input data will give a complete amortization table within seconds.
Notes
We can calculate the running total of the principal column to see how the loan amount is paid off. The running total of the interest payment is helpful to find out how much extra money we need to pay while paying off the loan amount.
Conclusion
Now, we know how to create an amortization table in Excel. Hopefully, it would encourage you to use this feature more confidently. Any questions or suggestions don’t forget to put them in the comment box below.