✦Template Printable Ideas Insights

Mastering Excel: Step-by-Step Guide to Create Amortization Schedules

By

Creating an amortization schedule in Excel is a crucial step in managing your financial obligations, whether it's for a mortgage, car loan, or business loan. This step-by-step guide will walk you through the process, ensuring that you understand each step and have a well-structured amortization schedule at the end.

To begin, open a new or existing Excel workbook and ensure you have Excel 2010 or later for the best functionality. We will create the amortization schedule for a 30-year mortgage with a $200,000 loan, 6% interest rate, and $1,000 annual payment.

Setting Up the Amortization Schedule

This section will focus on setting up the basic structure of the amortization schedule, including the labels and formulas for the columns.

Loan Amortization Schedule for Excel
Loan Amortization Schedule for Excel
Create an Easy Loan Amortization Schedule in Excel & Google Sheets
Create an Easy Loan Amortization Schedule in Excel & Google Sheets
Create a loan amortization schedule in Excel (with extra payments)
Create a loan amortization schedule in Excel (with extra payments)

In cell A1, enter "Loan Amortization Schedule" (without the quotes) and format it as a header. In row 2, enter the following labels, starting from cell A2:

  • Period (in years and months)
  • Beginning Balance
  • Payment
  • Interest
  • Principal
  • Ending Balance

Creating the Year-Month Column

In cell B3, enter "30", and in cell B4, enter "29". Then, select these two cells and drag the fill handle down to cell B305 to create the period column (B3:B305).

How to build an Amortization table in EXCEL (Fast and easy) Less than 5 minutes
How to build an Amortization table in EXCEL (Fast and easy) Less than 5 minutes
Loan Amortization Schedule for Excel
Loan Amortization Schedule for Excel
Loan Amortization Schedule in Excel
Loan Amortization Schedule in Excel

To display both the year and month, select cell B3 and click 'Format' > 'Number' > 'Custom'. Enter "0\" \"yyyy\" yyyy; ""[@[B3]]" and click 'OK'.

Calculating the Loan Payment

To calculate the annual loan payment, use the loan payment formula in cell C3: `=PMT(6%/12,12*30,-200000,0,1)`. This formula takes into account the interest rate, loan term, loan amount, and whether your payment is at the beginning or end of the period.

The result should be -$12,361.28, with the negative sign indicating it's an outgoing payment.

loan amortization schedule excel
loan amortization schedule excel
Loan Amortization Calculator: Excel Template & Schedule
Loan Amortization Calculator: Excel Template & Schedule
the calorie amount schedule is shown in green and yellow, with numbers for each month
the calorie amount schedule is shown in green and yellow, with numbers for each month
How to Make Loan Amortization Schedule in Excel - ORDNUR
How to Make Loan Amortization Schedule in Excel - ORDNUR
Loan Amortization Schedule Calculator | Plan Projections
Loan Amortization Schedule Calculator | Plan Projections
Get the Loan Amortization Schedule Template for Google Sheets
Get the Loan Amortization Schedule Template for Google Sheets

Calculating the Amortization Schedule

This section will focus on calculating the interest, principal, and ending balance for each period using formulas.

Calculating Interest

In cell D3, enter the formula for the interest: `=C3*(6%/12)` to calculate the monthly interest. Then, drag the fill handle down to cell D305.

To format the results as currency, select cells D3:D305, click 'Home' > 'Number', select the currency format, and choose your preferred currency symbol.

Amortization Schedule with Irregular Payments in Excel (3 Cases) - ExcelDemy
Amortization Schedule with Irregular Payments in Excel (3 Cases) - ExcelDemy
How To Create an Amortization Table In Excel
How To Create an Amortization Table In Excel
Amortization Schedule Template Excel & Google Sheets
Amortization Schedule Template Excel & Google Sheets

Calculating Principal and Ending Balance

In cell E3, enter the formula for the principal: `=C3-D3` to calculate the principal paid each period. Drag the fill handle down to cell E305.

To calculate the ending balance, in cell F3, enter the formula: `=F2+E3` and drag it down to cell F305. In cell F2, enter the loan amount: `$200,000`.

Congratulations! You have now created a comprehensive 30-year mortgage amortization schedule in Excel. Regularly reviewing this schedule can help you understand how much interest and principal you're paying, and when the loan will be paid off.

To take your skills further, consider exploring more features in Excel like conditional formatting, data validation, and pivot tables to enhance your amortization schedule or create entirely new financial tools.