7 Ways To Create Amortization Schedule In Excel

Creating an amortization schedule in Excel can be a straightforward process, but it requires a solid understanding of the formula and functions involved. In this article, we will explore seven different methods to create an amortization schedule in Excel, each with its unique advantages and disadvantages.

What is an Amortization Schedule?

Before we dive into the methods, let's define what an amortization schedule is. An amortization schedule is a table that outlines the repayment of a loan or mortgage over a specific period. It includes the interest and principal paid in each payment, as well as the outstanding balance after each payment.

Why Create an Amortization Schedule in Excel?

Creating an amortization schedule in Excel has several benefits. For one, it allows you to easily adjust the inputs, such as the loan amount, interest rate, and repayment period, to see how it affects the repayment schedule. Additionally, Excel makes it easy to create a schedule for multiple loans or scenarios, making it a valuable tool for financial planning.

Method 1: Using the IPMT and PPMT Functions

The IPMT and PPMT functions in Excel can be used to calculate the interest and principal payments, respectively. To create an amortization schedule using these functions, follow these steps:

1. Set up a table with the following columns: Payment, Interest, Principal, and Balance.
2. In the first row, enter the loan amount, interest rate, and repayment period.
3. Use the IPMT function to calculate the interest payment for each period.
4. Use the PPMT function to calculate the principal payment for each period.
5. Calculate the balance by subtracting the principal payment from the previous balance.

Method 2: Using the PMT Function

The PMT function in Excel can be used to calculate the total payment for each period. To create an amortization schedule using this function, follow these steps:

1. Set up a table with the following columns: Payment, Interest, Principal, and Balance.
2. In the first row, enter the loan amount, interest rate, and repayment period.
3. Use the PMT function to calculate the total payment for each period.
4. Calculate the interest payment by multiplying the outstanding balance by the interest rate.
5. Calculate the principal payment by subtracting the interest payment from the total payment.

Method 3: Using a Formula

You can also create an amortization schedule using a formula that calculates the interest and principal payments. To create an amortization schedule using this method, follow these steps:

1. Set up a table with the following columns: Payment, Interest, Principal, and Balance.
2. In the first row, enter the loan amount, interest rate, and repayment period.
3. Use the following formula to calculate the interest payment: =B2*C2/12
4. Use the following formula to calculate the principal payment: =B2-(B2*C2/12)
5. Calculate the balance by subtracting the principal payment from the previous balance.

Method 4: Using a Template

If you want to create an amortization schedule quickly and easily, you can use a template. Excel provides several templates that can be used to create an amortization schedule. To create an amortization schedule using a template, follow these steps:

1. Open Excel and click on the "File" tab.
2. Click on "New" and then click on "Template".
3. Search for "amortization schedule" and select a template.
4. Enter the loan amount, interest rate, and repayment period.
5. The template will automatically calculate the interest and principal payments.

Method 5: Using a Macro

If you want to automate the process of creating an amortization schedule, you can use a macro. A macro is a set of instructions that can be recorded and played back to perform a specific task. To create an amortization schedule using a macro, follow these steps:

1. Open Excel and click on the "Developer" tab.
2. Click on "Record Macro" and then click on "OK".
3. Set up a table with the following columns: Payment, Interest, Principal, and Balance.
4. Enter the loan amount, interest rate, and repayment period.
5. Record the macro by clicking on the "Record Macro" button.
6. Play back the macro to create the amortization schedule.

Method 6: Using a Add-in

If you want to create an amortization schedule quickly and easily, you can use an add-in. An add-in is a software program that can be installed in Excel to perform a specific task. To create an amortization schedule using an add-in, follow these steps:

1. Open Excel and click on the "File" tab.
2. Click on "Options" and then click on "Add-ins".
3. Search for an amortization schedule add-in and install it.
4. Enter the loan amount, interest rate, and repayment period.
5. The add-in will automatically calculate the interest and principal payments.

Method 7: Using a Online Tool

If you don't have Excel or prefer not to use it, you can use an online tool to create an amortization schedule. There are several online tools available that can be used to create an amortization schedule. To create an amortization schedule using an online tool, follow these steps:

1. Search for "amortization schedule online tool" and select a tool.
2. Enter the loan amount, interest rate, and repayment period.
3. The tool will automatically calculate the interest and principal payments.

Gallery of Amortization Schedules

Amortization Schedule Examples

We hope this article has helped you understand how to create an amortization schedule in Excel. Whether you're a financial professional or just looking to manage your personal finances, an amortization schedule is a valuable tool that can help you make informed decisions about your money.