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.

**Table of Contents**hide

**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 tota**l 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.