How to calculate amortization?

You might be already familiar with 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 is not contributed fully 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, number of payments, the amount you pay towards principal and loan and so on. Excel offers a number of financial functions that help you calculate amortization easily.

Calculate Amortization

Step 1. Open Excel 2010 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 them bold. Now your screen will look like this:

Amortization data

Step 2. 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.

Amortization format cells

You will get a window and select Currency from the Category and choose your preferred currency.

Amortization currency

Click OK. Similarly format the cell B4 to include percentage. Instead of Currency, choose Percentage from the Category and click OK.

Step 3. Click the cell B5 and in the formula bar, enter the formula =PMT(B4/12,B3*B2,-B1) and press Enter key. Now your screen will look like this:

Amortization PMT function

You could find that the amount you need to pay per installment is $1,173.7.

Step 4. 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 in Step 2.

Step 5. 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:

Amortization IPMT function

Step 6. 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:

Amortization copy formulas

Step 7. 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 will be complete.

Amortization calculations

If you analyze the values, you could find that the ending balance at the end of 18th period is $0, 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), that is $1126.6 more than your loan amount.