How To Calculate Loan Amortization Schedule In Excel

Creating a Loan Amortization Schedule in Excel

Understanding the Components

Step-by-Step Guide

Important Considerations

Example Formulas (Assuming B1-B4 are Loan Amount, Annual Interest Rate, Loan Term, Payments per Year respectively)

Home » Excel Templates » How To Calculate Loan Amortization Schedule In Excel

Tuesday, November 4th 2025. | Excel Templates

How To Calculate Loan Amortization Schedule In Excel - There are a lot of affordable templates out there, but it can be easy to feel like a lot of the best cost a amount of money, require best special design template. Making the best template format choice is way to your template success. And if at this time you are looking for information and ideas regarding the How To Calculate Loan Amortization Schedule In Excel then, you are in the perfect place. Get this How To Calculate Loan Amortization Schedule In Excel for free here. We hope this post How To Calculate Loan Amortization Schedule In Excel inspired you and help you what you are looking for.

tables calculate loan amortization schedule excel templatelab

Here’s an HTML-formatted explanation of how to calculate a loan amortization schedule in Excel: “`html Loan Amortization Schedule in Excel

An amortization schedule is a table detailing each periodic payment on an amortizing loan, breaking down the payment into principal and interest. Creating one in Excel is a relatively straightforward process that can provide valuable insights into your loan.

Before you start building the schedule, it’s crucial to understand the key components that define a loan:

Loan Amount (Principal): The total amount borrowed.
Interest Rate: The annual interest rate charged on the loan, expressed as a percentage. This needs to be converted to the interest rate per period.
Loan Term (Number of Periods): The total number of payment periods (e.g., months) required to repay the loan.
Payment Amount: The fixed amount paid each period. You can calculate this using Excel’s PMT function.
Beginning Balance: The outstanding loan balance at the beginning of each period.
Interest Paid: The portion of each payment that covers the interest accrued during the period.
Principal Paid: The portion of each payment that reduces the outstanding loan balance.
Ending Balance: The outstanding loan balance at the end of each period.

Set Up Your Spreadsheet:

Start by labeling the following input fields in your Excel sheet. Choose a clear, organized layout.

Loan Amount: (e.g., in cell B1)
Annual Interest Rate: (e.g., in cell B2)
Loan Term (in Years): (e.g., in cell B3)
Payments per Year: (e.g., in cell B4 – usually 12 for monthly payments)

Then, create headers for your amortization schedule table. This typically includes:

Period: Payment number (e.g., 1, 2, 3…)
Beginning Balance: Loan balance at the start of the period.
Payment: Total payment amount for the period.
Interest Paid: Interest portion of the payment.
Principal Paid: Principal portion of the payment.
Ending Balance: Loan balance at the end of the period.

For example:

You’ll fill in the values in column B, and then the amortization schedule starts on row 6.

Enter Loan Details:
Populate the input fields with the relevant loan information. For example:
- Loan Amount: $100,000 (enter 100000 in B1)
- Annual Interest Rate: 5% (enter 0.05 in B2 or 5% with the percentage formatting)
- Loan Term (in Years): 30 (enter 30 in B3)
- Payments per Year: 12 (enter 12 in B4)
Calculate the Periodic Payment:
Use Excel’s PMT function to calculate the fixed payment amount for each period. The formula is:

=PMT(rate, nper, pv, [fv], [type])

Where:
- rate is the interest rate per period (Annual Interest Rate / Payments per Year).
- nper is the total number of payments (Loan Term in Years * Payments per Year).
- pv is the present value, or the loan amount.
- [fv] is the future value (optional, defaults to 0). This is the value you want to have after the last payment (usually 0 for a loan).
- [type] is when payments are due (0 for end of period, 1 for beginning of period; defaults to 0).
In our example, assuming you enter the above values in cells B1-B4, the formula in a cell (e.g., B5) would be:

=PMT(B2/B4, B3*B4, B1)

This will return the *negative* of the payment amount, as the PMT function considers payments as cash outflow. To display the payment as a positive number, either put a negative sign in front of the PV or multiply the whole function by -1:

=-PMT(B2/B4, B3*B4, B1) or =PMT(B2/B4, B3*B4, -B1)

Format the cell containing this formula as currency.
Populate the Amortization Schedule:
1. Period Column: In column A, start with “1” in A7. In A8, enter the formula =A7+1. Drag this formula down to create the sequence of periods for the entire loan term (e.g., to row 366 for a 30-year loan with monthly payments – 30 * 12 = 360). You only need to create the rows for the total number of payments.
2. Beginning Balance Column: In B7, enter the initial loan amount: =$B$1. The dollar signs make this an absolute reference, meaning it won’t change when you drag the formula down. In B8, enter the formula to reference the previous period’s ending balance: =F7. Drag this formula down to the last period.
3. Payment Column: In C7, enter the calculated payment amount using an absolute reference to the PMT function we calculated in Step 3. For instance, if the PMT formula is in B5, enter =$B$5. Drag this formula down to the last period. Using absolute references ($) makes sure you always use the same payment amount.
4. Interest Paid Column: In D7, calculate the interest portion of the payment using the formula: =B7*($B$2/$B$4). This multiplies the beginning balance by the periodic interest rate. The dollar signs are again used to create absolute references to the interest rate and payments per year, which are in cells B2 and B4, respectively. Drag this formula down.
5. Principal Paid Column: In E7, calculate the principal portion of the payment by subtracting the interest paid from the total payment: =C7-D7. Drag this formula down.
6. Ending Balance Column: In F7, calculate the ending balance by subtracting the principal paid from the beginning balance: =B7-E7. Drag this formula down.
Verify Accuracy:
Check the last row of your schedule. The ending balance should be close to zero (or zero if Excel’s rounding doesn’t cause a tiny remaining amount). If it’s significantly off, double-check your formulas and input values.
Format Your Schedule:
Format the cells as currency where appropriate. You can also add borders, shading, and adjust column widths to improve readability.

Rounding Errors: Due to Excel’s floating-point arithmetic, you might encounter slight rounding errors, particularly in the final periods. These are usually insignificant. If needed, you can adjust the last payment to force the ending balance to zero.
Extra Payments: This schedule assumes fixed payments. If you make extra principal payments, you’ll need to modify the schedule accordingly. This would typically involve manually adjusting the principal paid, interest paid, and ending balance for the periods after the extra payment.
Variable Interest Rates: This schedule is designed for fixed-rate loans. For variable-rate loans, you’ll need to update the interest rate in your input fields whenever the rate changes, which will require adjustments to the payment amount and amortization schedule. You may need to create multiple schedules or add complexity to your formulas.
Error Checking: It’s always a good idea to double-check your formulas and input values to ensure the accuracy of your schedule.

Loan Amount

Annual Interest Rate

Loan Term (Years)

Payments per Year

Beginning Balance

Interest Paid

Principal Paid

Ending Balance

Payment (B5): =-PMT(B2/B4, B3*B4, B1)
Period 1 Beginning Balance (B7): =$B$1
Period 2 Beginning Balance (B8 onwards): =F7
Payment (C7 onwards): =$B$5
Interest Paid (D7 onwards): =B7*($B$2/$B$4)
Principal Paid (E7 onwards): =C7-D7
Ending Balance (F7 onwards): =B7-E7

By following these steps, you can create a comprehensive and informative loan amortization schedule in Excel. This schedule will help you understand how each payment is allocated between interest and principal, and how your loan balance decreases over time.

“`

604×707 loan amortization schedule excel step step tutorial from www.excel-easy.com

2255×2190 loan amortization schedule spreadsheet db excelcom from db-excel.com

1024×865 loan amortization schedule excel ordnur from ordnur.com

1600×1200 prepare amortization schedule excel steps from www.wikihow.com

691×799 amortization schedule template excel printable schedule template from www.printablescheduletemplate.com

562×420 create loan amortization schedule excel extra payments from www.ablebits.com

412×214 loan amortization schedule from www.exceltip.com

1865×2500 tables calculate loan amortization schedule excel templatelab from templatelab.com

How To Calculate Loan Amortization Schedule In Excel was posted in November 4, 2025 at 7:59 am. If you wanna have it as yours, please click the Pictures and you will go to click right mouse then Save Image As and Click Save and download the How To Calculate Loan Amortization Schedule In Excel Picture.. Don’t forget to share this picture with others via Facebook, Twitter, Pinterest or other social medias! we do hope you'll get inspired by ExcelKayra... Thanks again! If you have any DMCA issues on this post, please contact us!