You might already be familiar with the loan payment system. It is obvious that your loan principal decreases with each loan payment you make over the life of your loan. The loan payment you make does not fully contribute towards your principal amount.
Instead, a portion of your payment goes towards your principal and the remaining portion goes towards the interest of the loan. The process by which your loan principal decreases over the life of your loan is known as amortization.
Before taking a loan, you should be very clear about the total amount you need to pay to pay off the loan (principal + interest), the loan payment per installment, the number of payments, the amount you pay towards principal and the loan, and so on.
Excel offers a number of financial functions that help you calculate amortization easily.
Calculate the Amortization
Open Excel and save the file as amortization.xlsx. Type "Loan Amount" in A1, "Loan Term (Years)" in A2, "Payments Per Year" in A3, "Annual Interest Rate" in A4 and "One Time Payment" in A5. Also type "Period" in A7, "Beginning Balance" in B7, "Payment" in C7, "Principal" in D7, "Interest" in E7, and "Ending Balance" in F7.
You should not enter the double quotes when you type in the data. You can format these texts and make it bold. Now your screen will look like this:
Enter the details of your loan in cells B1 to B4, loan amount in B1 (say 20,000), loan term in years in B2 (say 1.5), number of payments per year (say 12) in B3 and interest rate in B4 (say 7). You can format the cells B1 and B5 to include your preferred currency. Right-click the cell B1 and select Format Cells.
You will get a window and select Currency from the Category and choose your preferred currency.
Click OK. Similarly, format cell B4 to include a percentage. Instead of Currency, choose Percentage from the Category and click OK.
You could find that the amount you need to pay per installment is $1,173.7.
As your loan period is 1.5 years (18 months), enter 1 to 18 in cells A8 to A25. Format the cells B8 through F25 to contain currency by following the steps explained earlier.
PPMT and IPMT formulas
The beginning balance at the start of the loan period will be the loan amount. So, enter "=B1" in cell B8 and press Enter key. In cell C8, enter =$B$5 and press Enter as it is the one time payment (payment per installment).
Click cell D8 and enter =PPMT($B$4/12,A8,$B$2*$B$3,-$B$1) and press Enter.
Click cell E8 and enter =IPMT($B$4/12,A8,$B$2*$B$3,-$B$1) and press Enter.
Click cell F8 and enter =B8-D8 and press Enter.
Now your screen will look like this:
Next you need to set the beginning balance for the second period. Of course, the begging balance of the second period would be the ending balance of first period. Click cell A9 and enter "=F8" and press Enter. Copy the formulas from C8 to F8 and paste them in C9 to F9.
Now your screen will look like this:
Select cells A9 through F9, mouse over the bottom right corner of the selection to receive a crosshair cursor and then click and drag the selection down to row 25. Release the mouse button.
Now the amortization table is completed.
If you analyze the values, you could find that the ending balance at the end of the 18th period is $0, meaning that the loan is completely paid off. As the loan period advances, your contribution towards the principal increases and your contribution towards the interest decreases.
Moreover, when you take a loan of $20,000 for a period of one and a half years, you are actually paying $21126.6 (1173.7 * 18), which is $1126.6 more than your loan amount.
The amortization schedule is ready.