How to Calculate Loan Payment in Excel (4 Suitable Examples)

We will use the following dataset, which contains two columns with Payment Details.

How to Calculate Loan Payment in Excel

Method 1 – Using the PMT Function to Calculate Loan Payments in Excel

Steps:

Select a different cell C10, to keep the Monthly Payment.
Use the following formula in the cell.

=PMT(C7,C8*12,-C5)

Formula Breakdown

We have used the PMT function which calculates the monthly or annual payment based on a loan with a constant interest rate and regular payment.

C7 denotes the monthly interest rate of 0.58%.
C8 denotes the total payment period in years which is 5. We have multiplied by 12 to calculate the monthly payment.
C5 denotes the present value which is $40,000. The Minus sign denotes Loan as debit.

Press Enter to get the Monthly Payment.

How to Calculate Loan Payment in Excel

Method 2 – Use the PV Function to Calculate the Total Affordable Loan

We’ll use a potential Monthly Payment as given data.

Steps:

Select cell C10, where you want to keep the Affordable Loan.
Use the formula given below in the C10 cell.

=PV(C7,C8*12,-C5)

Press Enter to get the Affordable Loan.

Formula Breakdown

The PV function returns the present affordable amount of a Loan.

C7 denotes the rate as the monthly interest rate.
C8 denotes the total payment period in years which is 5. We multiplied by 12 for the monthly payments.
C5 denotes the affordable monthly payment. The Minus sign denotes monthly payment as debit.

Method 3 – Using the NPER Function to Calculate the Required Payment Period

Steps:

Select the C10 cell for calculating the required period.
Use the following formula in the C10 cell.

=NPER(C7,-C8,C5,,0)

Press Enter to get the Required Period.

How to Calculate Loan Payment in Excel

Formula Breakdown

The NPER function gives the required payment period in months.

C7 denotes the monthly interest rate as Rate.
C8 denotes payment made in each period as PMT. The Minus sign denotes monthly payment as debit.
C5 denotes the total loan as present value (PV).
As the Future Value is unknown, FV will be Blank.
0 denotes the type as ending of the period.

Select cell C10.
From the Home tab, go to the Number command.
Press the Decrease Decimal feature until the fractional number converts to a whole number.

The number is rounded down.

How to Calculate Loan Payment in Excel

Method 4 – Use a Generic Formula to Calculate a Loan Payment in Excel

Steps:

Select C10, where you want to keep the Total Payment.
Use the formula given below in the C10 cell.

=C5*(1+C7*(C8*12))

Formula Breakdown

In this formula, we have converted years to months by multiplying 12 with C8 cell value.
- Output: 60.
We multiplied the Monthly Interest Rate, C6 with 60.
- Output:0.35.
We have added 1.
- Output: 1.35.
We multiplied the whole result with the Loan, C5.
- Output: $54,000.

Use the following formula in C11.

=C10/(C8*12)

Press Enter to get the result.

How to Calculate Loan Payment in Excel

Calculating the Interest Rate of a Loan Payment in Excel

Steps:

Select cell C8, where we’re keeping the Interest Rate.
Use the following formula in the C8 cell.

=RATE(C7*12,-C6,C5,,0)

Press Enter to get the Interest Rate.

Formula Breakdown

The RATE function will return the monthly rate in percentage of Loan Payment.

C7 denotes the NPER as the total payment period in years, which is 5. We have multiplied it by 12 for the monthly payment system.
C6 denotes the payment made in each period as PMT. The Minus sign denotes the monthly payment as debit.
C5 denotes the Loan as present value (PV).
As the Future Value is unknown, FV will be Blank.
0 denotes the type as the ending of the period.

Things to Remember

The four examples are for four different given information. Choose the method to do the calculation of Loan Payments according to your information.