There are many tools available that will run a monthly mortgage payment calculation. Free online mortgage calculators are a popular choice as they provide a quick and easy way to determine your monthly payment. Another option is to calculate your monthly mortgage payment by hand. This method allows a future homeowner the ability to understand where their money is being spent but can take more time and is prone to human error.
A third option to run a monthly mortgage payment calculation is to leverage software programs like Excel. This method can offer the best of both worlds. For example, the PMT (Payment) function in Excel is quick and easy to use and allows the homeowner to see how their money is being spent. While not as convenient as the online tools, the Excel function automates the calculation while minimizing the risk of human error.
Monthly Mortgage Payment Calculation
No matter what tool you decide to use to run the monthly mortgage payment calculation, three pieces of data will always be required. The first piece of data is the total loan amount. This amount is not the price of the home you are purchasing, rather the total dollars you are borrowing from the lender.
The second data element is the interest rate on the loan. This could be a locked in rate or an estimate of what you think the rate could be. Keep in mind that rates can change quickly and are dependent on many factors such as your credit score.
The final piece of data required to calculate a monthly mortgage payment is the terms. The most common fixed rate mortgages are either 15 or 30 years, although there are other options available.
Using the PMT (Payment) Excel Function
It is important to first point out that the PMT function should only be used to calculate a fixed rate mortgage. The interest rate must remain constant for the equation to work properly, which rules out any adjustable rate mortgages.
Once you have collected the required information to calculate your payment, it is time to use the PMT function. The actual function offers 5 parameters, but only the first 3 are required to calculate a fixed rate mortgage payment.
Here are the required parameters you must provide in order to properly use the function.
- Rate – monthly interest rate
- Nper – total number of loan payments
- Pv – present value or total amount borrowed
Excel Payment Function Example
In order to show an example of how this function can be used, let’s calculate my monthly mortgage payment. Here are the 3 required pieces of data needed to run the monthly mortgage payment calculation.
- Total Loan Amount – $215,950 (will be used as the Pv value)
- Interest Rate – 4.375% (needs to be converted into monthly interest rate)
- Mortgage Terms – 30 year fixed rate (needs to be converted into total payments)
Once the interest rate is converted into the monthly amount and the number of payments is calculated, the values can be inputted into the PMT Excel formula.
The parameters of my formula look like this -
=PMT(0.04375/12,360,215950)
The results of the formula equal ($1,078.21), which is the exact value that I calculated by hand.
Final Thoughts
Using Excel to run your monthly mortgage payment calculation has plenty of advantages. It offers a quick and flexible tool that can help homeowners calculate their monthly payments. The built in PMT (Payment) function can automate any monthly loan payment calculation, which reduces any human error.
What option do you prefer to run a monthly mortgage payment calculation?
Related posts:
- How Much Will My Monthly Mortgage Payment Be?
- How to Calculate Your Monthly Mortgage Principal and Interest Payments
- Should I Refinance My Mortgage?
- What is a Fixed Rate Mortgage?
- What is a Biweekly Mortgage?
Related posts brought to you by Yet Another Related Posts Plugin.
{ 1 trackback }
{ 1 comment… read it below or add one }
This was a great little tutorial. Thanks for sharing this, I will try and use excel a bit more, I know it is powerful enough but it is knowing how to get the best out of it!