How to Calculate Monthly Payment on a Loan in Excel (2 Ways)

Get FREE Advanced Excel Exercises with Solutions!

In this article, we will learn how to calculate monthly payment on a loan in Excel. When you take a loan for a certain period, you need to make a monthly payment depending on the interest rate. Today, we will demonstrate 2 easy methods. We can use the PMT function in Excel to find the monthly payment. Also, we can use the direct formula. Using these methods, you can calculate the monthly payment for a loan quickly. So, without further delay, let’s start the discussion.


Excel PMT Function Introduction

In order to know how to calculate monthly payment on a loan in excel, you have to first understand the PMT function. The PMT function calculates payments for a loan depending on constant payments and a constant interest rate. We can use this function to determine the monthly payment for a loan. The PMT function has 3 required and 2 optional arguments. Let’s get familiar with the syntax and arguments of the function.

  • Syntax

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

  • Arguments

Rate (required): It is the interest rate you will pay each period. For monthly calculation, we use the value of interest/month. Suppose, you took a loan with a 12% annual interest. Then, the monthly interest rate will be (12%/12) or, 1%.

Nper (required): It is the total number of payments you need to make to repay the loan. If you take a loan for 5 years and make monthly payments, then the total number of payments is 5*12=60. Because you need to pay 12 times a year.

Pv (required): It is called the present value. Also, known as the principal. In our case, it is the amount of the loan.

Fv (optional): It is known as the future value. It is the amount of money you want to attain after the last payment. In the case of a loan, you will not have to pay anything after the last payment. So, the future value will be 0.

Type (optional): This argument indicates when your payments are due. 0 denotes payments are due at the end of the period and 1 denotes payments are due at the beginning of the period. In the case of a loan, payments are due at the end of a period. So, you need to type 0 in place of this argument.


How to Calculate Monthly Payment on a Loan in Excel: 2 Ways

To explain the methods, we will use a dataset that contains information about the yearly and monthly interest rate, number of years, total numbers of payments for a loan, and the value of the loan. Suppose, you took a loan of $50,000 for 5 years with a yearly interest rate of 12%. You need to calculate the monthly payment for this loan. So, the dataset will look like the picture below.

Sample data to show how to calculate monthly payment on a loan in Excel


1. How to Calculate Monthly Payment on a Loan in Excel Applying Excel PMT Function

In the first method, we will show the process to calculate the monthly payment on a loan with the PMT function. You need to find the value of the required arguments and insert them inside the function to find the monthly payment. This process is simple and avoids all complexities. Let’s follow the steps below to see how we can determine the monthly payment for a loan.

STEPS:

  • In the first place, select Cell C6 and type the formula below to find the monthly interest rate:
=C5/12

Apply Excel PMT Function to Calculate Monthly Payment on a Loan

Here, Cell C5 contains the yearly interest rate. But we need the interest rate/month to get the monthly payment. So, we have divided Cell C5 by 12.

  • Secondly, press Enter to see the monthly interest rate.

Apply Excel PMT Function to Calculate Monthly Payment on a Loan

  • Thirdly, we need to find the total number of payments.
  • To do so, select Cell C8 and type the formula below:
=12*C7

Apply Excel PMT Function to Calculate Monthly Payment on a Loan

In this case, Cell C7 contains the number of years and for monthly payments, you need to pay 12 times a year. So, we have multiplied Cell C5 by 12.

  • Now, hit Enter and see the total number of payments. You need to pay 60 times in 5 years.

Apply Excel PMT Function to Calculate Monthly Payment on a Loan

  • In the following step, select Cell C11 and type the formula below:
=PMT(C6,C8,C9,0,0)

Apply Excel PMT Function to Calculate Monthly Payment on a Loan

Here, Cell C6 contains the value of the monthly interest rate, Cell C8 is the total number of payments, and Cell C9 is the loan amount. The fourth argument is 0 because the future value must be 0 for a loan. Also, the fifth argument is 0 because the payments are due after each period.

  • Finally, press Enter to get the monthly payment on a loan.
  • From the result, we can say that we need to pay $1,112.22 each month for 5 years to repay the loan.

Apply Excel PMT Function to Calculate Monthly Payment on a Loan

  • Moreover, you can use the picture below to understand the arguments clearly.

Apply Excel PMT Function to Calculate Monthly Payment on a Loan

Read More: How to Calculate Loan Payment in Excel


2. Determine Monthly Payment on a Loan Using Simple Formula in Excel

In Excel, we can also use the simple mathematical formula directly to determine the monthly payment on a loan. To calculate the monthly payment with the formula, we need to have the information on the yearly interest rate (i), loan amount (P), number of years (n), and number of payments/year (q). If you take a $50,000 loan for 5 years at a yearly interest rate of 12%, then the dataset will look like the picture below.

The general form of the formula to calculate the monthly payment for a loan can be written as:

M = (P*i)/(q*(1-(1+(i/q))^(-n*q)))

Here,

  • M is the amount of monthly payment.
  • P is the loan amount.
  • q is the number of payments per year.
  • i is the constant interest rate.

Let’s follow the steps below to see how we can determine monthly payments with the mathematical formula.

STEPS:

  • Firstly, select Cell C10 and type the formula below:
=(C8*C5)/(C7*(1-(1+(C5/C7))^(-C6*C7)))

Determine Monthly Payment on a Loan Using Simple Formula in Excel

Here, Cell C8 is the loan amount (P) which is multiplied by the yearly interest rate. Cell C5 contains the yearly interest rate(i). Cell C6 is the number of years (n) and Cell C7 is the number of payments/year (q).

  • After that, press Enter to see the result.
  • In this case, we got the same value for the monthly payment as the previous one.

Read More: How to Calculate Monthly Payment with APR in Excel


Download Practice Book

You can download the practice book from here.


Conclusion

In this article, we have discussed 2 easy methods of how to Calculate Monthly Payment on a Loan in Excel. I hope this article will help you to perform your tasks efficiently. Furthermore, we have also added the practice book at the beginning of the article. To test your skills, you can download it to exercise. Lastly, if you have any suggestions or queries, feel free to ask in the comment section below.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mursalin Ibne Salehin
Mursalin Ibne Salehin

Hi there! This is Mursalin. I am currently working as a Team Leader at ExcelDemy. I am always motivated to gather knowledge from different sources and find solutions to problems in easier ways. I manage and help the writers to develop quality content in Excel and VBA-related topics.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo