- 1). Open a new Excel worksheet and add labels. In cells A1 to A3, type "Loan Amount," "Loan APR" and "Loan Term" in that order. Skip cell A4, and in cell A5, type "Monthly Payment." Adjust the width of Column A if necessary.
- 2). Add the initial loan data. In cell B1, type in the amount of the mortgage or the principal. Convert the APR into decimal form. For instance, 5 percent = 0.05. Add this to cell B2. In cell B3, type in the mortgage term length in years.
- 3). Add the formula for the monthly payment to cell B5. In Excel, this is written as "=-PMT(B2/12,B3*12,B1)." The APR is divided by 12 in the formula to obtain a monthly interest rate. The loan term is multiplied by 12 in the formula to convert years to months. The minus sign in front is used to display the payment amount as a positive number. The monthly payment will display in cell B5.
- 1). To calculate the interest and principal payments for each payment made on the loan, you can build an amortization table. Start with the monthly payment calculator and add labels for the table. In cells B8 to E8, type in the labels "Beginning Balance," "Interest," "Principal" and "Ending Balance" in that order.
- 2). Type "B1" under "Beginning Balance" in cell B9. The initial beginning balance is the initial loan amount. The ending balance will become the new beginning balance from this point on. In cell B10, type in "E9."
- 3). In cell C9, under "Interest," type in the equation "=B9*$B$2/12." The APR is converted into a monthly interest rate and applied to the monthly beginning balance. Notice the absolute reference for the interest rate.
- 4). In cell D9, under "Principal," type in the equation "=$B$5-C9." The interest payment is subtracted from the monthly payment to obtain the principal payment for that month.
- 5). In cell E9, under "Ending Balance,"type in the equation "=B9-$B$5." The monthly payment is subtracted from the beginning balance to obtain the ending balance.
- 6). Copy cells C9 to E9 into cells C10 to E10. This represents the first two months. Copy cells B10 to E10 down the column for as many months as you would like to calculate. A 30-year mortgage would be a total of 360 rows.
- 1). Add the label "Additional Payment" in cell F8. You can also add a column between the ones you already have and use that instead. The formulas will all adjust themselves accordingly. Fill in the column below it with your additional payments. You can use a different payment in every space or you can copy a single payment down the column.
- 2). In cell E9, under "Ending Balance," edit the formula to read "=B9-($B$5+F8)" to account for the extra payment. Copy the new formula down the column. Although it's not necessary for the calculations, you can edit the "Principal" column in the same way.
- 3). With the complete calculator, you can now adjust the various values to try different mortgage repayment scenarios.
- Advertisement -
- Advertisement -