How to Create a Mortgage Calculator with Microsoft Excel

For most people bearing the burden of a mortgage, calculating the monthly payments and installments becomes a little too mind boggling. For such people, Microsoft Excel spreadsheet offers an opportunity to create their own mortgage calculator.

Microsoft excel mortgage calculator

This mortgage calculator can be made to do things like calculating the monthly payments for repaying the loan or total cost of the loan while keeping the yearly interest rate a constant. The spreadsheet can also display a table showcasing the amount of monthly payment applicable for interest and the principle. With the flexibility that the workbook offers, you can also show how extra money against principle affects the loan and monthly installments. With so much on offer, why trouble yourself with the hassle of calculating and keeping tabs on your own. Simply get working with the Microsoft excel and calculate your mortgages!

How to create a mortgage calculator with Microsoft excel

For those who are new to Microsoft excel, here are a few steps that you can undertake to create a mortgage calculator of your own.

Step 1: The first step is to open MS Excel.

Step 2: Next, start creating certain labels that will help you in identifying the data in your mortgage calculator, while also filling in the information. To begin with, type ‘Loan Value’ in cell A1, “number of payments in months” in cell A2, “interest rate” in A3 and “monthly payment” in A5.

Step 3: In next step, you will be required to format the cells in columns such that they accept and recognize data in the correct format. For instance, cell B1 should identify the data as currency since this will indicate the loan amount. For formatting the cells, thus, right click on the concerned cell (B1 in this case) and select “format cells” from the menu that pops. On the format window, select the tab saying “number”. Within this, check the “category” and select “currency” (for B1 cell). Now select the currency symbol and set decimal places to 2. Select “ok” to save and continue. Repeat the steps explained above for setting the formats for other cells as well. However, for B2, you will not be required to set any decimal places, hence select “zero”. For B3, under “number” tab, select “percentage” for calculating interest rate. Here again, decimal places will be “zero”. B5 will again be “currency”, so repeat the steps exactly as for B1.

Step 4: Once all the cells are properly formatted, you need to enter the calculation for mortgage calculator. For the same, you can use the function “PMT” that has the ability to handle amortizations. Therefore, all that is required for you to do is simply feed the information for calculating the payment. For this, in cell B1, enter the value of the loan say 100000 (hypothetical) and press enter. The workbook will automatically convert it to currency. Fill B2 with a figure of 360 indicating a loan of 30 years (30×12 months=360). In cell B3, enter the interest rate. For example, a hypothetical rate of 6.75.

Step 5: The last thing to do now is enter the formula which will be ABS(PMT(B3/12,B2,B1)). In the above formula, ABS stands for absolute value; B3/12 indicates yearly interest divided by number of months per year (as we need a monthly installment figure); B2 reflects total number of monthly payments and B1 indicates principal loan amount. Now press enter.

The results will show the monthly payment you need to pay. Given the figures taken in our example, it will reflect $648.60.

Leave a Reply

Your email address will not be published. Required fields are marked *