< Go Back

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

Formula

=PMT( rate, nper, pv )

Arguments

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

 

Explanation

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.

Example

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

=PMT(B4/B^,B5,-B3)

 

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.

Notes

  • 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

avatar