Today we are going to be talking about how to calculate monthly mortgage payments with the PMT() function in Microsoft Excel. So, you have a situation where you need to borrow $200,000 mortgage over 30 years, and then you have to figure out how much you’re going to be paying for. This example shows what the principal owed every year over 30 years.
To calculate this you can use a built in function that excel includes called PMT or =PMT(). In the parentheses, you can input several values—there can be as many as five—but in this case I’m just going to have three.
Calculate Monthly Loan Payments:
- Use =PMT(RATE,NPER,PV) formula.
- RATE is the interest rate for the period.
- NPER is the number of periods (months, years).
- PV is the present value or the principal.
Equation One:
For example, the raw formula for a 30-year loan for $200,00 at 5% interest would be expressed as =PMT(0.00416666,360,200000). Now, the equation looks a little weird because the values don’t look how you would expect, but that’ll be explained below.
RATE equals 0.00416666 because you need to take interest rate and divide it by 12 to account for the monthly basis for which were calculating. So, in this example it would be 5% interest divided by 12 giving us 0.0041666.
Equation Two:
However, there’s actually an easier way to do it. Instead of calculating all the values separately. You can just express this as a fraction. So, for RATE you would input 0.05/12 and the equation would look like =PMT(0.05/12,360,200000). You can also reference another cell to bring that value in.
So, when you paste this formula into Excel, it expresses it as a negative number because this is what you’re paying out. In this example, your monthly payment is $1,073.64.
Equation Three:
The other way you can express this equation is =PMT(0.05/12,30*12,200000). So, this is going to be a little bit different, but the result should be the same. When you paste it into Excel, it just formatted a little bit differently but the result is the same.
The final way you can format this in Excel put a value in another cell. So, say you put $300,000 in another cell, for example cell C4, then you can just replace the $200,000 in the original equation with the cell C4. If you have a list of numbers, you can just drag the formula down and it will automatically calculate the monthly mortgage payments.
Isn’t that a pretty easy way to calculate monthly loan payments instead of trying to do some really complicated formula on your own?
For more information on how to get the most out of excel, check out my Book Excel Basics: in 30 Minutes as well as the Excel Intermediate cheat sheets. If this helped you out, please take a moment to browse my website and check out my YouTube channel @In30Minutes for other helpful tips and tricks, and where you can find a video that shows how to use PMT() function.