How to Create a Loan Amortization Schedule in Excel
Step 1: Open a new Spreadsheet and Define Input Cells
Open a new spreadsheet and add the below components of loans in the mentioned Cells.
- C2 – Annual Interest Rate
- C3 – Loan Term in Years
- C4 – Number of Payments Per Year
- C5 – Loan Amount
Please refer to the below image for easy understanding.
Step 2: Create an Amortization Table with Labels
Now create an amortization table with the labels (Period, Payment, Interest, Principal, Balance) in A7:E7. Now enter the series number equal to the total number of payments (1-12), for this simply enter 1,2 & 3 in A8, A9 & A10 and drag the cell until A19.
Step 3: Calculate Total Payments (PMT Formulae)
To calculate total payments use PMT Function PMT (rate, nper, pv, [fv], [type])
Rate: Calculate the interest rate per payment period by dividing the annual interest rate by the number of payment periods within a year.
Nper: Determine the total number of payment periods by multiplying the number of years by the number of payment periods per year.
pv: Enter the Total Loan Amount
PMT FORMULAE: =PMT($C$2/$C$4, $C$3*$C$4, $C$5) in B8 and then drag the column until A19.
After entering the formulae in B8 and till B19, you will see the same payment amount for all the periods.
Step 4: Calculate interest (IPMT formulae)
Now find out the interest of each periodic payment by using the IPMT Function formulae in C8.
IPMT Formulae: =IPMT($C$2/$C$4, A8, $C$3*$C$4, $C$5)
After entering the formulae in C8, drag the column until C19
Step 5: Calculate Principal (PPMT Formulae)
Now calculate the using the PPMT Function in D8.
PPMT Formulae: =PPMT($C$2/$C$4, A8, $C$3*$C$4, $C$5)
After entering the formulae in D8, drag the column until D19.
Step 6: Calculate the Remaining Balance
Now to calculate the Remaining Balance for each period we’ll apply two separate formulas.
To determine the balance after the initial payment, located in cell E8, sum the loan amount (C5) and the principal of the first period (D8): =E8+C5+D8
Note: since the loan amount is represented as a positive number and the principal as a negative number, the latter is effectively deducted from the former.
For the second period onwards, find the balance by adding the previous balance to the principal for the current period: =E8+D9
Now drag down the column till E19, and you will get the periodic remaining balance figures.
Important Note: As shown in the image, by default, the values are visually represented with a red font and enclosed within parentheses. However, if you prefer to display all results as positive numbers, you can easily do this by adding a minus sign before using the PMT, IPMT, and PPMT functions.
Step 7: Make a Loan Summary
Now create a loan summary to see the exact status of your loan. Create a Loan Summary and Add the Below components in the mentioned Cells.
- Total Payments (E2): =-SUM(B8:B19)
- Total Interest (E3): =-SUM(C8:C19)
How to Make a Loan Amortization Schedule in Excel
Creating a loan amortization schedule in Excel is a crucial skill for anyone looking to manage their finances effectively. Whether you’re a small business owner, a financial advisor, or just managing personal finances, understanding how to break down your loan payments can provide clarity and help you plan better.
In this article, you will learn how to make a loan amortization schedule in Excel. This step-by-step tutorial will help you calculate your monthly payments, interest, and principal, giving you a clear view of your loan repayment plan. With these easy-to-follow instructions, you’ll be able to create a comprehensive loan amortization schedule in no time, ensuring you stay on top of your financial commitments. Let’s get started and simplify your loan management with Excel!