Welcome! Lets start your excel journey..

Welcome! Register Now to Unlock Exclusive Excel Tutorials

Get Started
  • Exclusive Playlists
  • Downloadable Videos
  • No Ads!
  • Exclusive Templates

How to Create a Loan Amortization Schedule in Excel: A Step-by-Step Tutorial

Admin
Duration: 9:13
Submitted: 7 months ago
Views: 364

Comments (0)

Link to this video:

Description:

When you take out a loan, the lender will usually require you to make regular payments over time. This can be difficult to keep track of without a loan amortization schedule.

In this tutorial, we will show you how to create a loan amortization schedule in Excel. It's easier than you might think! With this tool, you'll be able to keep track of your payments and ensure that you're on track to pay off your loan.

Table of Content;

00:00 INTRODUCTION

00:40 Loan Amortization Schedule

08:40 conclusion

What is Loan Amortization Schedule?

A loan amortization schedule is a table that shows you how much of your loan will be paid off over time. It includes information such as the interest rate, monthly payment amount, and remaining balance.

This type of schedule can be helpful in budgeting for your loan payments and keeping track of your progress. It can also help you see the true cost of your loan over time.

Why Use a Loan Amortization Schedule?

There are a few reasons why you might want to use a loan amortization schedule. First, it can help you budget for your payments. Knowing how much you need to pay each month can help you plan your finances accordingly.

Second, it can help you stay on track to pay off your loan. If you know what your remaining balance is and how much needs to be paid each month, you can make sure that you're making progress.

Finally, a loan amortization schedule can give you a good overview of the true cost of your loan. By seeing how much interest you'll pay over time, you can make sure that you're getting the best deal possible.

Can We Calculate Loan Amortization Schedule Online Without Excel?

There are a few websites that offer calculators to help you create a loan amortization schedule. However, these calculators can be limited and may not include all of the information that you need.

Excel is a versatile program that can easily create this type of schedule. With some simple formulas, you can customize your schedule to include all of the information that you need.

Tools you can use to create loan amortization schedule;

  • Calculator.net
  • Calculatorsoup

How to Create a Loan Amortization Schedule in Excel

Now that we've gone over what a loan amortization schedule is and why you might want to use one, let's get into how to create one. Creating a loan amortization schedule in Excel is actually very easy. We'll walk you through the steps below.

We will use the following functions to create loan amortization schedule in excel;

  • PMT Function
  • IPMT Function
  • PPMT Function

Lets learn the syntax of all of the above functions below;

The PMT function calculates the payment for a loan. The syntax is:

PMT(rate,nper,pv,type)

Where rate is the annual interest rate of the loan, nper is the number of payments to make on the loan, pv is the present value (or amount borrowed), and type can be either 0 or omitted for a regular loan or type can be set to

The IPMT function calculates the interest payment for a given period of the loan. The syntax is:

IPMT(rate,per,nper,pv)

Where rate is the annual interest rate of the loan, per is the number of the payment for which you want to calculate the interest, nper is the number of payments to make on the loan, and pv is the present value (or amount borrowed).

The PPMT function calculates the principal payment for a given period of the loan. The syntax is:

PPMT(rate,per,nper,pv)

Where rate is the annual interest rate of the loan, per is the number of the payment for which you want to calculate the principal, nper is the number of payments to make on the loan, and pv is

Now that we know how to use Excel to calculate a loan amortization schedule, let's put it into practice. In the below example, we will create a schedule for a loan with the following information:

- Loan amount: $15,000

- Annual interest rate: 12%

- Number of payments: 120

We will also assume that all payments are made at the beginning of each month.

Now let's get started!

 

Step One: Enter the loan amount, annual interest rate, and number of payments into a spreadsheet.

Step Two: In the second column, use the PMT function to calculate the monthly payment for the loan.

=PMT(A12/1200,B11,-C11)

In our example, this would be:

=PMT(12%/1200,120,-15000)

which gives us a result of $172.61.

Step Three: In the third column, use the IPMT function to calculate the interest payment for each period.

=IPMT(A12/1200,B13,B11,-C11)

In our example, this would be:

=IPMT(12%/1200,01,120,-15000)

which gives us a result of $15.00.

Step Four: In the fourth column, use the PPMT function to calculate the principal payment for each period.

=PPMT(A12/1200,B13,B11,-C11)

In our example, this would be:

=PPMT(12%/1200,01,120,-15000)

which gives us a result of $127.61.

Step Five: The final column will show the remaining balance of the loan after each payment is made.

=C11-B13

In our example, this would be:

=15000-127.61 which gives us a result of $14,472.39.

And there you have it! Your very own loan amortization schedule in Excel. With just a few simple steps, you can have all the information you need to track your loan payments. Be sure to experiment with different interest rates and payment terms to see how they impact your schedule. And most importantly, stay on top of those payments!