Excel Mortgage Calculator
In order to use the above Excel Mortgage Calculator, simply update the pink-shaded user-input fields of the spreadsheet (shown on the rightabove), with details of the loan amount, the interest rate, the number of years over which the loan is taken out, and the number of payments per year.
Once you have completed these fields, the summary table at the top-right of the spreadsheet will automatically display a summary of the mortgage payment details, and the mortgage amortization schedule will be displayed in the bottom half of the spreadsheet.
If you want the spreadsheet to show the dates of each of the scheduled payments, you must also enter the loan start date into the user-input fields. Once this has been entered, the summary box on the top left of the spreadsheet will display the date of the final payment and the date of each of the scheduled payments will be displayed in the bottom half of the spreadsheet.
The following date and financial functions have been used to produce the amortization schedule in the above Excel Mortgage Calculator:
|PMT function||Calculates the regular scheduled payment amount.|
|PPMT function||Calculates the Principal part of the regular scheduled payment.|
|IPMT function||Calculates the Interest part of the regular scheduled payment.|
|DATE function||Calculates the dates of the scheduled payments.|
The procedure for creating an Excel Mortgage Amortization Calculator, similar to the one above is explained in detail on the Wikihow website.