Calculate Amortization

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:

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.

You will get a window and select Currency from the Category and choose your preferred 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:

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:

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:

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.

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.