Microsoft logo
Excel logo

Excel Formula Coach

 Office logo
 

Plug your numbers in the boxes below, press the Build it! button, and we'll build your formula. We'll even give you the answer.

When you're done, you can paste the formula into your own Excel worksheet.

Interest rate:
(such as 4.5)

Length of loan:
(in years, such as 30)

Loan amount:
(such as 250000)

Ready?

This is your formula:

And here's your answer:

Try another?

PMT function: Use it to calculate a payment

Calculates the payment for a loan based on constant payments and a constant interest rate. For example, you're purchasing a home with an interest rate (APR) of 4.5%, a loan amount of $250,000, with 30 years to pay it off. Your monthly mortgage payment will be $1,266.71.

Want to find out how much of a loan you can afford based on your budgeted payment amount? See Excel Formula Coach: PV, scenario #1.

Syntax PMT(rate, nper, pv, [fv], [type])

Tip Wondering why [fv] and [type] are in square brackets? The brackets mean they're optional. If you don't include values for fv and type in your formula, Excel assumes your balance will be $0 at the end of the loan, and that your payments are due at the end of the period.

Example =PMT(4.5%/12,30*12,250000)

The PMT function has the following arguments — the first three are always required:

Rate (Required) The interest rate for the loan. For monthly payments, divide this by 12.

Nper (Required) The total number of payments for the loan. For monthly payments, multiply this by 12.

Pv (Required) The present value, or the total amount that a series of future payments is worth now; also known as the principal. In other words, the loan amount.

Fv (Optional) The future value, or the balance you want after the last payment is made. If you don't include a value for fv, it's assumed to be 0 (zero), that is, the future value of the loan is 0.

Type (Optional) The number 0 (zero) means the payments are due at the end of each period, and 1 means they're due at the beginning.
 


Here's your chance to leave feedback!

Would you like to see more Excel functions featured in Excel Coach? Do you have an idea for improving the design? Let us know by going to the PMT function article and leaving feedback at the end, under the "Did this article help you?" section. It'll help us better identify feedback on this if you mention "Excel Coach" in your comment.

Did this article help you

Choose one of the three responses, leave a comment in the "Rating received!" box, and click the "Submit" button. We'll read it...we promise.

Feedback box

Thank you!

Microsoft

© 2014 Microsoft Corporation. All rights reserved.