mortgage calculator template

Mortgage Calculator – Calculate House Price, Monthly Payment, and Gain or Loss

Download template

This mortgage calculator will allow you to accomplish three different things:

1) Determine the housing price based on a desired monthly payment, interest rate, and terms.
2) Determine the monthly payment based on the price of a house, interest rate, terms, and down payment
3) Calculate the mortgage balance remaining and calculate a gain or loss depending on the selling price you enter.

To select which calculation you are after selecting the option from the drop-down under the ‘Calculate’ header:

mortgage calculator selection
All of the inputs are in yellow cells and can all be changed. Please note that since some of the same fields are used in the different calculators, if you input figures in one cell and change the calculator you will notice the fields won’t reset to 0 but will carry the figures you entered earlier. So you will need to clear or change the amounts in those fields when that happens.


Calculate an Affordable Housing Price

I’ll start with the first example, the Housing Price. In this instance, you want to determine the house price you can afford based on the annual interest rate, term, and how much you want to pay per month. Once you enter your inputs it will tell you the house price that you can afford and if you need additional funds for your downpayment – based on your desired downpayment %

Mortgage calculator house price

In the above example, I wanted to know what price I can afford if I wanted to pay $2,000 a month at a 4% annual interest rate for 30 years. The result was $523,653.10. And since I entered my desired downpayment % as well as how much I had available, it lets me know any amounts I am short for the downpayment. For a house costing $523,653.10, a 20% downpayment would be $104,730.62. Since I put that I have $100,000 available, it tells me that I need an additional $4,730.62 to meet the 20% downpayment.

Calculate the Monthly Mortgage Payment

Next, I will switch over to calculate my monthly payment. In this situation, I specify the house price I want to buy, the amount of the down payment, interest rate, and years

mortgage calculator monthly payment

In this example I set my house price to $500,000; downpayment to $50,000; term again 30 years and interest rate also still 4%. This calculates my monthly payment to be $2,148.37.

Calculate the Gain or Loss on a Sale Price

In my last example, Gain/Loss on Sale has the most variables since it takes into account the price, down payment, monthly payment, the start of the mortgage, selling date, price, and estimated costs. This would be if you wanted to gauge whether you might be looking at a loss or a profit based on the variables entered.

If you’ve been entering these amounts in the previous calculators you will notice some values have carried over, so always be careful to clear the fields first or at least double check the inputs or you may get a result you did not expect.

mortgage calculator gain or loss on sale

For my inputs here I have entered similar values to my earlier examples and now I am calculating whether I will have a gain on the sale if I sell it for $550,000. After commission costs of 20,500 plus closing costs of 11,000 (550,000 x 2%), plus the mortgage balance that remains of 404,966.58, that will leave me with proceeds from the sale of $113,533.42. The gain or loss in this template looks at whether I am walking away with more money than my original downpayment or less. Since my downpayment was $104,730.62, it is a gain on the sale since I am taking more than what I originally put into the house.

These calculators should only be used for estimating purposes and shouldn’t be intended to calculate with 100% accuracy any tax liabilities or other costs. Housing rules vary widely from one region to another so it would be very difficult to factor in every variable. Even mortgage penalties among banks vary in calculation so for the sake of simplicity those variables are taken out of the equation, however, you can estimate a closing cost % and if you want to be conservative you can adjust this % to help account for these variables as you see fit.

Add a Comment

You must be logged in to post a comment