mortgage amortization schedule template excel

Excel Mortgage Amortization Schedule Templates: Calculate Payments Easily

By Christopher Walker
AD

Understanding your mortgage amortization schedule is key to managing your home loan effectively. It's a detailed breakdown of your monthly payments over the life of your loan, showing how much goes towards interest and principal each month. To help you track your progress, you can create a mortgage amortization schedule using Excel.

Using an Excel template for your mortgage amortization schedule offers several benefits. It's flexible, customizable, and allows you to update and view your payment details in real-time. Plus, it's easy to create, even for those new to Excel. Let's explore how to make your own mortgage amortization schedule template in Excel.

Creating Your Mortgage Amortization Schedule Template

Before you start, gather your loan details including loan amount, interest rate, term length, and your monthly payment amount. Then, follow these steps to create your template:

AD
Loan Amortization Schedule for Excel
Loan Amortization Schedule for Excel
the loan amount schedule for an apartment
the loan amount schedule for an apartment
Mortgage payoff calculator with extra principal payment (Free Template)
Mortgage payoff calculator with extra principal payment (Free Template)

1. **Set Up the Header**: In Row 1, list the following headers: Month, Interest, Principal, Total Payment, Balance Owing. Use Merge & Center to center the headers across the columns.

Inputs and Formulas

Start in Row 2 to input your loan details. Enter your loan amount in cell D2 (e.g., "80000"), interest rate in B3 as a decimal (e.g., "0.05"), and term length in C3 (e.g., "300" for 30 years). Then, enter your monthly payment in B4 (e.g., "546.69").

Loan Amortization Schedule in Excel
Loan Amortization Schedule in Excel
Amortization Schedule for a Mortgage
Amortization Schedule for a Mortgage
Mortgage Payoff Calculator with Extra Payment (Free Excel Template)
Mortgage Payoff Calculator with Extra Payment (Free Excel Template)

**Calculate Monthly Interest**: In C5, enter this formula: `=B3*D2` to calculate the monthly interest. Then, apply this formula to every cell below it, copying down to match your loan's term length.

Calculating Principal, Total Payment, and Balance Owing

**Principal**: In D5, enter this formula: `=B4-C5` to calculate the principal portion of your payment. Copy this formula down to match your loan's term length. Be sure to wrap text in these cells to display the number as a dollar amount.

AD

**Total Payment**: In E5, enter this formula: `=C5+D5` to calculate the total payment. Copy this formula down to match your loan's term length.

Mortgage Amortization Table — Track Every Payment Over 30 Years
Mortgage Amortization Table — Track Every Payment Over 30 Years
Loan Amortization Schedule (Simple)
Loan Amortization Schedule (Simple)
Making An Excel Payment Schedule To Easily Keep Track Of Your Money - Free Sample, Example & Format Templates
Making An Excel Payment Schedule To Easily Keep Track Of Your Money - Free Sample, Example & Format Templates
10 Free Amortization Schedule Templates in MS Word and MS Excel
10 Free Amortization Schedule Templates in MS Word and MS Excel
13 Amazing Amortization Schedule Templates in EXCEL - Word Excel Fomats
13 Amazing Amortization Schedule Templates in EXCEL - Word Excel Fomats

**Balance Owing**: In F5, enter this formula: `=IF(D4=0, D3+D5, D3-D5+IFERROR(F4,0))` to calculate the remaining balance. Copy this formula down to match your loan's term length. In cell D21, enter "0" to separate youracrylate formula.

Customizing Your Mortgage Amortization Schedule

A basic mortgage amortization schedule is a great start, but you can add more features to tailor it to your needs:

AD

**Extra Payments**: Allocate rows for extra payments by entering your amount in the Principal column (D) and leaving the Interest column (C) blank. Remember to adjust the Total Payment and Balance Owing columns accordingly.

**Change Interest Rate**: To see how a change in interest rate affects your loan, create a new set of columns with a modified interest rate. This will also change your principal, total payment, and balance owing amounts.

Visualizing Your Progress

**Line Graph**: Create a line graph to visualize your remaining balance over time. Select the Month column as your x-axis, and the Balance Owing column as your y-axis. This graph lets you see your loan progress at a glance.

AD

**Area Chart**: To show how much you've paid in interest and principal over time, create an area chart. Select the Month column as your x-axis, the Interest column as one series, and the Principal column as another series.

**Regularly review and update** your mortgage amortization schedule to stay on top of your loan. It's an essential tool for managing your home loan and understanding your finances. And with an Excel template, it's easy to keep track of your progress and make adjustments as needed.

AD