Calculate Payment For A Loan

In Excel, we can calculate the payment for a loan by applying function PMT function. This function will calculate the constant periodic payment that must be paid off investment, with a condition of constant interest rate.

Calculate Payment For A Loan


=PMT( rate, nper, pv )


rate is the constant interest rate of the period

nper is the number of periods that the investment/loan is paid

pv stands for a Present value of the investment/loan



An investment or a loan usually has four main components: the total amount of investment, the interest rate, the number of periods (term), and the payment per period. PMT function will calculate the payment per period when the other three components are available.


Figure 1. Example of using PMT function to calculate the monthly payment of investment


As an example in Figure 1, we can calculate the monthly payment by input



Rate – Since the interest rate in the formula is counted by month, for interest rate, we will have to find the monthly rate by divided 7.5% to 12 months.

Nper- the total period should be in months (60 months)
PV – the total investment amount is $10,000. The reason for using the minor (-) before B3 is to make this value negative. Since an investment is a money owed.


  • Make sure that interest rate and a number of periods should be in the same units. (usually monthly basis)
  • Negative or possible present value is only a display. We highly recommend putting a minor in front of PV for investment and loan only.


Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment