How to Make an Amortization Table in Excel?

Get FREE Advanced Excel Exercises with Solutions!

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.

Amortization Table in Excel


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.

Amortization Table in Excel

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.

Amortization Table in Excel


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)

Amortization Table in Excel

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.

Amortization Table in Excel


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.

Amortization Table in Excel

  • 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.

Amortization Table in Excel


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.

Amortization Table in Excel

  • 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.

Amortization Table in Excel

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

Amortization Table in Excel

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

Amortization Table in Excel

Read more: Create Table in Excel Using Shortcut


Similar Readings


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.

Amortization Table in Excel

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)

Amortization Table in Excel


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)

Amortization Table in Excel


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)

Amortization Table in Excel


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)

Amortization Table in Excel

In cell, C14 write the formula =I13. Now copy these formulas to all the cells of the table and here is the result.

Amortization Table in Excel

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.

Amortization Table in Excel

  • 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.

Amortization Table in Excel


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.

Amortization Table in Excel


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

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , and premium Excel consultancy services for Excel and business users. Feel free to contact us with your Excel projects.
Al Arafat Siddique
Al Arafat Siddique

Hello! This is Arafat. Here I'm researching Microsoft Excel. I did my graduation from Bangladesh University of Engineering and Technology(BUET). My interest in data science and machine learning allured me to play with data and find solutions to real-life problems. I want to explore this data-driven world and make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo