Real Estate Tips: Building an Excel Mortgage Calculator

Microsoft Excel can be used to create a mortgage calculator. The mortgage calculator can be used to calculate the monthly payment on a mortgage based on different variables for loan amount, interest rate and the term of the loan. Here are the steps to create a mortgage calculator in Excel:

Step 1: Create Headings

Create headings in different cells for loan amount, interest rate, loan term, payments per term and payment amount. Headings should be placed in column 1 in the order listed.

Step 2: Format Cells

The cells containing values go in column 2. The cells containing loan amount and payment amount need to be for currency. The interest rate should be a percentage. Loan term and payments per term are integers.

Step 3: Give Cells a Name

The first cell in Excel is A1. You can replace this name with something else. For example, you could replace the names of your cells with the following names: Loan_Amount, Interest_Rate, Loan_Years, and Num_Pmt_Per_Year.

Step 4: Create Formula

The payment amount cell will contain the formula. The formula will use a built-in function called PMT. This function uses input from the other fields to calculate the monthly mortgage payment. Using the cell names above, enter the following formula in the payment amount cell:

=-PMT( Interest_Rate/Num_Pmt_Per_Year,Loan_Years * Num_Pmt_Per_Year,Loan_Amount)

blog comments powered by Disqus