Excel Loan Payment Schedule Template

Easy to use Microsoft Excel loan payment schedule shows monthly payment details, based on your loan info, entered at the top of the worksheet.

Note: Check with your lender, to confirm the exact dates and amounts - they might use different calculations.

Author: Debra Dalgleish

Introduction

In this Excel loan payment schedule template, enter your loan information at the top of the worksheet, in the green cells. The template calculates the monthly payment, in cell E2, using the Excel PMT function. This short video shows how the PMT function works.

To see the steps for calculating a simple loan payment with the PMT function, watch this short video. The written instructions are below the video.

Loan Payment Schedule

Below that, there is a loan repayment schedule, showing each payment date and payment details. The row with the latest payment is highlighted, so it's easy to see where you are in the schedule.

There are 3 main sections on this page:

• How to Use the Loan Payment Schedule: How to enter loan information, see the payment schedule, and add more rows, if needed
• How It Works: See the monthly payment calculation, the payment schedule formulas, and details on how they work
• Highlighting: See how the conditional formatting rule is set up, so it highlights the latest payment row in the payment schedule
• Download: Get the Loan Payment Schedule Template

How to Use the Loan Payment Schedule

In this section, you'll see how to use the loan payment schedule.

Enter Loan Information

This Excel loan payment schedule is simple to use. Just fill in the 4 green cells at the top of the worksheet:

• First Payment: The date when you'll make your first loan payment
• Loan Amount: The amount you are borrowing
• Months: The number of months over which you'll pay back the loan
• Annual Rate: The annual rate of interest for the loan

Monthly Payment Calculation

After you enter the loan information on the spreadsheet, the PMT function in the yellow cell (E2) calculates the monthly payment. For example, with a loan amount of $5000, over 36 months, at an annual interest rate of 5%, the monthly payment is calculated to be $149.85.

WARNING: Check with your loan company to confirm the exact dates and amounts - they might use different calculations.

If you select the cell with the monthly payment calculation, you can see the formula in the Excel Formula Bar.

=-PMT(LoanRate/12,LoanMths,LoanAmt)

If you're interested in how the formula works, there are details further down on this page.

Try Different Loan Numbers

You can try different loan numbers in the green cells, to see how they affect the monthly loan payment amount. For example, if you borrow $10000 over 48 months, at a rate of 4.5%, the monthly payment is $228.03.

Loan Payment Schedule

Below the loan information cells, there is a loan payment schedule, with all the monthly payment details.

WARNING: Check with your loan company to confirm the exact dates and amounts - they might use different calculations.

In the screenshot below, the loan is $5000, over 36 months, at an annual rate of 5%. The monthly payment is $149.85, and that amount appears in each row of the payment schedule, in column C.

• NOTE: All of the table cells contain formulas. Do not type in the loan payment schedule. Only enter information in the green cells at the top of the worksheet.

Scroll to the end of the loan payment schedule to see when your last payment will be made. For this example, the first payment was made on January 1st, 2018, and the last payment will be made on December 1, 2020.

Add More Rows

In the Excel Loan Payment Schedule Template, the table has 48 rows. If you need more rows than that, follow these steps:

• Scroll to the end of the loan payment schedule table
• Point to the small handle, at the bottom left corner of the table
• When the pointer changes to a 2-headed arrow, drag down, to add more rows

Loan Payment Schedule Table Columns

There are 7 columns in the payment details table. Here are the column names and purpose:

• Pay Date: Payment due date
• Outstanding: Outstanding loan amount, before the payment is made
• Mth Pmt: Payment amount, from the yellow cell at the top of the worksheet
• Interest: How much of the monthly payment is for interest?
• Principal: How much of the monthly payment goes toward the principal?
• Total Princ Paid: Running total of the principal amounts paid to date
• Pmt Num: Payment number

There are details on the loan payment schedule formulas, and how they work, further down the page.

How It Works

You can use the Excel Loan Payment Schedule Template without knowing anything about its formulas. Just enter your loan information in the green cells, and see the results in the table.

But, if you'd like to know what formulas and functions are used, and how they work, this section has those details for you.

Named Cells

To make it easier to refer to the loan information cells at the top of the worksheet, those cells have been named:

• First Payment: Cell A2, named LoanStart
• Loan Amount: Cell B2, named LoanAmt
• Months: Cell C2, named LoanMths
• Annual Rate: Cell D2, named LoanRate
• Monthly Payment: cell E2, named LoanPmt

NOTE: The cell names are shown in row 3, for information only.

Payment Amount Calculation

At the top of the worksheet, in the yellow cell (E2), the Excel PMT function calculates the monthly payment. The PMT function has 3 required arguments

• rate - interest rate for the loan
• nper - total number of payments for the loan
• pv - present value; also known as the principal

In our formula, the required information was entered in the green cells at the top of the sheet.

• =-PMT(LoanRate/12,LoanMths,LoanAmt)

PMT Formula Notes

• There's a minus sign at the start of the formula, to show the result as a positive number.
• The annual loan rate is divided by 12, to calculate the monthly rate
• The PMT function also has 2 optional arguments, which are not used in the template's formula. You could add these arguments, if needed for your calculation.
  • fv - future value, or the balance that you want to have left after the last payment. If fv is omitted, the fv is assumed to be zero.
  • type -When payments are due in the period (0=end, 1=beginning). If omitted, type is assumed to be zero, and payments are due at the end of the period.

Payment Schedule Table

The loan payment schedule is in a named Excel table – tblPay. Each column contains formulas - no data is typed into the table.

The formulas in the first 6 columns (A:F) check the Pay Num column ( G ), to see if contains an empty string (""). If it does, the other formulas show an empty string too.

Here's the start of all the other formulas, in row 7:

• =IF(G7="","",

Payment Number

Pay Num is the last column in the payment schedule table, and it calculates the payment number in each row. Because all of the other columns check the Pay Num column, we'll look at its formula first.

Here is the Pay Num formula in cell G7:

=IF(MAX(G$6:G6)< LoanMths, SUM(G6,1),"")

• First, the MAX function finds the highest number in column G, starting in row 6 (absolute row reference G$6), and ending in the row above the formula's row (relative reference - G6)
• That number is compared to the number at the top of the sheet, in the LoanMths cell
• If the MAX number is lower, then the SUM function adds 1 to the MAX result
• If the MAX number is not lower, then the result is an empty string (""), and the cell looks empty

In this example, the loan term is 36 months, so the payment numbers stop at 36, in row 42, and all the other cells in that row look empty.

Payment Date

Excel is smart enough to show that as a real date, instead of creating an error because we told it to show month 13 (12+2-1)

Month Payment

In column C, there's a simple formula to show the monthly payment amount, based on the PMT function result at the top of the worksheet:

=IF(G7="","",LoanPmt)

Interest

In column D, the IPMT function calculates the interest amount in each payment. The IPMT function is similar to the PMT function, but it has one additional argument -per(period number).

IPMT(rate, per, nper, pv, [fv], [type])

Here is the Interest formula in cell D7, where the period number comes from cell G7:

=IF(G7="","",-IPMT(LoanRate/12, G7, LoanMths, LoanAmt))

NOTE: There is a minus sign in front of the IPMT function, to return a positive number.

Principal

In column E, the formula shows how much of the monthly payment went toward paying off the principal amount.

Since the interest has been calculated in column D, it's easy to find the principal amount – just subtract the interest amount from the payment amount.

=IF(G7="","",C7-D7)

NOTE: You could use the PPMT function instead, but the simple subtraction works just as well.

Outstanding

From the principal amounts, we can calculate the Outstanding amount. In each row, it's the original loan amount, minus the total principal amounts in the rows above.

=IF(G7="","",LoanAmt-SUM(E$6:E6))

Total Principal Paid

And for the Total Princ Paid column, the formula is a total of all the principal amounts, including the current row.

=IF(G7="","",SUM(E$6:E7))

Last Payment Highlighting

There's a Conditional Formatting rule in the loan payment schedule table, so it highlights the latest payment, based on the current date.

To create the conditional formatting rule, follow these steps:

• Select all the data cells in the table, starting from cell A7.
• On the Excel Ribbon's Home tab, click Conditional Formatting, then click New Rule
• For Rule Type, select "Use a formula to determine which cells to format"
• In the formula box, type this INDEX/MATCH formula, which refers to cell A7 (the active cell):
  • =$A7=INDEX($A$7:$A$54, MATCH(TODAY(), $A$7:$A$54,1))

How the Rule Works

In the Conditional Formatting rule:

• MATCH looks for the current date (TODAY function) in the payment date column
• If the current date is not found, MATCH returns the location of the latest date before the current date, because the 3rd argument is 1 (Less than)
• Then, the INDEX function returns the date from that location in the list of payment dates
• If the date in the current row matches that date, the table row is highlighted with light orange

Get the Template

To get the template, download the Excel Monthly Payment Schedule Template file. The file is in xlsx format, and zipped. There are no macros in the workbook.

Excel Functions Tutorials

Excel PMT function

Payment Calculation Table

Cost Calculator, Annual

SUM Function

VLOOKUP Function