Get Loan Amortization Schedule in Excel

To start it all up, loan amortization is a fancy way to define a loan that is paid back in installments throughout the entire term of the loan. So, all loans are amortized in nature in one way or another. To elaborate all this in a simpler method let me give you an example; if one happens to get an amortized loan for 12months then we will have 12 equal monthly payments, with each payment comes an amount towards principal and some towards interest. To ease up your work, you can create a loan amortization schedule.

An amortization schedule is basically a table that lists periodic payments on a mortgage or loan over time. It breaks down each payment into principal and interest, moreover, it shows the remaining balance after each payment, interesting right? Let's go ahead and identify how to create an amortization schedule.

Table of Contents

Method 1: Using inbuilt Excel Template

Open excel
Search for Loan Calculator template
Click create

Fill in the templates with your custom details.

Let us calculate the monthly payment of a loan that has an annual interest of 6%, 2 years of payment, and the amount borrowed to be $42,000. The start date of this loan is 1/1/2021.

The template will display a summary of the loan repayment. Scroll down to view all the details.

Method 2: Calculate Loan Amortization from scratch

The main requirements that one needs include the following functions:

PMT FUNCTION: This function calculates the total amount of periodic payment. The amount will stay constant for the entire duration of the loan.

PPMT FUNCTION: This function gets the principal part of each payment that goes towards the loan principal, the amount you borrowed. The amount will increase with the subsequent payment.

IPMT FUNCTION: This function finds the interesting part of each payment that goes towards interest. Unlike the above, this decreases with each payment.

Now, let's go through it, step by step:

1. Set up the amortization schedule.

Define the input cells where you will enter the known components of a loan which are:

B1- annual interest rate.
B2- loan term in years.
B3 – payments per year.
B4- loan amount.

After that, we'll create an amortization table with labels (Period, Payment, Interest, Principal, Balance) that should be in A7:AE, in the period column, enter a series of numbers equal to the number of payments, in this case, it's ( 1 – 12).

Let us calculate the monthly payment of a loan that has an annual interest of 6%, 2 years of payment, and the amount borrowed to be $42,000. Let's place our values in the consecutive cells of the template that we have created

2. Use PMT() to calculate the first payment

For you to get the payment amount, we calculate it with the PMT (rate, nper,pv, [fv] ,[type] function. It is an inbuilt Excel function. First label your first payment on cell A:8

Now place the cursor to cell B:8 to calculate the first payment. Click on the formula bar at the top left corner and search for the PMT function

The values on the popo up menu represents

Rate: the annual interest rate.

Nper: number of years

Pv: payments per year

FV: The loan amount

Fill in your values to calculate the 1st payment

Click ok and the first payment will be populated

You'll then put the formula together in form of an absolute cell reference, which you'll then copy to the B8 after dragging it down, you'll notice that there's a constant payment amount for all the periods.

3. Find the principal using PPMT ().

To calculate, the principal part, we'll use the PPMT function. Click on cell C:8 and search for the function.
Fill in your values in the pop-up menu and press okay

4. Use iPMT() to calculate the 1st interest

Place the cursor on cell D:9 or below the cell that you have labeled interest

Search for the formula
Enter your values to the corresponding boxes in the pop-up menu and press okay

5. Get the remaining balance.

In order to get the remaining balance for each period you can use this formula;

To find the balance after payment the first payment in E8, simply add the loan in B4 and the principal of the first period C8. Thus, B4+D8. Place the cursor on cell E8 and write the formula =B4+D8 on the formula bar and press enter.