How to Create a Mortgage Payment Schedule in Excel

Managing your mortgage payments efficiently is crucial for maintaining a healthy financial life. Microsoft Excel, a powerful spreadsheet software, can help you create a mortgage payment schedule, enabling you to track your payments, understand your amortization schedule, and plan for the future. Here's a step-by-step guide on how to create a mortgage payment schedule in Excel.

Biweekly Mortgage Calculator in Excel with Extra Payments [Free Download] - ExcelDemy
Biweekly Mortgage Calculator in Excel with Extra Payments [Free Download] - ExcelDemy

Before we dive into the process, ensure you have the following information handy: your mortgage principal amount, interest rate, loan term, and the start date of your mortgage. You'll also need to know whether your mortgage is a fixed-rate or adjustable-rate mortgage, as this will affect your payment calculations.

Mortgage Re-Payment Schedule Excel | Templates at allbusinesstemplates.com
Mortgage Re-Payment Schedule Excel | Templates at allbusinesstemplates.com

Setting Up Your Excel Workbook

To begin, open a new Excel workbook and save it with a descriptive name, such as "Mortgage Payment Schedule." In the first sheet, name it "Payment Schedule."

Mortgage Payoff Calculator with Extra Payment (Free Excel Template)
Mortgage Payoff Calculator with Extra Payment (Free Excel Template)

In the second sheet, name it "Amortization Schedule." This sheet will help you understand how your mortgage principal reduces over time and how interest is applied to each payment.

Creating the Payment Schedule

Create a loan amortization schedule in Excel (with extra payments)
Create a loan amortization schedule in Excel (with extra payments)

In the "Payment Schedule" sheet, start by labeling the columns with the following headers:

  • Payment # - The sequential number of each payment.
  • Payment Date - The date when each payment is due.
  • Principal - The amount of the payment that goes towards reducing your mortgage principal.
  • Interest - The amount of the payment that goes towards paying the interest on your mortgage.
  • Total Payment - The total amount of each payment, which is the sum of the principal and interest.

Calculating Monthly Payments

How to Create an Amortization Schedule Using Excel Templates
How to Create an Amortization Schedule Using Excel Templates

To calculate your monthly mortgage payment, use the mortgage calculator formula: M = P [ i(1 + i)^n ] / [ (1 + i)^n – 1 ], where:

  • M is your monthly payment,
  • P is your principal loan amount,
  • i is your monthly interest rate (annual interest rate divided by 12),
  • n is the number of months in the loan term.

For example, if your principal is $200,000, your annual interest rate is 4%, and your loan term is 30 years, your monthly payment would be approximately $1,073.64.

Loan Amortization Schedule for Excel
Loan Amortization Schedule for Excel

Filling in the Payment Schedule

Now, fill in the "Payment Schedule" sheet with the following information:

Loan Payoff Tracker Excel Spreadsheet
Loan Payoff Tracker Excel Spreadsheet
Create an Easy Loan Amortization Schedule in Excel & Google Sheets
Create an Easy Loan Amortization Schedule in Excel & Google Sheets
Track Your Mortgage with Our Excel Spreadsheet - Track Your Extra Payments, Analyze Your Interest, and Pay down Debt!
Track Your Mortgage with Our Excel Spreadsheet - Track Your Extra Payments, Analyze Your Interest, and Pay down Debt!
Mortgage Amortization Schedule Excel Spreadsheet Template, Loan Payoff Schedule, Loan Payment Planner Excel, Debt Organizer, Debt Log Sheet
Mortgage Amortization Schedule Excel Spreadsheet Template, Loan Payoff Schedule, Loan Payment Planner Excel, Debt Organizer, Debt Log Sheet
Loan Amortization Schedule in Excel
Loan Amortization Schedule in Excel
Mortgage Schedule Calculator | Printable Excel Worksheet
Mortgage Schedule Calculator | Printable Excel Worksheet
Mortgage Payment Calculator and Payoff Schedule - Microsoft Excel Template | Loan Amortization | Calculate with Additional Payments
Mortgage Payment Calculator and Payoff Schedule - Microsoft Excel Template | Loan Amortization | Calculate with Additional Payments
Loan Amortization Schedule for Excel
Loan Amortization Schedule for Excel
Mortgage Loan Calculator Using Excel | Free Template & Guides
Mortgage Loan Calculator Using Excel | Free Template & Guides
Mortgage Calculator with Taxes and Insurance in Excel (With Easy Steps) - ExcelDemy
Mortgage Calculator with Taxes and Insurance in Excel (With Easy Steps) - ExcelDemy
Mortgage Payoff Tracker Excel Spreadsheet | Overpayment Calculator | Ireland Forecast Template | Google Sheets
Mortgage Payoff Tracker Excel Spreadsheet | Overpayment Calculator | Ireland Forecast Template | Google Sheets
Loan Repayment Google Sheets Spreadsheet, Mortgage Amortization Calculator Template with Extra Payments to Payoff a Mortgage or Car Loan
Loan Repayment Google Sheets Spreadsheet, Mortgage Amortization Calculator Template with Extra Payments to Payoff a Mortgage or Car Loan
Mortgage Amortization Table — Track Every Payment Over 30 Years
Mortgage Amortization Table — Track Every Payment Over 30 Years
Get the Loan Amortization Schedule Template for Google Sheets
Get the Loan Amortization Schedule Template for Google Sheets
Loan Amortization Schedule Excel Template | Mortgage Calculator Spreadsheet | Extra Payment Tracker | Debt Payoff Planner
Loan Amortization Schedule Excel Template | Mortgage Calculator Spreadsheet | Extra Payment Tracker | Debt Payoff Planner
Home Buying Planner Spreadsheet | Mortgage Calculator, Down Payment Tracker & Home Budget Template
Home Buying Planner Spreadsheet | Mortgage Calculator, Down Payment Tracker & Home Budget Template
Mortgage Payoff Calculator Spreadsheet (Pink), Mortgage Tracker for Microsoft Excel & Google Sheets, Financial Planning Tool Mortgage Cost
Mortgage Payoff Calculator Spreadsheet (Pink), Mortgage Tracker for Microsoft Excel & Google Sheets, Financial Planning Tool Mortgage Cost
Mortgage Payoff Calculator Spreadsheet, Amortization Schedule Google Sheets and Excel, Loan Payoff Tracker
Mortgage Payoff Calculator Spreadsheet, Amortization Schedule Google Sheets and Excel, Loan Payoff Tracker
Loan Amortization with Extra Principal Payments Using Excel
Loan Amortization with Extra Principal Payments Using Excel
Loan Amortization Schedule - ExcelSuperSite
Loan Amortization Schedule - ExcelSuperSite
  • In cell A2, enter the start date of your mortgage.
  • In cell B2, enter the formula =EDATE(A2, 1) to calculate the due date of your second payment.
  • Drag this formula down to copy the due dates for all your payments.
  • In cell C2, enter your monthly mortgage payment amount.
  • Drag this amount down to copy it for all your payments.

Your payment schedule is now set up, and you can easily track your mortgage payments over time.

Creating the Amortization Schedule

The "Amortization Schedule" sheet helps you understand how your mortgage principal reduces over time and how interest is applied to each payment. Here's how to create it:

Setting Up the Amortization Schedule

In the "Amortization Schedule" sheet, label the columns with the following headers:

  • Payment #
  • Payment Date
  • Beginning Principal - The remaining principal balance at the beginning of the period.
  • Interest
  • Principal
  • Ending Principal - The remaining principal balance at the end of the period.

Calculating the Amortization Schedule

To calculate the amortization schedule, use the following formulas:

  • In cell B2, enter the start date of your mortgage.
  • In cell C2, enter your principal loan amount.
  • In cell D2, enter the formula =C2*INT(RATE!B2,12,0) to calculate the interest for the first period, where RATE!B2 is the annual interest rate cell in a separate "Rate" sheet.
  • In cell E2, enter the formula =C2-D2 to calculate the principal for the first period.
  • In cell F2, enter the formula =C2-E2 to calculate the ending principal for the first period.
  • In cell G2, enter the formula =EDATE(B2,1) to calculate the payment date for the second period.
  • In cell C3, enter the formula =F2 to copy the ending principal to the beginning principal for the next period.
  • Drag the formulas in cells D3:F3 down to copy the calculations for all periods.

Your mortgage payment schedule and amortization schedule are now complete. Regularly update these schedules to stay on top of your mortgage payments and plan for the future. Consider exploring other Excel features, such as conditional formatting and data validation, to enhance the functionality of your mortgage management tools.

Managing your mortgage payments proactively is an essential aspect of maintaining financial health. By creating and regularly updating your mortgage payment schedule in Excel, you'll gain valuable insights into your mortgage amortization, understand your interest payments, and stay on track towards becoming debt-free. Happy spreadsheeting!