The PMT function in Excel determines the total payment owed each period—inclusive of the interest and principal payment. The total payment, unlike the other two components, will remain constant over the entire borrowing term.

How to create an Excel sheet to track payments Open a new Excel spreadsheet. Create column headings for the following information. Enter the payment information into the spreadsheet. Use formulas to calculate the total amount of payments received and the total amount of outstanding payments.

Even a single extra payment made each year can reduce the amount of interest and shorten the amortization, as long as the payment goes toward the principal and not the interest.

If you prepay your mortgage you reduce the principal balance, reducing the interest due next month and every month forward. If you prepay $1000 on your mortgage, the interest next month will be reduced by 10003.7%/12=3.08 You will still make the same payment, but an additional 3.083 will be credited toward principal.

Ideally, you want your extra payments to go towards the principal amount. However, many lenders will apply the extra payments to any interest accrued since your last payment and then apply anything left over to the principal amount. Other times, lenders may apply extra funds to next month's payment.

More info

The tutorial shows how to build an amortization schedule in Excel to detail periodic payments on an amortizing loan or mortgage. Loan amortization schedule. 3.4, Enter values, Loan summary. This Excel spreadsheet makes it easy to view the amortization of a home loan with optional extra monthly payments. Does anyone know how to calculate the principal and interest payments after a one time extra principal payment on a mortgage in excel? Use our free mortgage calculator to get an estimate of your monthly mortgage payments, including principal and interest, taxes and insurance, PMI, and HOA. Use our free mortgage calculator to get an estimate of your monthly mortgage payments, including principal and interest, taxes and insurance, PMI, and HOA.