Amortization tables are commonly used in the financial sector. They list out the periodic payments on a loan or mortgage over a period of time and break down each payment into principal and interest. When we create an amortization table in Excel we can define, the amount we are borrowing, the interest rate, the number of payments, if we are paying at the beginning or end of the month, the start date, and if we have a balloon payment to clear the balance.

You may already be familiar with amortization tables and not realize it. Most of us who have a mortgage or have borrowed money from the bank will have received an amortization schedule from our lender so we can see the breakdown of our monthly payments. It is useful to know how to create an amortization table in Excel particularly if you work in the financial sector. Even if you don't, the process of creating an amortization table introduces us to some new techniques and formulas in Excel.

We can download an amortization table template in Excel or we can start from scratch and create our own.

To download a template

- Go to the File tab.
- Click on the New page.
- Type "amortization" into the search bar.

In this example, we are going to start from a blank template that does not contain any formulas.

Before we start adding calculations and formulas, let's first understand what we are looking at.

At the top, we have the details we need to calculate the monthly payments on a loan.

In this example, we are borrowing $350,000 to buy a house. We have $50,000 in savings which we will use at the end of the loan to clear the balance.

To calculate our monthly payments over the term of the loan, we need to first use formulas to work out the Period Rate, the Total Number of Periods, and the Monthly Payment. Once we have these calculations, we can use them to complete the amortization table.

First, let's calculate the monthly payment.

The Period Rate calculation works out the monthly interest rate. Currently, in the table above the interest rate is per annum. We want to know what our monthly payments will be so all of our calculations must be monthly.

This is a simple SUM calculation. We divide the Interest Rate (Annual) by the Number of periods per year.

- Type =SUM(D9/D11)

We are paying a monthly interest rate of 0.31250%.

Next, we need to work out the total number of periods the loan will be paid over. A month is classified as a period so effectively, how many months are we paying the loan over.

If you have the number of months already in the table then you do not need to do this calculation. In our example, we have the Loan Period showing as 30 years so we need to convert this to months.

This is a simple SUM calculation. We need to multiply the Loan Periods (Years) by the number of periods per year.

- Type =SUM(D10*D11)

The loan will be paid over a total of 360 months.

We can now use the PMT function in Excel to calculate the monthly payment. PMT stands for Payment and it is one of Excel's Financial functions.

This is how our PMT formula looks. Note the highlighted cells used in the calculation.

The PMT formula will produce a negative result by default. This is because Excel sees this money as being taken from your account.

To make this a positive value, press **F2** to edit the PMT formula and add a minus sign before the **pv**.

The total monthly payment for this loan is $1,545.60.

We can now use the monthly payment to complete the amortization table.

An amortization table allows us to see how much interest we are paying and how much the principal amount decreases by over the period of the loan. We need to complete each column in the table.

We are paying the loan over 360 months so we need to have 0 - 360 listed in the Payment Number column. Typing out these numbers manually would be a very tedious task. Fortunately, there is a magic trick in Excel that can help us with this.

- Type
**0**into the first cell. - Hold down the right mouse button, drag the fill handle down one cell and then back up one cell to reveal a secret menu.
- Select
**Series**from the menu. - In the
**Series in**area, select**Columns**. - Add a
**Stop value**of**360**.

- Click
**OK**.

This will quickly fill down 1 - 360 in the Payment Number column.

Next, we need to enter the Payment Date for each payment. We have the first payment date listed in the table. In our example, we are going to make a payment on the last day of the month.

- Manually type the date of the first payment in the cell next to payment 1.
- Double-click the fill handle to fill the dates down.

Notice that the dates are not correct. By default, Excel will assume we want consecutive dates. We can easily change this.

- Click the Autofill Options button.
- Select Fill Months from the menu.

The dates will now be the last day of every month.

Next, we need to bring our PMT calculation to the table. We can simply link to the cell.

- Click in the cell and type =
- Select the cell that contains the PMT calculation.

The amount we pay each month stays the same so remember to press **F4** to make the cell absolute.

- Double-click the fill handle to copy the formula down.

Next, we need to calculate the amount of interest paid each month. This will change as the loan reduces. To make this easier, we are going to add the **Ending Balance** in the first row of the table.

- Click in
**Interest Paid**.

The Interest Paid is the Balance * Monthly Interest Rate. We are going to copy this formula down so remember to lock cell D13. This calculation tells us how much interest we will pay each month.

Next, we need to calculate the** Principal Reduction**. This is the amount we are paying on the balance of the loan that is not interest. This is a simple calculation of PMT - Interest Paid.

Finally, we need to calculate the **Ending Balance**. This is the amount outstanding on the loan each month. This is a simple calculation of Ending Balance - Principal Reduction.

Now we have all the basic calculations in place, we can simply copy them down to complete the table.

- Select all cells in the second row that contain calculations.
- Double-click on the fill handle to copy down.

If the amortization table has been completed correctly, we should see that payment 360 has an ending balance of $50,000. It is at this point we would make our balloon payment to clear the loan.

Notice that as the loan reduces the amount of interest we are paying also reduces and the amount we are paying off the principal increases.

When working with financial spreadsheets and money, we need to be as accurate as possible. If we apply rounding to our amortization table, the ending balance will be a few pennies off but the calculations will be more accurate.

To round all calculations in the amortization table, we only need to round the PMT formula and the Interest Paid formula.

- Press
**F2**to edit the**PMT**formula. - Round the result to 2 digits.

- Press
**F2**to edit the**Interest Paid**formula. - Round the result to 2 digits.
- Copy the formula down using the fill handle.

For additional information on this topic in the MMC course library, check out all our Excel courses.

Creating a Custom Slicer Style Create a custom slicer style and mod...

Data Validation drop-down lists are a powerful Excel feature. Cre...

Creating a Custom Slicer Style Create a custom slicer style and mod...