To create a basic loan amortization schedule, follow these
steps.
Note The following steps are an example that contains the formulas
that you have to have for a basic loan amortization schedule. If a cell
reference is blank (for example, cell B1), do not type data in the cell.
- In Excel, open a new workbook.
- On Sheet 1, type the following data in the specified cells:
A1: Loan Amount: B1: C1: D1:
A2: Interest Rate: B2: C2: D2:
A3: # of Months: B3: C3: D3:
A4: Monthly Payment B4: =PMT(B2/12/100,B3,B1) C4: D4:
A5: Payment # B5: Start Balance C5: Int. for Month D5: Payment E5:End Balance
A6: 1 B6: =B1 C6: =B6*($B$2/12)/100 D6: =$B$4 E6: =B6+C6+D6
A7: =A6+1 B7: =E6 C7: =B7*($B$2/12)/100 D7: =$B$4 E7: =B7+C7+D7
- Select column B, column C, column D, and column
E.
- On the Format menu, click
Cells.
- On the Number tab, click
Accounting in the Category list, change the
Decimal places box to 2, change the
Symbol box to None, and then click
OK.
- In cell B:1, type the loan amount that you want to
amortize. If the loan amount is $181,900, type
181900.
- In cell B:2, type the interest rate of the loan. For
example, if the loan interest rate is 5.75%, type
5.75.
- In cell B:3, type the total number of payments of the loan.
For example, for monthly payments over a 30-year loan duration, type
360.
- Select cells A:7 to E:7
- With your mouse, click and hold the square dot on the
lower-right corner of the selection, and then drag to extend the selection to
the number of payments in cell B:4. For example, if you typed 360 for the total
number of payments of the loan, extend the selection to row 365 of your
worksheet.