How to create an amortization table in Excel

If you have a loan with your bank, it's always good to be able to track its repayment over time. Making an amortization table in Excel is a good way to follow the repayment and to see how the loan evolves on one side and the interest on the other according to the calendar installments. Excel is software that is perfect for this kind of activity. You can create it yourself from home and you don't have to call a professional.

Steps

Prepare Amortization Schedule in Excel Step 1

Step 1. Go to your spreadsheet

Double click the Excel software icon and open a new workbook.

Prepare Amortization Schedule in Excel Step 2

Step 2. Enter the titles

Write in cells A1 To A4 the following items in the order given: loan amount, interest rate, month and monthly payment.

Prepare Amortization Schedule in Excel Step 3

Step 3. Write the encrypted data

In the cells B1 To B3, type in the values for your loan.

Prepare Amortization Schedule in Excel Step 4

Step 4. Enter the interest rate

Type the value of the interest rate as a percentage.

Prepare Amortization Schedule in Excel Step 5

Step 5. Insert a formula

In the cell B4, type for the calculation of your refund the formula = ROUND (VPM ($ B $ 2/12; $ B $ 3; - $ B $ 1; 0); 2), then press the Enter key.

Note that surrounding cell values with "$", like this "$ B $ 2", means that if you copy the formula to another cell, the formula will always take the same cells for the calculation as it should. to do.
In the formula, you can see that the interest rate is divided by 12. This is because the interest rate given is an annual rate and you need to know its value per month.
Note, for example, that a loan worth € 150,000 with an annual interest rate of 6% over a period of 30 years (360 months) will cause you to repay € 899.33 each month.

Prepare Amortization Schedule in Excel Step 6

Step 6. Enter new information

From cell A7 and up to the cell H7, write the following items in the order given: period, initial balance, monthly payment, principal, interest, accumulated principal, accumulated interest and remaining balance.

Prepare Amortization Schedule in Excel Step 7

Step 7. Type the dates in the Period column

In the first column Period of your table, write the repayment date in each cell.

Enter in the cell AT 8, the date of your first reimbursement. To have a correct display of the dates, you may need to configure the display in the format Dated for the whole column.
Once the first date is entered, select the cell, then click on the fill handle and drag your cursor down to the cell A367. If you see that the increment has been done in day, click on the small icon at the bottom right of your selection and in the contextual menu that opens, choose Increment months.

Prepare Amortization Schedule in Excel Step 8

Step 8. Write the other information

In the cells B8 To H8, enter the rest of the information.

Type in the cell B8, the starting amount of your loan.
To the cell C8, type = $ B $ 4, then click Enter.
Go into the cell E8, then write the formula

= ROUND ($ B8 * ($ B $ 2/12); 2).

This formula will calculate the amount of the interest rate for this maturity. Note that "$ B8" means that the formula (regardless of where you copy it to) will always look for information in column "B" to perform its calculation.
Click in the cell D8 and enter the formula = $ C8- $ E8. This formula will calculate the portion of the loan that you will repay for this month. Once again the “$” which is in front of each letter in the formula allows to frame the search for data always in the same columns, regardless of where the formula will be copied.
Go to cell H8, then type the formula = $ B8- $ D8. This last formula gives you the amount of the loan that remains to be repaid after this date.

Prepare Amortization Schedule in Excel Step 9

Step 9. Continue on the next line

Go to the row below and start typing the elements for the cells B9 To H9.

In the cell B9, enter = $ H8. Then copy the cells C8, D8 and E8 to paste formulas into cells C9, D9 and E9. Finally, copy the cell H8 and paste it in the cell H9. You can see that the formulas fit right away in the new cells and that's thanks to the "$" used in the formulas.
Go to the cell F9, then type the formula = $ D9 + $ F8, this will give you the cumulative amount of repaid capital. Go to cell G9 and type the formula = $ E9 + $ G8 and thus you will have the accrued interest paid on that date.

Prepare Amortization Schedule in Excel Step 10

Step 10. Finish entering the data

Now that all the formulas are in place, you can complete your amortization schedule.

Select cells B9 To H9, then in the right corner of your selection you can see the recopy handle in the shape of a cross, click on it and move the cursor down to line 367. So you have a table which will calculate the reimbursement of an amount and the period can run over 30 years, if necessary.
Check that the feedback handle has worked correctly in mode Copy cells. You will not arrive at 0 € exactly and this is normal since you have used the formula ROUND.

Advice

With this amortization table, you can see for any period the amount of accumulated capital, the accumulated interest, the balance you have left to repay. You just have to go down in your table to know all this information.
Check that you have all the formulas in the correct cells and that the “$” is present. Know that you will not arrive at 0 €, it is normal with the formula ROUND.