✦Template Printable Ideas Insights

**"Excel Mortgage Amortization Schedule: Step-by-Step Guide"

By

If you're curious about how much you'll pay on your mortgage over time, creating a mortgage amortization schedule in Excel is an excellent way to visualize and understand this. An amortization schedule details how much of your payment goes towards interest and how much towards the principal each period. Here's a step-by-step guide to help you create one.

Before we dive in, ensure you have Excel installed on your computer, and you're familiar with its basic functions. Let's assume you have a loan of $250,000 at an annual interest rate of 5%, paid monthly over 30 years.

Setting Up Your Amortization Schedule

The first step is to set up your spreadsheet to calculate the monthly payments, interest, principal, and remaining balance.

Loan Amortization Schedule for Excel
Loan Amortization Schedule for Excel
Create a loan amortization schedule in Excel (with extra payments)
Create a loan amortization schedule in Excel (with extra payments)
Loan Amortization Schedule in Excel
Loan Amortization Schedule in Excel

In the first row, create headers for each column: 'Period', 'Start Balance', 'Monthly Payment', 'Interest', 'Principal', and 'End Balance'.

Calculating Monthly Payment

Use Excel's PMT function to calculate your monthly payment. This function accepts five arguments: rate, num_periods, present_value, future_value, and type. In our case, 'rate' is your annual interest rate divided by the number of periods per year (5%/12 for monthly payments), and 'num_periods' is the total number of periods (360 for a 30-year loan). As we're paying off the loan, 'present_value' is -$250,000.

the loan annotation schedule is displayed in this screenshote, with an arrow pointing
the loan annotation schedule is displayed in this screenshote, with an arrow pointing
Loan Amortization Schedule Calculator | Plan Projections
Loan Amortization Schedule Calculator | Plan Projections
FREE 7+ Amortization Table Samples in Excel
FREE 7+ Amortization Table Samples in Excel

Extending the Schedule

Next, use the auto-fill feature to extend your amortization table. Click and drag the fill handle (the small square in the bottom-right corner of the cell) down to create periods for your loan. The 'Monthly Payment' column should now display the same amount ($1,266.51) for each period.

Similarly, copy and paste the 'Start Balance' cell downward. This will be the same as the 'End Balance' from the previous period. This step ensures that the balance for each period is correctly calculated.

Calculating Interest and Principal

Now, let's calculate the interest and principal components of your payment each period.

In the 'Interest' column, use Excel's IPMT function to calculate the interest paid each period. This function also takes five arguments: rate, period, num_periods, present_value, and future_value. For the first period, 'period' is 1, and for subsequent periods, it's the cell above.

Calculating Interest

For the first period, the formula will look like this: =IPMT((5%/12), 1, 360, -250000, ). Press enter, and you'll see the interest paid in the first period. Repeat this for each period, extending the formula down as needed.

The 'Principal' column can be calculated by subtracting the 'Interest' from the 'Monthly Payment': =

Create an Easy Loan Amortization Schedule in Excel & Google Sheets
Create an Easy Loan Amortization Schedule in Excel & Google Sheets
Get the Loan Amortization Schedule Template for Google Sheets
Get the Loan Amortization Schedule Template for Google Sheets
How to Make Loan Amortization Schedule in Excel - ORDNUR
How to Make Loan Amortization Schedule in Excel - ORDNUR
How to Create an Amortization Schedule Using Excel Templates
How to Create an Amortization Schedule Using Excel Templates
Loan Amortization with Microsoft Excel
Loan Amortization with Microsoft Excel
Mortgage Payoff Calculator with Extra Payment (Free Excel Template)
Mortgage Payoff Calculator with Extra Payment (Free Excel Template)
Amortization Schedule, Balloon Mortgage Calculator and Mortgage Payment Details
Amortization Schedule, Balloon Mortgage Calculator and Mortgage Payment Details
How to Create a Loan Amortization Schedule in Google Sheets
How to Create a Loan Amortization Schedule in Google Sheets
Loan Amortization with Extra Principal Payments Using Excel
Loan Amortization with Extra Principal Payments Using Excel