In this tutorial, we will walk through the process of creating a loan amortization schedule in Excel. An amortization schedule is a table that shows the breakdown of principal and interest payments over the life of a loan. By following this tutorial, you will learn how to use Excel’s built-in financial functions to create a schedule that can be used to track payments and plan for future payments.
In this example, we want to take a loan of $300,000. To repay the loan, we will pay an amount every month for 3 years with an annual interest rate of 6%. To get the amount for the payment each month, we will create an amortization schedule. To do this, we will use the PMT, IPMT, and PPMMT functions. Here’s a guide to help you create your own schedule:
- In Excel, set up your loan information in cells A2, A3, and A4 for the loan amount, number of years, and interest rate, respectively. Enter the corresponding values in the cells in column B.
- In row 7, create the headers for the schedule by entering the following labels in the corresponding cells: “Payment” in cell A7, “Amount” in cell B7, “Principal” in cell C7, “Interest Amount” in cell D7, and “Balance” in cell E7
- Under “Payment”, enter an array of numbers to represent the months of each payment. (e.g., 1 for the 1st month, 2 for the 2nd month…)
- To calculate for the Amount, we will use the PMT function, this function takes in the syntax: PMT(interest rate per period, number of periods, present value of the loan). In cell B8, we will enter the formula =PMT($B$4/12,$B$3*12,-$B$2) to calculate the monthly payment.
Note: Since we want to know the payment in months, we have to divide the annual interest by 12 months, and convert the years to months by multiplying by 12.
- To calculate for the Principal, we will use the PPMT function, this function takes in the syntax: PPMT(interest rate per period, period, number of periods, present value of the loan, future value, type). In cell C8, we will enter the formula =PPMT($B$4/12,A8,$B$3*12,$B$2) to calculate the portion of the first payment that goes towards paying off the principal.
- To calculate for the Interest Amount, we will use the IPMT function, this function takes in the syntax: IPMT(interest rate per period, period, number of periods, present value of the loan, future value, type). In cell D8, we will enter the formula =IPMT($B$4/12,A8,$B$3*12,$B$2) to calculate the portion of the first payment that goes towards paying the interest.
- To calculate for the Balance we will use the formula =B2+C8 in cell E8, this will give us the remaining balance of the loan after the first principal amount.
- Lastly, for the remaining balance of the other payments, we will reference the previous balance less the current principal amount.
In conclusion, a loan amortization schedule is a useful tool for tracking and planning loan payments. By using the PMT, PPMT, and IPMT functions in Excel, you can easily create a schedule that shows the total monthly payment, the portion of the payment that goes towards the principal, the portion that goes towards interest, and the remaining balance of the loan.