How-To-Create-Amortization-Schedule-In-Excel-Someka-Blog-Featured-Image

How to Create an Amortization Schedule in Excel? Step-by-step Guide with Examples

Excel Tips
Updated on January 16, 2024

Whether you’re a lender or borrower, you will definitely need amortization schedules to understand your financial commitments or manage your loans. So, here’s a basic guide for you on how to create an amortization schedule in Excel with ready template or from scratch.

Table Of Content

1. What is an amortization schedule?
2. How to Calculate Loan Amortization with Someka Template?
3. How to Create an Amortization Schedule in Excel from scratch?
4. Why to use Excel for amortization schedules?
5. Who will use Loan Amortization Schedules in Excel?
6. Conclusion

1. What is an amortization schedule?

Basically, amortization schedule is a table that lists every loan payment period in detail.

Here, the payments are divided into principal and interest components. So, this schedule illustrates how the loan balance will drop over time until it is zero. Therefore, it’s a helpful resource for learning how loan payments are made over time.

What are the variables in an amortization schedule?

Key variables in an amortization schedule include loan amount, interest rate, term, and payment frequency.

Loan Amount: Initial sum borrowed
Interest Rate: Cost of borrowing, usually an annual rate
Loan Term: Length of time to repay the loan.
Payment frequency: Metric on how often payments are made. This could be annual, quarterly or monthly.

Beside those, we can also add balloon payment, which is an optional clause for loan contracts.

What is a balloon payment?

A balloon payment is a large, lump-sum payment due at the end of a loan term, after a series of smaller regular payments. Additionally, this type of payment structure is common in certain types of loans, particularly in mortgage and commercial loan agreements.

2. How to Calculate Loan Amortization Schedule with Someka Template?

Firstly, you do not have to bother preparing amortization tables and making comprehensive calculations. We provide you with a ready to use template to calculate your payment schedules in seconds.

Download Someka Loan Amortization Calculator NOW!

This template consists of three main areas: 1. Assumptions, 2. Loan Summary, 3. Amortization table.

– This is a screenshot from Someka’s Loan Amortization Excel Template –

This is very easy to use this template:

Step 1: Fill out your assumptions

You’ll see an assumption table on the top-left side of your worksheet. So, you will be able to fill out this area with:

Loan Amount
Interest Rate
First Payment Date
Payment Frequency
Loan Term
Balloon Payment (Optional)

Step 2: Analyze your loan summary

The template will calculate your summary automatically. So, all you have to do is analyze your results:

Installment Amount
Number of Payments
Total Interest Paid
Total Payment

Thus, this table will let you understand your financial commitments in a clean way.

Step 3: Print your amortization schedule

Finally, the template prepares your amortization schedule automatically. Also, it’s dynamically print-ready. So, just click on CTRL+P and get your schedule printed.

3. How to Create an Amortization Schedule in Excel from scratch?

If you still prefer preparing your own payback schedule, let’s create an amortization schedule in Excel together.

Step 1: Prepare assumptions

Firstly, you should establish your loan assumptions.

Enter the start date, term, interest rate, and loan amount in different cells. Since these numbers are the foundation of your schedule, make sure they are correct.

Step 2: Create amortization table

Make a table with columns for principal, interest, remaining balance.

Thus, this is the main section of your template. So, the payments made on this loan will be monitored in this table.

Step 3: Payment and Installment Calculations

Then, we’ll calculate monthly payments and the principal and interest split using Excel functions. Obviously, a payment period will be represented by each row in the table.

What are the amortization formulas in Excel?

Excel has specific functions for amortization calculations:

PMT Function: Firstly, PMT formula calculates the payment for a loan based on constant payments and a constant interest rate.
IPMT Function: Then IPMT formula calculates the interest portion of each payment.
PPMT function: Finally, PPMT function calculates the principal part of each payment.

Before we dive into the syntax of each formula we want to give an explanation of each item of these formulas:

Rate: The interest rate for each period. If you have an annual rate and are making monthly payments, divide this rate by 12.
Nper: Total number of payments for the loan
Pv: Present value or the total amount of the loan
Per: The period for which you want to find the interest and must be in the range 1 to nper.

Monthly Payments:

Calculating monthly payments for a loan in Excel can be done using the PMT function. The PMT function is used to calculate the payment for a loan based on constant payments and a constant interest rate.

=PMT(rate,nper,pv)

For example:

Assume that you borrow $100,000 with an annual interest rate of 5% and a loan term of 20 years. Then your formula will be as below:

The result will be displayed in the selected cell. It’s important to note that the PMT function returns a negative number, representing an outgoing payment.

This will give you the monthly payment amount. Remember, this includes both principal and interest but does not include other costs like insurance or taxes if those are part of your loan arrangement.

Interest for each payment

So, we’ll calculate interest for each payment with Excel IPMT function:

=IPMT(rate, per, nper, pv)

Let’s continue with our example:

Similarly, IPMT formula also gives an negative number as a payment.

Principal Payment

We’ll use PPMT function built-in Excel to calculate the principal payments for each period.

= PPMT(rate, per, nper, pv)

This function is very similar to the IPMT in terms of syntax.

Please note that we can also calculate principal payment by subtracting monthly interest payment from the monthly total payment.

Remaining Balance on amortization schedule in Excel

This is not a standard Excel function, but you can calculate it by subtracting the principal paid to date from the original loan amount. While using minus sign, we should also turn the negative principal payments to positive by multiplying by -1 coefficient.

Finally, copy all the formulas in the first row until the end of your table.

Please be careful about the absolute cell references in Excel calculations. Using dollar sign ($) in Excel is an important skill.

That’s all. Your amortization table is ready.

Step 4: Calculate loan summary in Excel amortization schedule

Lastly, we’ll summarize the total amount paid over the course of the loan period at the end of the table.

Installment Amount = The total of the monthly payments. Take the sum of Interest and Principal payment of any period.
Number of Payments = Total payment number. Multiply the loan terms in years by 12 for a monthly frequency.
Total Interest Paid = Take the Sum of Interest Rate Column.
Total Payment = Get the sum of all Interest and Principal Payments.

This is the end of our step by step guide. So, now you know how to create an amortization schedule in Excel.

4. Why to use Excel for amortization schedules?

Excel is a powerful tool for creating amortization schedules. Let’s summarize the benefits of Excel as a list for creating amortization schedules:

Flexibility
Precision
Built-in functions to simplify complex calculations
Widely accessible
User-friendly
Editable and customizable
Offline access
And much more…

Thus, these factors make Microsoft Excel ideal for both personal and professional financial planning, including amortization calculations.

If you prefer an online tool, then you can also download Loan Amortization Google Sheets Template from Someka portfolio.

5. Who will use Loan Amortization Schedules in Excel?

All parties involved in borrowing, lending or any other credit agreement will need an amortization table to understand the financial commitments.

Among the common users, we can say:

Lenders
Credit payers
Mortgage payers
Student loan borrowers
Car or other commodity credit lenders and borrowers

If you want to calculate the amortization schedule of your mortgage specifically, then you can also use our Mortgage Calculator Template:

– This is the main section of Someka’s Mortgage Calculator Excel Template –

Besides, there might be other schedules that you want to build in excel, such as rotation schedule, league schedule, task schedule etc.

Please check out our Excel Schedule Guideline to learn more about creating different types of schedules in spreadsheets.

6. Conclusion: How to create amortization schedule in Excel?

In this article, we have explained how to create an amortization schedule in Excel with step-by-step guiding. So, you’ll find two different options: Firstly, you can make your table from scratch. Or, you can download the Someka Loan Amortization Excel Template and do not bother yourself with formatting or calculations: