How to Create an Excel Mortgage Payment Formula
Creating an excel mortgage payment formula is a quick and easy process that can help you make educated decisions on purchasing a home. This calculation can be setup within 5 minutes and can save you a lot of time in the future for interested home buyers. Whether you are looking to purchase your first home, refinance an existing home, or are just investigating the possibilities of owning your own home - this calculation can help! Use the following steps to create an excel mortgage payment formula that you can use to help you purchase a home.
Other People Are Reading
Instructions
-
-
1
Capture the 3 key pieces of data required to calculate a monthly mortgage payment. Collect the Total Loan Value and add it to cell (A:1), Interest Rate should be added to cell (A:2), and add the Number of Years on the loan in cell (A:3). These 3 data fields will be the basis for creating your excel mortgage payment formula.
-
2
Calculate the monthly interest rate based on your annual interest rate in cell (A:2). Enter the following formula in cell (A:5) - '=A2/(12*100)'. The single quotes are to denote what should go into the cell for your excel mortgage payment formula. Be sure to exclude them when entering the information.
-
-
3
Enter a calculation in cell (A:6) to find out how many months you plan to take out the mortgage for. Add the following formula in this cell - '=A3*12'. This calculation will allow you to break down your mortgage payments on a monthly basis instead of yearly.
-
4
Finish your calculation in cell (A:7) by using the following formula - 'A1*(A5/(1-(1+A5)^-A6))'. This final calculation will give you the estimated monthly mortgage payments for the values you provided. The great thing about creating an excel mortgage payment formula is that you can save it and change the total loan amount, interest rate, and number of years to figure out what kind of home you can afford!
-
5
Save your spreadsheet and format any cells you feel appropriate. For example, the final monthly payment in cell (A:7) could be formatted as a dollar amount which looks cleaner than a calculated number. The choice is yours depending on how detailed you would like to get.
-
1
Tips & Warnings
You could include several of the calculations in steps 2 - 4 in one single cell depending on how you would like to see the data.
Play around with the numbers to help guide you and what you can afford.
The total loan amount should equal the amount the bank or lender is lending to you. Any down payments should be deducted from this amount.
The total loan amount does not include any homeowner's insurance, local taxes, or any homeowner's association expenses. These dollar amounts should be added to the monthly amount once calculated.
