How to Create an Amortization Table in Excel

To keep track of how much you owe on a loan or mortgage over time, you may use a table called an amortization schedule. It details the principal paid, the interest paid, and the amount left after each payment. 

What is an Amortization Schedule?

An amortization schedule shows how much loan principal and interest you’ve paid off. Once payment is made, the outstanding balance will reflect the amount still owed.

How to Create an Amortization Schedule in Excel

Follow these steps to create the amortization schedule:

Create column A labels

Start by opening Excel and creating a new spreadsheet for your amortization schedule. Then, fill out column A with the appropriate headings to better manage your data. After that, fill in the following information in every individual cell:

Fill in A1 with the loan amount.

Fill in A2 with the interest rate.

Fill in A3 with the months. 

Fill in A4 with the payments.

Fill out column B with loan details

The “B” column is where you’ll want to input your loan details. Fill in the information in cells B1 through B3, and leave cell B4 blank. Ensure the number you provide for “Months” is the same as the months for the loan. If the loan is for three years, you will write “36” to indicate that there will be 36 payments. Interest rates should also be a numeric amount, such as 10.2%.

Calculate payments in B4

To determine the amount to be paid in B4, select it and then put it in this formula:

=ROUND(PMT(₹B₹2/12,₹B₹3,-₹B₹1,0), 2)

When the formula is transferred to other cells in the spreadsheet, the dollar signs indicate that the formula is still referring to the original cells.

Formulate the column headings in row 7

We will now use row 7 to enter column headings. Fill in the following headings as the cell labels:

Fill in A7 with the period.

Fill in B7 with the beginning balance.

Fill in C7 with the payment.

Fill in D7 with the principal.

Fill in E7 with your interest.

Fill in F7 with the cumulative principal.

Fill in G7 with the cumulative interest.

Fill in H7 with the ending balance.

Fill in the period section

Place your due dates in the “Period” section. You should enter the loan’s initial payment month and corresponding year in A8. You can format the date in various ways, by including the months, days, and years.

To make a cell your active selection, click it. Now, choose all cells until A368, and hold down the control key while dragging the pointer. Select “Last Month” by clicking the little lightning bolt in the cell’s lower right corner.

Fill in the information from B8 to H8

Then, enter the initial amount of the loan in B8 and the remaining balance in H8. Select C8, then enter “=₹B₹4” and hit Enter. To find out how much interest you owe at the beginning of the period, insert the following formula into cell E8:

=ROUND(₹B8*(₹B₹2/12), 2).

Subtract the interest from the total payment in cell C8, and enter the result in cell D8 using the following calculation.

=₹C8-₹E8

Then, enter the following formula into column H8 to get the amount by which the principal payment for the specified period differs from the initial loan balance:

=₹B8-₹D8

Fill in cells B9 to H9.

Fill in the remaining cells in the schedule, from B9 to H9. To begin, go to cell B9 and type “=₹H8” before hitting enter. Then, paste the contents of cells C9, D9, and E9 into cells C8, D8, and E8. Then, go over to H9 and duplicate its contents there.

To calculate the total amount of principal you’ve paid, apply the following formula in cell F9:

=₹D9+₹F8

In cell G9, use the correct formula, which is:

=₹E9+₹G8

Use the crosshairs to fill up the remaining schedule

Mark the range from B9 to H9 to finish your amortization plan. Hover the mouse pointer over the bottom right corner of the selected region. Doing this will transform it into a crosshair icon.

When the crosshair cursor is in place, drag it down to row 367 to automatically fill in all the cells in that row. By doing so, you generate an amortization plan that you can use to:

• Handle loan payments
• Make early payments
• Measure progress
• Collect other useful data

Benefits of Using a Loan an Amortization Schedule

Utilizing Excel’s built-in amortization plan has several advantages.

Ability to save

You can save an amortization schedule created in Excel at any time to preserve its current state. This guarantees that the data you receive is current and correct, allowing you to return to your day.

Offline usability

Suppose you need to make changes to your amortization plan while you’re offline. In that case, Excel has you covered.

Extra payment feature

You can easily include extra payments into an Excel amortization plan, which is a major perk. You may easily make changes and see the monthly amount that must be increased.

Interest payment management

An amortization schedule clarifies your interest payments. Suppose you have questions about your interest payments. In that case, you may use this calculator to contact your loan service.

User-friendliness

The process of creating an amortization plan in Excel is straightforward. That’s because of the program’s user-friendliness. This makes it possible for anybody to make a budget or expense report for their own use.

Summing up

An amortization schedule can be very handy if you’re considering a loan. Also, it can help you manage your finances effectively. In this piece, we learned how to make a loan amortization schedule and how its various parts fit together. 

Making an amortization schedule is very easy. You just need to know the basics of Excel, and you’re good to go. Furthermore, we are confident that you will be able to create effective schedules with the assistance of this guide.

Are you looking for an instant personal loan? Get a quick personal loan with Piramal Finance; apply now!