Home > Formulas > Sum > Payments Excel Loan Payment Schedule TemplateEasy 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 - Loan Payment ScheduleIn this Excel loan payment schedule template, enter your loan information at the top of the worksheet, in the green cells. This video shows a 40-second overview of the payment schedule template, and how it works. |
Video: PMT FunctionThe template calculates the monthly payment, in cell E2, using the Excel PMT function. The next short video shows how the PMT function works, and the written instructions are below the video. |
Loan Payment ScheduleBelow 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 4 main sections on this page:
|
How to Use the Loan Payment ScheduleIn this section, you'll see how to use the loan payment schedule. |
Enter Loan InformationThis Excel loan payment schedule is simple to use. Just fill in the 4 green cells at the top of the worksheet:
Monthly Payment CalculationAfter 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 NumbersYou 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 ScheduleBelow 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.
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 RowsIn the Excel Loan Payment Schedule Template, the table has 48 rows. If you need more rows than that, follow these steps:
|
Loan Payment Schedule Table ColumnsThere are 7 columns in the payment details table. Here are the column names and purpose:
There are details on the loan payment schedule formulas, and how they work, further down the page. |
How It WorksYou 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 CellsTo make it easier to refer to the loan information cells at the top of the worksheet, those cells have been named:
NOTE: The cell names are shown in row 3, for information only. |
Payment Amount CalculationAt 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
In our formula, the required information was entered in the green cells at the top of the sheet.
PMT Formula Notes
|
Payment Schedule TableThe 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:
Payment NumberPay 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),"")
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 DatePay Date is the first column in the payment schedule table, and its formula calculates each payment date, based on the start date entered in cell A2 (LoanStart). Here is the formula in cell A7: =IF(G7="","", DATE(YEAR(LoanStart), MONTH(LoanStart)+G7-1, DAY(LoanStart)))
For example, if the first payment is December 1, 2019, the second payment is January 1, 2020.
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 PaymentIn 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) |
InterestIn 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. |
PrincipalIn 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. OutstandingFrom 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 PaidAnd 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 HighlightingThere'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:
How the Rule WorksIn the Conditional Formatting rule:
|
Get the TemplateTo 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 |
Last updated: April 19, 2023 2:45 PM