How to Make an Amortization Table in Excel?

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:

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:

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.

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

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.

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:

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

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.

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.

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

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

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.

Further Readings

• How to Refresh All Pivot Tables in Excel (3 Ways)
• Pivot Table Custom Grouping: With 3 Criteria
• Pivot Table is Not Picking up Data in Excel (5 Reasons)
• Excel Table Name: All You Need to Know