How to Make a Loan Amortization Table With Additional Principal in Excel
- 1). Enter the labels for the key loan variables. Type "Initial Value" into cell A1, "APR" (Annual Percentage Rate) into cell A2 and "Term" into cell A3.
- 2). Enter the values for the key variables in the column to the right of the labels. For example, if you had a 30-year fixed loan of $250,000 at 6 percent APR, you would enter "250000" in cell B1, "0.06" in cell B2, and "30" in cell B3.
- 3). Type the label "Payment" in cell A5. In cell B5, use the "PMT" formula from Excel along with the data you have already entered to calculate the monthly payment. Type in: =-PMT($B$2/12,$B$3*12,$B$1) if the mortgage is due monthly, or =-PMT($B$2/26,$B$3*26,$B$1) if the mortgage is due biweekly. For the example loan in step 2, the payment should equal $1,498.88.
- 4). Add column labels for your table. "Balance" in cell A7, "Interest" in cell B7, "Principal" in cell C7, "Additional Principal" in cell D7. Type "=$B$1" in cell A8, just under "Balance" to start the table.
- 5). Multiply the payment period interest (APR/12 for monthly, APR/26 for biweekly) by the loan value in cell B8 by typing "=A8 * $B$2/12". For the example loan, the value should be $1,250.
- 6). Calculate the monthly principal for the first payment by subtracting the interest from the monthly payment in cell C8: "=$B$5 - B8" For the example loan, the value should be 248.88. Enter "0" for the first additional principal payment in cell D8.
- 7). Calculate the new loan balance in cell A9 by subtracting the principal from cell C8 and the additional principal from cell D8 from the previous loan balance: "=A8-C8-D8" Copy cells B8 and C8 into cells B9 and C9, respectively. Copy cells A9 through C9 down the spreadsheet until the loan value reaches "0." For the example loan, this occurs at row 368.
- 8). Add additional principal payments in the "Additional Principal" column, as they are paid. The change will immediately ripple through the amortization table. Each represents a single payment due.
Source...