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.

## Leave a Comment