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


<< Go Back to Calculate Payment in Excel | Excel for Finance | Learn Excel

What is ExcelDemy?

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

Mursalin Ibne Salehin holds a BSc in Electrical and Electronics Engineering from Bangladesh University of Engineering and Technology. Over the past 2 years, he has actively contributed to the ExcelDemy project, where he authored over 150 articles. He has also led a team with content development works. Currently, he is working as a Reviewer in the ExcelDemy Project. He likes using and learning about Microsoft Office, especially Excel. He is interested in data analysis with Excel, machine learning,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo