# How can I use the Excel PMT function to calculate a mortgage payment?

The formula wizard asks for:

Rate, NPER, PV, and FV.

I have the following info:

I guess the rate would be the annual interest rate (say 5.5%) divided by 12, or 0.48%.

NPER is the number of periods, or 30 years * 12 months = 360.

But what do I plug in for PV (present value) and FV (future value)?

I'm guessing PV is what I think of as the "loan amount." Is that right?

-
I think there are some more excel functions that would be able to calculate this in a more direct fashion – Joe Philllips Aug 8 '09 at 2:40

## 3 Answers

The PV, Present Value is the loan amount, and should be entered as a negative number, so if you are borrowing 10,0000 enter -10,0000. You had everything else right. Just make sure that the NPER and Rate are expressed in similar terms. The formula actually works for any period of time. For instance say you are a mobster type and have a loan shark business and you charge 1% per week, you would enter .01 as the rate and 52 for the NPER for a one year loan, coming up with a payment of 24.76 per thousand borrowed.

-

You're close: PV is the amount you're borrowing while FV is 0.

-

just be consistent with signs and periodicity:
- signs: everything that goes IN your pocket is positive, OUT of your pocket is negative 8*)
-periodicity: if your PMT is monthly, the rate must be monthly and the NPER is a number of month. If you want to switch to annual, change ALL params to annual.

-