✦Template Printable Ideas Insights

Creating Loan Amortization Schedule Excel: Step-by-Step

By

Creating a loan amortization schedule in Excel can help you understand how much you'll pay each month and the breakdown of interest and principal payments over the life of your loan. Here's a step-by-step guide to create an amortization schedule using Excel.

Before you begin, ensure you have the following details about your loan: principal amount, interest rate, loan term (number of years), and the frequency of payments (monthly, quarterly, etc.).

Setting Up Your Amortization Schedule

Open a new Excel workbook and save it with a relevant name, such as "Loan Amortization Schedule".

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 Calculator: Excel Template & Schedule
Loan Amortization Calculator: Excel Template & Schedule
Create a loan amortization schedule in Excel (with extra payments)
Create a loan amortization schedule in Excel (with extra payments)

In the first row, starting from column A, enter the following headers: 'Period', 'Start of Period Balance', 'Principal This Period', 'Interest This Period', 'Payment This Period', and 'End of Period Balance'.

Calculating Payment Amount

First, you need to calculate your loan payment amount using the following formula in Excel: PMT(rate, nper,PV,(FVFlash)), where:

Loan Amortization Schedule for Excel
Loan Amortization Schedule for Excel
Loan Amortization Schedule in Excel
Loan Amortization Schedule in Excel
loan amortization schedule excel
loan amortization schedule excel
  • rate: Interest rate per period (annual rate divided by the number of periods per year)
  • nper: Total number of payment periods (loan term in years times the number of periods per year)
  • PV: Present Value, or loan amount (the principal amount you're borrowing)
  • FV: Future Value, usually set to Flash Finance Value (0 if the loan is fully amortized), which cancels out to -(FVFlash)

Format the result as currency with two decimal places and name this cell as 'Payment Amount'.

Filling in the Schedule

Now, fill in the cells under each header. You can use formulas and Excel's fill handle for quick population of the schedule.

  • 'Period': Enter '1' in A2, then drag the fill handle down to copy the formula =A2+1 to get subsequent periods.
  • 'Start of Period Balance': Use the formula =IF(A2=1, LoansAmount, C1) to populate all cells except the first one with the previous period's 'End of Period Balance'.
  • 'Principal This Period': Use the formula =MAX(Payment Amount-PPi), where PPi is the interest in the current period. This ensures that the principal portion of your payment doesn't exceed the remaining loan balance.
  • 'Interest This Period': Use the formula =Start of Period Balance*Rate/nper.
  • 'Payment This Period': Enter your calculated 'Payment Amount' in each cell.
  • 'End of Period Balance': Use the formula =Start of Period Balance-Principal This Period.

Format the 'Period' column as currency with two decimal places.

Loan Amortization Schedule Calculator | Plan Projections
Loan Amortization Schedule Calculator | Plan Projections
How to Make Loan Amortization Schedule in Excel - ORDNUR
How to Make Loan Amortization Schedule in Excel - ORDNUR
Get the Loan Amortization Schedule Template for Google Sheets
Get the Loan Amortization Schedule Template for Google Sheets
Create an Easy Loan Amortization Schedule in Excel & Google Sheets
Create an Easy Loan Amortization Schedule in Excel & Google Sheets
Loan Amortization with Microsoft Excel
Loan Amortization with Microsoft Excel
Amortization Schedule Example | Template Business
Amortization Schedule Example | Template Business

Adjusting for Extra Payments or Early Payoff

To account for extra payments or early payoff, adjust your 'Payment Amount' and the 'Period' column accordingly. You can use the Goal Seek tool in Excel to find the loan term required for a specific final balance.

Regularly reviewing and updating your amortization schedule will help you stay on track with your loan repayment plan. By understanding how your loan amortizes, you can make informed decisions about paying down your loan more quickly or changing your payment strategy.

Loan Amortization Schedule Excel Template: Debt Payoff Calculator (Digital Download)
Loan Amortization Schedule Excel Template: Debt Payoff Calculator (Digital Download)
Car Loan Amortization Schedule in Excel with Extra Payments
Car Loan Amortization Schedule in Excel with Extra Payments
DM102: Debt Reduction
DM102: Debt Reduction