How To Make A Mortgage Calculator With Excel

How To Make A Mortgage Calculator With Excel


Mortgage calculator is an automated tool that helps the users to determine the financial suggestions of change in different variables in a mortgage financial schedule. To use the Excel Mortgage Calculator, you simply need to enter your mortgage details into the user input fields. The input fields would ask for your loan amount, the interest rate (%), number of years over which your loan has been withdrawn and the number of payments per year.

How To Make A Mortgage Calculator With Excel

How To Make A Mortgage Calculator With Excel

Once you complete the above process, the summary table at the right-top of the spreadsheet will directly display the summary of your mortgage payment details. Along with this, the amortization arrangement will be displayed in the bottom line of the spreadsheet.

Secondly, if you want the spreadsheet to display the dates of your schedule payments, you need to enter the initial loan start date into the column box of the user-input fields. Once the date has been entered, on the top left of the spreadsheet a summary box will be displayed, where you can enter the final payment. Moreover, each of the schedule payment dates will be displayed in spreadsheet’s bottom half.

Do you know the functions used in the Excel mortgage calculator?

PMT function

The Excel PMT function has been used to calculate the monthly payments on a loan ($50,000), the total amount is supposed to be paid after 5 years.  The interest is charged at a 5% rate per year. Finally, the payment of the loan will be made by the end of each month. Thus, to calculate the PMT function you need to use the formula,    =pmt (5%/12,60,50000), calculating the formula, you will get the result as -943.56.

PPMT function

The PPMT function majorly calculates the Interest part of your regular scheduled payment. For example, the Excel PMT function will help you to calculate your payment on the principal amount in months 1 and 2 on the loan of ($50,000). The entire amount needs to be paid off after 5 years. Your interest will be charged at the rate of 5% per year and your payment over the loan requires being made by the end of each month. To understand the entire process, you need to use the formula, month 1: =ppmt(5%/12,1,6,50000) and for month 2: =PPMT(5%/12,2,60,50000)

Calculating the formula, you will get a sum total of -$735.23 for month 1 and -$738.29 for month 2.

IPMT Function

The IPMT function would help you to calculate the interest part of your regular scheduled payment in month 1 and month 2. In case your principle loan amount is $50,000, you need to pay off the entire amount by the end of 5 years. Just like the previous process, the charge rate of the interest would be 5% per year. To get the interest amount, you need to use the formula:

Month 1: =IPMT (5%/12, 1, 60, 50000) which comes to -$208.33

Month 2: =IPMT (5%/12, 2, 60, 50000) which come to -$205.27


TwitterFacebookLinkedInPin It