Here is the excel mortgage payment calculator file. Download Excel Mortgage Payment Calculator You can easily see what happens when you increase loan tenure or decrease interest rate. You can play with it by testing various combinations and finding monthly payments. Now your mortgage payment calculator is ready. But you can make it positive (for display purposes) by multiplying it with -1 like this = -PMT((A2/12)%,A3*12,A1) Step 5: Play with your Model Remember, PMT returns value in negative numbers (as it is the amount we need to pay, not get). Assuming you have linked scroll bars like this: Now that the scroll bars are ready, just write the PMT formula. Step 4: Plug the values in to PMT formula Make sure you set the minimum and maximum values in a reasonable range. One for interest rate and one for loan tenure. Repeat the same steps for 2 other scroll bars. So whenever scroll bar is updated the cell gets updated too (and vice-a-versa). When you do this, excel links the scroll bar to the selected cell. To keep our model simple, just set minimum as 35 and maximum has 500.Īlso, select a cell to link the scrollbar. Here set minimum and maximum values for the scroll bar. To set the properties for the scrollbar control, right click on it and go to “format control” option. Step 2: Set Properties for this Scroll-bar Once selected, just add the control to spreadsheet by clicking anywhere. (related: enable developer toolbar in excel 2007) Add a Scroll-bar Control In Excel 2003 use View > Toolbars > Forms and select Scroll-bar control. To do this go to Developer Ribbon > Insert > Scroll-bar Form Control in Excel 2007. So, if the loan is $120,000, the input should be 120.įirst add a scroll-bar form control to your excel sheet. To keep it simple, we will ask users to enter input in ‘000s. 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. 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.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |