![calculate mortgage payment in excel calculate mortgage payment in excel](https://exceljet.net/sites/default/files/styles/og_image/public/images/formulas/estimate%20mortgage%20payment.png)
![calculate mortgage payment in excel calculate mortgage payment in excel](https://www.wikihow.com/images/e/ef/Calculate-an-Interest-Payment-Using-Microsoft-Excel-Step-8.jpg)
We will use this scroll bar to take “loan amount” input. And we feed these values to PMT formula to find the monthly installment amount.
![calculate mortgage payment in excel calculate mortgage payment in excel](https://ufreeonline.net/wp-content/uploads/2019/04/calculating-mortgage-payments-in-excel-new-calculate-mortgage-payment-excel-iamfreeub-of-calculating-mortgage-payments-in-excel.jpg)
We will use scroll-bar controls to take numeric inputs required (P,N and R) for the payment calculation. We are multiplying loan duration (N) with 12 since we are going to make monthly payments. We are dividing interest rate (R) by 12 since R is annual interest rate and we make monthly payments. Given the values of P, N and R, we can find the monthly payments using Excel’s PMT formula like this: How is mortgage payment calculated?Īs I said above, any mortgage (or housing loan) will have 3 parts – loan amount (p), loan tenure (n) and annual interest rate (r). Now that you find form controls hot and attractive, lets proceed and make a house loan payment calculator. This way of gathering inputs is more fun, engaging and interactive. instead of taking “age” in a cell, you can use a scroll bar and set the values from 0 to 100.instead of asking a user to enter “yes” or “no” in a cell, you can ask them to click a check box.Do you know that you can add the very same controls to Excel spreadsheet to make the it interactive? Why you should not be boring and use the form controlsĪ form control is a button or check box or scrollbar or some other click-able thing you see in Windows. We are going to use Excel’s form controls (more on this below) to build a mortgage payment calculator like this: Given these 3 parameters, we can easily determine the monthly installment amount (this will be the same amount for all months during loan tenure) duration of the loan (also called as tenure of mortgage) in years.But we will not build a boring excel sheet, we will build a mortgage calculator that is easy to play with.Ī mortgage payment is a monthly installment that you pay towards a loan. Today we will build a mortgage payment calculator using excel.