Free Excel Loan Amortization Schedule (Free Download)

Managing a loan becomes much easier when you know exactly where your money is going every month. Whether you’re paying off a home mortgage, car loan, personal loan, student loan, or small business loan, an Excel loan amortization schedule helps you understand every payment in detail.

Instead of wondering how much of your monthly payment goes toward interest and how much reduces your loan balance, an amortization schedule gives you a clear month-by-month breakdown. This makes it easier to plan your finances, estimate your total interest costs, budget for future payments, and even see how making extra payments can help you become debt-free sooner.

Microsoft Excel is one of the best tools for creating and managing loan amortization schedules because it automatically performs the calculations for you. By entering a few basic loan details, such as the loan amount, annual interest rate, repayment period, and payment frequency, Excel generates a complete repayment schedule that updates instantly whenever you change the loan information. Many built-in Excel financial functions, including PMT(), IPMT(), and PPMT(), make these calculations both accurate and easy to customize.

In this comprehensive guide, you’ll learn everything you need to know about using a free Excel loan amortization schedule. 

We’ll explain what an amortization schedule is, how it works, how to download or create one, and how to customize it for different loan types. 

You’ll also learn how to add extra payments, reduce your total interest, compare loan options, and avoid common mistakes that beginners often make.

What Is a Loan Amortization Schedule?

A loan amortization schedule is a detailed table that shows how a loan is repaid over time. It lists every scheduled payment from the beginning of the loan until the balance reaches zero. Rather than showing only the total monthly payment, it breaks each payment into two important parts:

Principal

The principal is the original amount of money you borrowed. Every payment reduces a portion of this balance.

Interest

Interest is the cost of borrowing money. At the beginning of most loans, a larger portion of each payment goes toward interest. As your loan balance decreases, the interest portion becomes smaller, while more of each payment is applied toward the principal.

For every payment period, an amortization schedule typically displays:

• Payment number
• Payment date
• Beginning loan balance
• Monthly payment amount
• Interest paid
• Principal paid
• Remaining loan balance

Because the balance changes after every payment, the interest charged also changes over time. This is why an amortization schedule is one of the most valuable financial planning tools available. It provides complete transparency into how your loan is being repaid and helps you see the long-term cost of borrowing. Most fixed-rate loans, including mortgages, auto loans, and many personal loans, use this type of repayment schedule.

For example, imagine you borrow $25,000 for a car with a 6% annual interest rate over 5 years. Although your monthly payment remains the same throughout the loan term, the amount applied to interest decreases each month while the amount applied to the principal increases. 

By the final payment, nearly all of your payment goes toward paying off the remaining loan balance.

How Does a Loan Amortization Schedule Work?

A loan amortization schedule follows a simple principle: every payment you make is divided into interest and principal. Although your monthly payment usually stays the same for a fixed-rate loan, the way that payment is split changes over time.

When your loan begins, the outstanding balance is at its highest. Since interest is calculated on the remaining balance, a larger portion of your first few payments goes toward paying interest. Only a smaller portion reduces the principal.

As you continue making payments, your loan balance gradually decreases. Because interest is calculated on a smaller balance each month, the interest portion becomes smaller while the principal portion becomes larger. 

By the final payment, almost the entire payment goes toward paying off the remaining principal. Excel’s PMT(), IPMT(), and PPMT() functions are specifically designed to calculate the total payment, interest portion, and principal portion of each installment.

For example, imagine you borrow $30,000 with a 5.5% annual interest rate for 5 years.

Your first payment may look something like this:

Payment Details	Amount
Monthly Payment	$573.22
Interest	$137.50
Principal	$435.72
Remaining Balance	$29,564.28

Several years later, one of your final payments might look like this:

Payment Details	Amount
Monthly Payment	$573.22
Interest	$5.18
Principal	$568.04
Remaining Balance	$0.00

Notice that the monthly payment remains the same, but the interest decreases while the principal increases. This shifting balance is exactly what an amortization schedule helps you visualize.

Why Should You Use an Excel Loan Amortization Schedule?

Many online loan calculators only tell you your monthly payment. They rarely provide a complete picture of how your loan changes over time.

An Excel amortization schedule offers several advantages.

It Shows Every Payment

Instead of displaying only the monthly payment, Excel lists every payment from the beginning of the loan until the balance reaches zero. You can quickly see exactly how much principal and interest you’re paying each month.

It Helps You Budget Better

Knowing your repayment schedule allows you to plan monthly expenses more effectively. You can estimate future balances and understand how much debt remains after any payment.

It Makes Extra Payments Easy to Track

One of Excel’s biggest advantages is flexibility. You can add an Extra Payment column and instantly see how paying an additional amount each month reduces both your loan term and the total interest paid.

It Is Fully Customizable

Unlike many online calculators, Excel lets you customize almost everything, including:

• Payment frequency
• Interest rate
• Loan term
• Currency
• Cell colors
• Charts
• Printable layouts
• Additional payment columns
• Taxes and insurance columns

It Works Offline

Once you’ve downloaded the workbook, you don’t need an internet connection. Your loan calculations remain available whenever you need them.

It Can Compare Multiple Loans

Planning to refinance or choose between different loan offers?

You can duplicate the worksheet and compare:

• Monthly payments
• Total interest paid
• Loan payoff dates
• Overall borrowing costs

This makes Excel a practical tool for evaluating financing options before committing to a loan.

Free Excel Loan Amortization Templates to Download

To help you get started quickly, we’ve created several Excel templates designed for common borrowing situations. 

Each template includes realistic sample data, built-in formulas, and editable fields so you can replace the example values with your own loan information.

I’ve created a starter set of 10 Excel workbooks for you. You can download them below:

1. Basic Loan Amortization Schedule
2. Mortgage Loan Calculator
3. Car Loan Amortization Schedule
4. Personal Loan Planner
5. Student Loan Repayment Tracker
6. Business Loan Amortization Schedule
7. Extra Payment Calculator
8. Biweekly Loan Payment Schedule
9. Adjustable Rate Loan Calculator
10. Printable Loan Payment Tracker

Note: These are basic starter templates containing input fields and a working PMT() formula.

Template 1: Basic Loan Amortization Schedule

Best for: Beginners, personal loans, and simple installment loans.

This template includes:

• Loan amount
• Annual interest rate
• Loan term
• Monthly payment
• Payment number
• Interest paid
• Principal paid
• Remaining balance

It is ideal if you’re creating your first amortization schedule or simply want to understand how loan repayments work.

Sample Data

Field	Value
Loan Amount	$10,000
Interest Rate	7%
Loan Term	3 Years
Payment Frequency	Monthly
Total Payments	36

Template 2: Mortgage Loan Amortization Schedule

Best for: Home loans and long-term mortgages.

This template is designed for longer repayment periods and includes optional columns for:

• Property taxes
• Home insurance
• HOA fees
• Extra principal payments
• Total monthly housing payment

Sample Data

Field	Value
Loan Amount	$350,000
Interest Rate	6.25%
Loan Term	30 Years
Payment Frequency	Monthly
Total Payments	360

This template allows homeowners to see how additional principal payments can significantly reduce total interest over the life of the mortgage.

Template 3: Car Loan Amortization Schedule

Best for: New and used vehicle financing.

Buying a car is one of the most common reasons people use an amortization schedule. This template helps you monitor how much of each payment goes toward interest versus reducing your loan balance.

Features

• Automatic monthly payment calculation
• Monthly payment schedule
• Principal and interest breakdown
• Remaining balance tracker
• Total interest paid
• Loan payoff summary

Sample Loan Data

Field	Value
Loan Amount	$28,500
Interest Rate	5.75%
Loan Term	60 Months
Payment Frequency	Monthly
Vehicle	2026 Sedan

Why Use This Template?

Auto loans typically range from three to seven years. This template lets you see exactly how quickly your balance decreases and how making additional payments can reduce the total interest you’ll pay over the life of the loan.

Template 4: Personal Loan Amortization Schedule

Best for: Debt consolidation, medical expenses, vacations, weddings, or home improvements.

Personal loans usually have shorter repayment terms than mortgages, making them ideal for a simple Excel repayment schedule.

Features

• Fixed monthly payments
• Interest tracker
• Principal tracker
• Remaining balance
• Early payoff calculator

Sample Loan Data

Field	Value
Loan Amount	$12,000
Interest Rate	9.25%
Loan Term	36 Months
Payment Frequency	Monthly
Purpose	Home Renovation

Why Use This Template?

It gives borrowers a clear picture of how much interest they’re paying and how much of the loan remains after each payment.

Template 5: Student Loan Repayment Schedule

Best for: College and university education loans.

Student loans often span several years, making it helpful to monitor repayment progress.

Features

• Monthly repayment schedule
• Remaining balance
• Interest paid
• Total repayment cost
• Optional extra payment column

Sample Loan Data

Field	Value
Loan Amount	$45,000
Interest Rate	4.95%
Loan Term	10 Years
Payment Frequency	Monthly
Total Payments	120

Why Use This Template?

Many graduates make extra payments whenever possible. This template allows you to see how additional payments reduce both the repayment period and total interest. Adding an extra payment column is a common way to model faster loan payoff in Excel.

Template 6: Small Business Loan Amortization Schedule

Best for: Business expansion, equipment purchases, and startup financing.

Business owners often need to track loan repayments for accounting and budgeting purposes.

Features

• Monthly repayment schedule
• Interest expense tracking
• Principal reduction
• Remaining balance
• Annual payment summary

Sample Loan Data

Field	Value
Loan Amount	$150,000
Interest Rate	7.10%
Loan Term	7 Years
Payment Frequency	Monthly
Business Purpose	Equipment Purchase

Why Use This Template?

It helps business owners understand borrowing costs while making budgeting and cash-flow planning much easier.

Template 7: Extra Payment Loan Amortization Schedule

Best for: Borrowers who want to pay off loans early.

One of the biggest advantages of Excel is that you can include an Extra Payment column.

Whenever you enter an additional payment, the remaining balance decreases more quickly, reducing future interest charges.

Features

• Standard monthly payment
• Extra payment column
• Interest savings
• Early payoff estimate
• Remaining balance

Sample Loan Data

Field	Value
Loan Amount	$220,000
Interest Rate	6.15%
Loan Term	30 Years
Monthly Extra Payment	$250
Payment Frequency	Monthly

Why Use This Template?

Even a relatively small extra payment each month can shorten the repayment period and lower the total interest paid over the life of the loan. Dynamic amortization templates commonly use formulas that automatically recalculate the schedule when extra payments are entered.

Template 8: Biweekly Loan Payment Schedule

Best for: Homeowners and borrowers who receive biweekly paychecks.

Instead of making 12 monthly payments each year, this template is designed around 26 biweekly payments.

Features

• Biweekly payment dates
• Interest calculation
• Principal reduction
• Remaining balance
• Loan payoff tracker

Sample Loan Data

Field	Value
Loan Amount	$180,000
Interest Rate	5.80%
Loan Term	25 Years
Payment Frequency	Every Two Weeks

Why Use This Template?

Making biweekly payments often results in the equivalent of one additional monthly payment each year, which can reduce the overall loan term and total interest costs.

Template 9: Adjustable Interest Rate Loan Amortization Schedule

Best for: Adjustable-rate mortgages (ARMs), refinancing, and loans with changing interest rates.

Unlike a fixed-rate loan, an adjustable-rate loan may have one interest rate for an introductory period and a different rate afterward. This template helps borrowers understand how rate changes affect monthly payments and the remaining loan balance.

Features

• Adjustable interest rate input
• Initial fixed-rate period
• Updated payment calculation after the rate changes
• Principal and interest breakdown
• Remaining balance tracker
• Total interest summary

Sample Loan Data

Field	Value
Loan Amount	$275,000
Initial Interest Rate	4.25%
Adjusted Interest Rate	6.00%
Initial Fixed Period	5 Years
Total Loan Term	30 Years
Payment Frequency	Monthly

Why Use This Template?

If your loan’s interest rate changes in the future, this template lets you estimate how your monthly payment may change. It is also useful when comparing refinancing options or evaluating whether switching to a fixed-rate loan could reduce long-term borrowing costs.

Template 10: Printable Loan Payment Tracker

Best for: Borrowers who prefer keeping printed payment records.

Some people like maintaining paper copies of their financial documents. This template combines an amortization schedule with a payment tracker that can be printed and stored with other loan documents.

Features

• Clean printable layout
• Monthly payment checklist
• Payment due dates
• Remaining balance
• Annual payment summary
• Notes section

Sample Loan Data

Field	Value
Loan Amount	$18,500
Interest Rate	8.50%
Loan Term	48 Months
Payment Frequency	Monthly
Loan Purpose	Home Improvement

Why Use This Template?

It provides a simple way to keep payment records, monitor loan progress, and verify that every payment has been made on time.

Which Loan Amortization Template Should You Choose?

Choosing the right template depends on the type of loan you’re managing. While every template calculates principal, interest, and the remaining balance, some are tailored for specific borrowing needs.

If You Need To…	Recommended Template
Track a simple personal loan	Basic Loan Amortization Schedule
Manage a home mortgage	Mortgage Loan Template
Finance a vehicle	Car Loan Template
Repay education debt	Student Loan Template
Track business financing	Business Loan Template
Pay off a loan faster	Extra Payment Template
Make payments every two weeks	Biweekly Payment Template
Monitor changing interest rates	Adjustable Interest Rate Template
Keep printed payment records	Printable Payment Tracker

By selecting the template that matches your loan, you’ll spend less time editing spreadsheets and more time understanding your repayment progress.

Before You Use Any Template

Before entering information into your Excel workbook, gather the following loan details. Having accurate information ensures your repayment schedule is correct from the very first payment.

Loan Amount

Enter the total amount borrowed from the lender before any repayments are made.

Example: $50,000

Annual Interest Rate

This is the yearly percentage charged by your lender.

Example: 6.25%

Always enter the annual interest rate unless the worksheet specifically requests a monthly rate. Most Excel formulas automatically convert annual rates into monthly rates by dividing by 12.

Loan Term

Specify how long you’ll take to repay the loan.

Examples include:

• 3 years
• 5 years
• 10 years
• 15 years
• 30 years

Excel converts the loan term into the total number of payment periods.

Payment Frequency

Choose how often you’ll make payments.

Common options include:

• Monthly
• Biweekly
• Weekly
• Quarterly

Monthly payments are the most common for mortgages, personal loans, and auto loans.

Loan Start Date

The start date helps Excel calculate future payment dates automatically.

Example:

January 1, 2027

Optional Information

Some templates also allow you to enter:

• Extra monthly payments
• Property taxes
• Home insurance
• HOA fees
• One-time lump sum payments
• Balloon payments
• Late payment notes

These optional fields make the templates more flexible and suitable for different financial situations.

Why We Included Sample Data

Every downloadable template contains realistic example values instead of empty cells.

This allows you to:

• Understand how the worksheet works immediately.
• See formulas in action before entering your own numbers.
• Verify that calculations are updating correctly.
• Learn faster by modifying an existing example rather than building a schedule from scratch.

Once you’re comfortable with the layout, simply replace the sample values with your own loan information. Excel will automatically recalculate the payment amount, principal allocation, interest charges, and remaining balance using its financial functions.

How to Download a Free Excel Loan Amortization Template

Getting started with a loan amortization schedule is easy because you don’t have to build one from scratch. Microsoft provides free financial templates that you can open, customize, and save directly in Excel. These templates already include the formulas needed to calculate monthly payments, interest, principal, and the remaining loan balance.

If you’ve downloaded one of the templates from our website, simply save the file to your computer and open it in Microsoft Excel. All formulas are already configured, so you’ll only need to replace the sample loan details with your own information.

Below are three simple ways to get started.

Method 1: Download a Loan Amortization Template from Our Website

This is the easiest option because the templates are already designed with real-life examples and beginner-friendly layouts.

Step 1: Choose the Template That Matches Your Loan

Browse the downloadable templates on this page and select the one that best matches your loan.

For example, you might choose:

• Basic Loan Amortization Schedule
• Mortgage Loan Template
• Car Loan Template
• Student Loan Template
• Extra Payment Template

Each workbook is designed for a different borrowing situation while keeping the layout easy to understand.

Step 2: Download the Excel File

Click the Download button.

Depending on your browser, the file may automatically download to your Downloads folder.

If prompted, choose a location that’s easy to remember, such as:

Documents

or

Desktop

Step 3: Open the Workbook

Double-click the downloaded .xlsx file.

Microsoft Excel will open the workbook automatically.

If you receive a security warning stating that the workbook contains formulas, click Enable Editing so you can enter your own loan details.

Method 2: Use Microsoft’s Free Excel Templates

If you already have Microsoft Excel installed, you can download one of Microsoft’s built-in amortization templates directly from the application or from the Microsoft Create template gallery. Microsoft offers templates such as Loan Amortization Schedule, Mortgage Loan Calculator, and Vehicle Loan Payment Calculator, all of which can be customized with your own loan information.

Step 1: Open Microsoft Excel

Launch Excel as you normally would.

Instead of opening an existing workbook, stay on the Home screen.

Step 2: Search for Templates

In the search box, type:

Loan Amortization

or

Loan Calculator

Excel will display several available templates.

Step 3: Select a Template

Choose the template that best fits your needs.

Click Create.

Excel will download the workbook and open it automatically.

Step 4: Save Your Copy

Before making changes, save the workbook with a new name.

For example:

Mortgage Loan 2027.xlsx

This preserves the original template in case you want to use it again later.

Method 3: Build Your Own Loan Amortization Schedule

If you’d rather learn Excel while creating your own worksheet, you can build an amortization schedule from scratch using Excel’s built-in financial formulas. Functions such as PMT(), IPMT(), and PPMT() automatically calculate the payment amount, interest portion, and principal portion once you enter the loan details.

Creating your own workbook also gives you complete control over the design and calculations.

How to Open the Template in Microsoft Excel

Once you’ve downloaded the workbook, opening it takes only a few seconds.

Step 1: Locate the Downloaded File

Open your Downloads folder or the location where you saved the workbook.

The file usually ends with:

.xlsx

Step 2: Double-Click the Workbook

Excel will automatically launch and display the worksheet.

If another spreadsheet program opens instead, right-click the file and choose:

Open With

Then select Microsoft Excel.

Step 3: Enable Editing

If the workbook was downloaded from the internet, Excel may display a yellow notification bar.

Click:

Enable Editing

This allows you to modify the worksheet and replace the sample loan information.

Step 4: Save a Working Copy

It’s always a good idea to save another copy before editing.

Go to:

File > Save As

Choose a new file name.

For example:

Personal Loan Schedule.xlsx

This gives you a clean backup if you ever need to start over.

How to Use the Loan Amortization Template

One of the biggest advantages of these templates is that they require very little manual work. Most calculations are automatic.

You’ll simply replace the example values with your own loan information.

Step 1: Enter the Loan Amount

Locate the input section at the top of the worksheet.

Click the Loan Amount cell.

Replace the sample value with the total amount you borrowed.

Example

$75,000

As soon as you press Enter, Excel prepares to recalculate the repayment schedule.

Step 2: Enter the Interest Rate

Find the Annual Interest Rate field.

Type the interest rate exactly as shown in your loan agreement.

Example

6.25%

Do not convert it into a monthly percentage unless the worksheet specifically asks for one. Most templates perform that conversion automatically using built-in formulas.

Step 3: Enter the Loan Term

Specify how long you’ll repay the loan.

Examples include:

• 36 months
• 60 months
• 10 years
• 15 years
• 30 years

The template uses this information to determine the total number of payment periods.

Step 4: Choose the Payment Frequency

Select how often you’ll make payments.

Common options include:

• Monthly
• Biweekly
• Weekly
• Quarterly

Most personal loans and mortgages use monthly payments, while some lenders offer biweekly repayment options.

Step 5: Review Your Monthly Payment

After entering the loan amount, annual interest rate, loan term, and payment frequency, the template automatically calculates your regular payment using Excel’s PMT() function. This function returns the fixed payment amount required to pay off the loan over the selected term, assuming a constant interest rate and consistent payment schedule. (support.microsoft.com)

Review the calculated payment carefully before relying on the schedule.

Make sure the amount is close to the payment shown in your loan agreement. Small differences may occur if your lender includes fees, taxes, insurance, or uses a different compounding method.

Example

Loan Details	Value
Loan Amount	$50,000
Interest Rate	6.00%
Loan Term	5 Years
Monthly Payment	$966.64

If the payment looks incorrect, double-check:

• Loan amount
• Interest rate
• Loan term
• Payment frequency

Most calculation errors happen because one of these values was entered incorrectly.

Step 6: Understand Every Column in the Amortization Schedule

The repayment table contains several columns that show how your loan changes after each payment.

Understanding these columns will help you interpret the schedule and monitor your progress.

Payment Number

This column identifies each payment in chronological order.

For example:

Payment Number

1

2

3

4

The final payment number represents the end of your loan.

Payment Date

Shows when each payment is due.

Example:

Payment Number	Payment Date
1	January 1, 2027
2	February 1, 2027
3	March 1, 2027

Many templates calculate future payment dates automatically once you enter the loan start date.

Beginning Balance

This is the amount you owe before making the current payment.

Example:

Payment	Beginning Balance
1	$50,000.00
2	$49,283.36

Each payment reduces this balance until it eventually reaches zero.

Monthly Payment

This column displays the amount you pay during each payment period.

For most fixed-rate loans, this amount remains the same throughout the loan term.

Interest Paid

This shows how much of your payment goes toward interest.

At the beginning of the loan, this value is relatively high.

As the remaining balance decreases, the interest charged each month also becomes smaller.

Principal Paid

This shows how much of your payment reduces the original loan amount.

Unlike the interest portion, the principal portion gradually increases with every payment.

Remaining Balance

After each payment, Excel subtracts the principal portion from the outstanding loan balance.

Eventually, this column reaches:

$0.00

This means the loan has been completely repaid.

Step 7: Add Extra Monthly Payments

One of the biggest advantages of using Excel instead of an online calculator is the ability to experiment with extra payments.

Suppose your regular payment is:

$850

If you decide to pay:

$950

each month, the additional $100 goes directly toward reducing the principal balance.

Because interest is calculated on the remaining balance, paying extra reduces future interest charges and may shorten the repayment period by months or even years.

Many of the templates included in this guide contain an Extra Payment column where you can enter additional amounts.

Example

Regular Payment	Extra Payment	Total Payment
$850	$100	$950

Even relatively small additional payments can produce significant long-term savings, particularly on long-term loans such as mortgages.

Step 8: Save Your Updated Workbook

After entering your loan information, save the workbook so you don’t lose your changes.

Select:

File > Save

or press:

Ctrl + S

If you plan to compare different loan options, save separate copies of the workbook.

For example:

• Mortgage Option A.xlsx
• Mortgage Option B.xlsx
• Refinancing Comparison.xlsx

This makes it easy to compare monthly payments and total interest without overwriting your original calculations.

Step 9: Print the Loan Amortization Schedule

Many borrowers prefer having a printed repayment schedule for budgeting or record-keeping.

Excel makes printing simple.

Select:

File > Print

Before printing, review the preview window.

If necessary, change the following settings:

• Landscape orientation
• Narrow margins
• Fit all columns on one page
• Repeat header row on every page

If the schedule spans several pages, consider exporting it as a PDF so it can be viewed on any device while preserving the layout.

Common Mistakes Beginners Make

Even though Excel performs the calculations automatically, entering incorrect information can produce inaccurate repayment schedules.

Here are some of the most common mistakes and how to avoid them.

Entering the Wrong Interest Rate

Always enter the annual interest rate unless the template specifically asks for a monthly rate.

For example:

Correct:

6.50%

Incorrect:

0.54%

Most templates divide the annual rate by 12 automatically.

Choosing the Wrong Loan Term

Verify whether the template expects the loan term in:

• Years
• Months

Entering 30 when the worksheet expects 360 months will produce incorrect results.

Always read the instructions included with the template.

Editing Formula Cells

Formula cells calculate values automatically.

If you accidentally replace a formula with a number, future calculations will no longer update correctly.

To avoid this, only edit the designated input cells, which are often highlighted with a different background color.

Forgetting to Save Changes

It’s easy to spend time updating loan information and then close the workbook without saving.

Get into the habit of pressing Ctrl + S regularly while making changes.

Ignoring Extra Payments

If you make additional payments but don’t record them in your worksheet, your remaining balance and payoff date won’t reflect your actual progress.

Update the Extra Payment column whenever you pay more than the required amount.

Pro Tips for Using Loan Templates More Effectively

Once you’re comfortable using the template, consider these tips to get even more value from it.

• Create separate worksheets for each loan instead of combining multiple loans in one schedule.
• Use descriptive file names so you can easily identify different loan scenarios.
• Back up your workbook using cloud storage or an external drive.
• Review your repayment schedule periodically to ensure it still matches your lender’s statements.
• Try different extra payment amounts to see how they affect your payoff date and total interest.

These small habits can make your spreadsheet a valuable long-term financial planning tool rather than just a payment tracker.

How to Create Your Own Loan Amortization Schedule in Excel

Although our downloadable templates save time, learning how to build your own loan amortization schedule helps you better understand how loan calculations work. It also gives you complete control over the workbook, allowing you to customize the layout, add new columns, create charts, or include additional calculations.

The good news is that you don’t need to be an Excel expert. Microsoft Excel includes built-in financial functions that perform the complex calculations automatically. Once you’ve entered your loan details and formulas, Excel updates the entire repayment schedule whenever you change the loan amount, interest rate, or loan term.

Step 1: Create a New Excel Workbook

Open Microsoft Excel and create a new blank workbook.

To keep your worksheet organized, reserve the top section for loan details and the lower section for the repayment schedule.

A simple layout might look like this:

Cell	Description
B2	Loan Amount
B3	Annual Interest Rate
B4	Loan Term (Years)
B5	Payments Per Year
B6	Loan Start Date
B7	Monthly Payment

This layout keeps all editable values together, making the worksheet easier to update.

Step 2: Enter Your Loan Information

Enter your sample loan information into the worksheet.

Example

Field	Sample Value
Loan Amount	$250,000
Interest Rate	5.75%
Loan Term	30 Years
Payments Per Year	12
Loan Start Date	January 1, 2027

These values will be used by Excel to calculate every payment.

Step 3: Create the Amortization Table

Below the input section, create the following column headers.

Column	Purpose
Payment Number	Identifies each payment
Payment Date	Due date
Beginning Balance	Balance before payment
Payment	Total payment
Interest	Interest portion
Principal	Principal portion
Extra Payment	Optional additional payment
Ending Balance	Balance after payment

These columns form the core of every loan amortization schedule.

Step 4: Calculate the Monthly Payment

Instead of calculating the payment manually, Excel’s PMT() function does the work for you.

The PMT function calculates the fixed payment required to repay a loan based on a constant interest rate, loan term, and loan amount. It is one of Excel’s most commonly used financial functions for mortgages, auto loans, and personal loans. (support.microsoft.com)

Example Formula

=PMT(B3/B5,B4*B5,-B2)

Formula Breakdown

• B3/B5 converts the annual interest rate into the rate per payment period.
• B4*B5 calculates the total number of payments.
• -B2 represents the loan amount as a negative value so Excel returns the payment as a positive number.

Once you press Enter, Excel calculates the regular payment automatically.

Step 5: Calculate the Interest Portion

Each payment includes an interest component.

Excel’s IPMT() function calculates how much interest is paid during a specific payment period.

Example Formula

=IPMT(B3/B5,A12,B4*B5,-B2)

In this example:

• A12 contains the payment number.
• The remaining arguments refer to the loan details entered earlier.

As the payment number increases, the calculated interest gradually decreases because the outstanding balance becomes smaller.

Step 6: Calculate the Principal Portion

After determining the interest amount, Excel calculates the portion of the payment that reduces the loan balance.

Use the PPMT() function.

Example Formula

=PPMT(B3/B5,A12,B4*B5,-B2)

This function returns the principal amount for each payment period.

Unlike the interest portion, the principal amount gradually increases throughout the repayment period.

Step 7: Calculate the Ending Balance

The ending balance equals:

Beginning Balance − Principal Paid − Extra Payment

Example Formula

=Beginning Balance-Principal-Extra Payment

Each row uses the previous row’s ending balance as the next row’s beginning balance.

By the final payment, the ending balance should equal:

$0.00

or a value very close to zero because of rounding.

Step 8: Fill the Remaining Rows

Once the formulas have been entered for the first payment, you don’t need to recreate them manually.

Select the formula cells.

Move your cursor to the bottom-right corner until it becomes a plus (+) sign.

Drag the formulas downward until you reach the final payment.

Excel automatically adjusts the formulas for every payment period.

Step 9: Format the Worksheet

Formatting makes the worksheet easier to read.

Consider applying the following improvements:

Currency Formatting

Display all monetary values as currency with two decimal places.

Percentage Formatting

Show interest rates as percentages.

Bold Headers

Make the column headers bold so they’re easy to identify.

Cell Borders

Add borders around the repayment table to separate rows and columns.

Highlight Input Cells

Use a light fill color for editable cells so users know where to enter their own loan information.

These formatting changes improve readability without affecting calculations.

Step 10: Test Your Spreadsheet

Before using the worksheet for real financial planning, test it with different loan values.

For example:

Loan Amount	Interest Rate	Loan Term
$15,000	6.00%	3 Years
$50,000	7.25%	5 Years
$250,000	5.50%	30 Years

Check that:

• The monthly payment changes appropriately.
• Interest decreases over time.
• Principal increases over time.
• The ending balance reaches zero.

If all of these conditions are met, your amortization schedule is working correctly.

Understanding the Key Excel Functions

Excel includes several financial functions that make loan calculations much easier. Understanding what each one does will help you customize your templates with confidence.

PMT()

The PMT() function calculates the fixed payment required to repay a loan.

Use it when you want to determine the regular payment amount based on the loan amount, interest rate, and repayment period.

IPMT()

The IPMT() function calculates the interest portion of a specific payment.

Since interest is based on the remaining loan balance, this amount gradually decreases over time.

PPMT()

The PPMT() function calculates the principal portion of a payment.

As the loan balance decreases, the principal portion gradually increases.

Why These Functions Matter

Without these built-in functions, you would have to perform complex financial calculations manually for every payment.

Using PMT(), IPMT(), and PPMT() makes your spreadsheet more accurate, easier to maintain, and simple to update whenever the loan details change.

How to Customize Your Loan Amortization Template

One of the biggest advantages of using Microsoft Excel is that you are not limited to a basic repayment schedule. You can customize the workbook to match your financial goals, add useful calculations, and create visual reports that make it easier to understand your loan.

Whether you’re tracking a single personal loan or comparing multiple mortgage options, a customized workbook can become a powerful financial planning tool.

Below are some of the most useful customizations you can add.

Add an Extra Payment Calculator

Making additional payments is one of the fastest ways to reduce both the total interest paid and the overall loan term.

Instead of creating a separate worksheet, you can add a new column called Extra Payment next to the regular monthly payment.

Example

Payment No.	Monthly Payment	Extra Payment	Total Payment
1	$950.00	$100.00	$1,050.00
2	$950.00	$100.00	$1,050.00
3	$950.00	$250.00	$1,200.00

Whenever you enter an extra payment, the workbook should reduce the remaining balance before calculating the next payment.

This allows borrowers to estimate how much money they can save by paying a little extra each month.

Create a Loan Summary Dashboard

A dashboard gives users a quick overview of their loan without scrolling through hundreds of payment rows.

Place the dashboard near the top of the worksheet or on a separate sheet.

Include important values such as:

Summary Item	Example
Original Loan Amount	$250,000
Remaining Balance	$182,450
Monthly Payment	$1,462.75
Total Interest Paid	$28,925
Total Interest Remaining	$47,860
Loan Payoff Date	January 2057
Payments Completed	84 of 360

This summary helps users understand their current loan status at a glance.

Add Charts to Visualize Loan Progress

Charts make financial data easier to understand.

Excel includes several chart types that work well with amortization schedules.

Remaining Balance Chart

A line chart can show how the loan balance decreases over time.

This helps borrowers visualize their repayment progress.

Principal vs. Interest Chart

A stacked column chart can compare how each payment is divided between principal and interest.

During the early years of a loan, the interest portion is larger.

Later, the principal portion becomes the larger part of each payment.

This visual representation helps users understand why loans become less expensive over time.

Loan Balance Trend

A simple line graph can display the remaining balance after every payment.

Users can quickly identify how extra payments affect the payoff timeline.

Add Conditional Formatting

Conditional formatting automatically highlights important information.

Some useful examples include:

Highlight Late Payments

If you’re manually tracking payments, overdue payments can be displayed with a red background.

Highlight Final Payment

Automatically highlight the final payment row when the remaining balance reaches zero.

Highlight Extra Payments

Use a different background color whenever an extra payment has been entered.

This makes additional payments easy to identify throughout the worksheet.

Add a Loan Comparison Worksheet

Many borrowers compare multiple loans before making a decision.

Create another worksheet where users can compare different loan offers.

Example

Loan Feature	Loan A	Loan B
Loan Amount	$250,000	$250,000
Interest Rate	5.80%	6.15%
Loan Term	30 Years	25 Years
Monthly Payment	$1,467	$1,632
Total Interest	$278,120	$239,615

This worksheet makes it easier to evaluate different lenders or refinancing offers.

Add a Payment Status Column

A payment tracker allows users to record whether each installment has been paid.

Example

Payment Number	Due Date	Status
1	January 1, 2027	Paid
2	February 1, 2027	Paid
3	March 1, 2027	Pending
4	April 1, 2027	Upcoming

This simple addition turns the worksheet into a practical payment management tool.

Add Notes for Each Payment

Sometimes borrowers need to record additional information.

Adding a Notes column lets users document details such as:

• Payment confirmation numbers
• Bank transaction IDs
• Changes in payment amounts
• Refinancing dates
• Payment holidays
• Missed payments

These notes can be useful for personal record-keeping.

Protect Formula Cells

One of the most common problems beginners encounter is accidentally deleting formulas.

To prevent this:

1. Unlock only the input cells where users enter loan information.
2. Protect the worksheet.
3. Allow editing only in the designated input cells.

This helps ensure that the formulas remain intact while still allowing users to customize their loan details.

Common Excel Formula Errors and How to Fix Them

Even well-designed templates can display errors if data is entered incorrectly.

Below are some of the most common issues and their solutions.

Error: #VALUE!

Cause

One or more cells contain text instead of a number.

Solution

Check that the loan amount, interest rate, and loan term contain numeric values only.

Error: #DIV/0!

Cause

Excel is attempting to divide by zero.

This often happens when the payment frequency or loan term is blank.

Solution

Make sure all required input fields have valid values before entering formulas.

Error: #NAME?

Cause

Excel doesn’t recognize a function or named range.

This may occur if a formula has been typed incorrectly.

Solution

Verify that function names such as PMT, IPMT, and PPMT are spelled correctly and that any named ranges used in the workbook are valid.

Monthly Payment Looks Too High or Too Low

Cause

The interest rate or loan term may have been entered incorrectly.

For example:

Entering:

60

instead of:

5

years

or

Entering:

6

instead of:

6%

can produce unrealistic payment amounts.

Solution

Review all loan details before relying on the calculated payment schedule.

Remaining Balance Doesn’t Reach Zero

Cause

A formula may have been accidentally deleted or modified.

Solution

Restore the original formula or replace the affected row using a copy from another correctly functioning row.

Best Practices for Maintaining Your Loan Workbook

To keep your workbook accurate and easy to manage over time, follow these recommendations:

• Save a backup copy before making major changes.
• Update the workbook after every payment.
• Record extra payments as soon as they’re made.
• Compare your spreadsheet with your lender’s statements periodically.
• Keep separate workbooks for different loans to avoid confusion.
• Avoid editing cells that contain formulas unless you understand how they work.
• Store a copy in cloud storage so you can access it from multiple devices and recover it if your computer fails.

By following these practices, your amortization schedule will remain a reliable record of your loan from the first payment to the final payoff.

Excel vs. Google Sheets: Which Is Better for Loan Amortization?

Both Microsoft Excel and Google Sheets can be used to create and manage loan amortization schedules. They support many of the same financial formulas, including PMT(), IPMT(), and PPMT(), making either platform suitable for calculating loan payments.

However, each application has its own strengths. Choosing the right one depends on how you plan to use your loan spreadsheet.

Microsoft Excel

Microsoft Excel is the preferred choice for users who need advanced financial calculations, large workbooks, and professional reporting.

Advantages

• Excellent performance with large spreadsheets.
• Advanced financial functions and data analysis tools.
• Better formatting and printing options.
• Supports charts, dashboards, and conditional formatting.
• Works without an internet connection.
• Compatible with most financial templates.

Best For

• Mortgage calculations
• Business loans
• Financial planning
• Large amortization schedules
• Advanced Excel users

Google Sheets

Google Sheets is a cloud-based spreadsheet application that works directly in your web browser.

Advantages

• Free with a Google account.
• Automatic cloud saving.
• Easy sharing and collaboration.
• Accessible from almost any device.
• Supports most Excel formulas.

Best For

• Personal budgeting
• Collaborative financial planning
• Users who frequently switch devices
• Basic loan calculations

Which One Should You Choose?

If you regularly work with spreadsheets or need advanced customization, Microsoft Excel is the better option.

If your priority is collaboration and accessing your workbook from anywhere, Google Sheets is an excellent alternative.

The good news is that the templates included in this guide can typically be opened in both applications. After importing an Excel workbook into Google Sheets, review the formulas and formatting to ensure everything displays correctly before relying on the calculations.

FAQs

Is an Excel loan amortization schedule free?

Yes. Many loan amortization templates are available at no cost. You can download free templates from our website or use the templates available in Microsoft Excel. Once downloaded, you can edit them as often as needed.

Can I use these templates for a mortgage?

Yes. Mortgage loans are one of the most common uses for an amortization schedule.

Simply enter:

• Loan amount
• Interest rate
• Loan term
• Loan start date

The worksheet will automatically generate the repayment schedule.

Can I use the same template for a car loan?

Absolutely.

Car loans generally use fixed monthly payments, making them ideal for an Excel amortization schedule.

Simply replace the sample loan details with your own.

Can I use the template for a personal loan?

Yes.

Whether you’ve borrowed money for debt consolidation, home improvements, education, or other personal expenses, the template works the same way.

How does Excel calculate monthly loan payments?

Excel uses the PMT() function to calculate the payment amount.

The calculation considers:

• Loan amount
• Interest rate
• Number of payments

Once these values are entered, Excel automatically determines the regular payment required to repay the loan.

What is the difference between principal and interest?

Principal is the amount you originally borrowed.

Interest is the cost charged by the lender for borrowing that money.

Each payment usually includes both principal and interest.

As your balance decreases, the interest portion becomes smaller while the principal portion becomes larger.

Can I make extra payments?

Yes.

Most of our downloadable templates include an Extra Payment column.

Entering additional payments reduces the remaining balance, decreases total interest, and may shorten the repayment period.

Can I change the interest rate later?

Yes.

If your loan has an adjustable interest rate or you refinance your loan, simply update the interest rate in the worksheet.

Excel automatically recalculates the payment schedule based on the new value.

Can I compare multiple loans?

Yes.

One of Excel’s biggest advantages is flexibility.

You can duplicate the worksheet and compare different loans based on:

• Monthly payment
• Total interest
• Loan term
• Payoff date

This makes it easier to choose the most affordable financing option.

Can I print the amortization schedule?

Yes.

Excel provides several printing options.

Before printing, you can:

• Adjust page orientation.
• Scale the worksheet.
• Add headers and footers.
• Export the workbook as a PDF.

This is useful for maintaining physical records or sharing the schedule with family members, financial advisors, or accountants.

Will the templates work in Google Sheets?

Yes.

Most templates can be imported into Google Sheets.

After importing, review the formulas and formatting to make sure everything functions as expected.

Are these templates suitable for beginners?

Yes.

Every template includes sample loan data and clearly labeled input fields.

Simply replace the example values with your own loan details, and the calculations update automatically.

No advanced Excel knowledge is required.

Summary

A loan amortization schedule is one of the most effective tools for understanding and managing debt. Instead of simply knowing your monthly payment, it shows exactly how every payment is divided between principal and interest, how your remaining balance changes over time, and when your loan will be fully repaid.

Throughout this guide, you’ve learned how loan amortization works, how to download and use free Excel templates, how to build your own schedule from scratch, and how to customize your workbook with extra payment tracking, dashboards, charts, and loan comparison worksheets. You also explored Excel’s built-in financial functions, learned how to troubleshoot common formula errors, and discovered best practices for keeping your loan workbook accurate and organized.

Whether you’re managing a mortgage, car loan, personal loan, student loan, or business loan, a well-designed amortization schedule gives you greater visibility into your finances and helps you make informed borrowing decisions. Recording extra payments, reviewing your progress regularly, and comparing different loan options can potentially reduce the total interest you pay and help you reach your financial goals sooner.

Download the free Excel loan amortization templates provided with this guide, replace the sample data with your own loan details, and start tracking your repayments with confidence. With just a few updates to the input fields, you’ll have a personalized repayment schedule that can support smarter budgeting and better long-term financial planning.