You need one amortization table When, for example, you have requested a loan with a fixed interest rate, you want to know what that interest is that is applied and as you make the payments for an obvious question the principal is reduced, since all this is shown in a table.
And you can view it from a schedule with all the details of your payments, therefore you can know the money that is destined to pay the interest and the one that goes to the main balance. That is why it is so important that you know …
How to create an amortization table
The amortization table You can create it in an Excel document by following these steps:
Step 1: Open an Excel spreadsheet.
Step 2: In cell A1 write Loan amount, in cell A2 write Interest rate, in cell A3 write Months and in cell A4 write Payments.
Step 3: From cells B1 to B4, reserve them to place the information related to your loan.
Step 4: When you fill in the cells in column B write the interest rate as a percentage.
Step 5: In cell B4 you will calculate your payments like this in the formula bar: = PMT ($ B2 / 12/12, $ 3, – $ B $ 1,0) and press Enter.
When you write USD as a dollar sign in the formula, it is an absolute reference that implies that the formula will use the specific cells regardless of whether you copy anywhere on the spreadsheet.
Divide the interest rate by 12 because the annual rate is calculated on a monthly basis.
Step 6: From cells A7 to H7 you must label the communes with these names: A7 as Payment, the next as Principal, continues with Interest, then Principal, the next as Accumulated, then with Accumulated Interest and the last with # Balance Final.
Step 7: In the Period column, complete cell A8 with the month and year corresponding to the first payment you made on the loan. If you need to change the format of the column.
To enter the data, select the cell and with one click you will drag to cell A367 to complete the column, making sure that Auto complete is in Fill Months.
Step 8: Go to cells B8 to H8 and complete the fields by entering the initial balance of the loan in cell B8.
Type = $ B $ 4 in cell C8 and press Enter.
In cell E8 you will create the formula for calculating the amount of interest on your loan and you will see = $ B8 * ($ B $ 2/12) as a formula that will find the corresponding cell in column B.
You will subtract the interest from the interest on your loan in cell D8 from the total of what you paid that is in C8. To copy correctly use relative references and you will see = $ 8- $ E8 as a formula.
You will create a formula in cell H8 to subtract the main part that corresponds to the obviously initial balance payment for the period and you will see = $ B8- $ D8 as a formula.
Step 9: From cell B 9 to H9 you will create entries including a relative reference corresponding to the final balance but from the previous period in this way: In the corresponding cell you will write = $ H8 and press Enter, cells C8, D8 and E8 will be copied to Paste them in the C9, D9 and E9 and the H8 you will paste it in the H9.
You will create a formula in cell F9 so that you obtain the principal accumulated that you paid with a formula of this type: = $ D9 + $ F8 and repeat the procedure in G9 for the accumulated interest and you will see = E9 + $ G8.
Step 10: Choose cells B9 to H9 and with the mouse go to the lower right corner of the selection until you see the cross-shaped cursor and click dragging down until you reach row 367. At this point, release the button of the mouse.
Auto Complete should be in Copy Cells and you should see $ 0.00 as last ending balance.
It is likely that the $ 0.00 you will not get as the last ending balance because you did not use absolute and relative references correctly and therefore the cells were not copied properly,
Go to one of the payment periods and you will see the interest charge for the principal and what you paid up to the interest and principal loan date.
Amortization table
- The amortization table is also a amortization table where you can see the payment schedule when you access a loan.
- It works as a summary of payments you make as a borrower and for the entire duration of the loan.
- You will find the period in the first column and it has to do with the moment you make the payment.
- The interests are found in the second column. They are the interests that as a borrower you pay in each period to the lender.
- Regardless of whether it is fixed or pourable interest, you calculate it with a multiplication: interest rate for pending capital (in the fifth column)
- You will find the amortization of the capital in the third column. It is the repayment of the loan without interest. They are the payments you make in each payment period and are discounted from the outstanding capital.
- You can find the fee to pay in the fourth column. Amortization and interest are added.
- You can find the principal of the loan that is pending repayment in the fifth column. It is calculated by subtracting the outstanding capital corresponding to the previous period with the amortization of the current period of each period.
Loan amortization table
- The amortization table as a definition is the time it takes to complete the payments on a loan in full, that is, it can be considered as the period of time you need for the payment of a mortgage, for example.
- In turn, it can be defined as part of the money that you invested and recover or all of it by having invested in some good.
- Therefore, the table shows you the payments that you will make during the entire period of the loan.
- It shows you the months or installments per month that you must pay your loan.
- It shows you the amortization of the capital as the amount you return for the loan without interest.
- It shows you the interest you pay every month with each payment.
- It shows you the payments, that is, the fee you pay.
It shows you the outstanding debt, that is, the amount that is pending payment and that you must repay.
Download: Excel amortization table