Mursalin Ibne Salehin
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.
Download Practice Book
You can download the practice book from here.
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 Easy 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.
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
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.
- Thirdly, we need to find the total number of payments.
- To do so, select Cell C8 and type the formula below:
=12*C7
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.
- In the following step, select Cell C11 and type the formula below:
=PMT(C6,C8,C9,0,0)
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.
- Moreover, you can use the picture below to understand the arguments clearly.
Read More: How to Calculate Loan Payment in Excel (4 Suitable Examples)
Similar Readings
- How to Calculate Coupon Payment in Excel (4 Suitable Examples)
- Calculate Auto Loan Payment in Excel (with Easy Steps)
- How to Calculate Car Payment in Excel (with Easy Steps)
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)))
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
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. Also, you can visit the ExcelDemy website for more articles like this. Lastly, if you have any suggestions or queries, feel free to ask in the comment section below.
