ExcelInsider Team
If you are considering taking a loan, you may want to calculate the monthly payment required to repay the loan. In Excel, it is possible to create a loan payment schedule based on the loan amount, interest rate, and the loan terms.
There are essentially two methods that allow you to do so. You can either use the function provided by Microsoft or you can apply the formula that is used to calculate the monthly payment manually. In this article, we will learn how to calculate the monthly payments on a loan in Excel.
➤ Insert this formula to calculate the monthly payment:
=-PMT(B2/12,C2*12,A2)
➤ Replace B2 with the interest rate, C2 with the loan term in years, and A2 with the loan amount.
➤ Autofill other rows in the column if you have multiple options for loan terms and interest rates.
That is not the only way to calculate a monthly payment in Excel, and you might want to know the methods in detail so that you can manipulate the formulas yourself. Read the whole article to learn more, and don’t forget to download the file to follow along.
Using PMT Function to Calculate Monthly Payment on a Loan
For this example, we have a dataset with the same loan amount of $100,000.00. There are multiple annual interest rates and loan terms. As there are multiple options, we are free to choose the loan option we like. In order to decide which one is the best, we need to calculate the monthly payment. Then, we can select the lowest option or the lowest term along with the lowest payment option if we want.
➤ Create another column for the Monthly Payment ($). We are selecting column D for this.
➤ Write this formula in the D2 cell:
=-PMT(B2/12,C2*12,A2)
➤ Go to the bottom-right of the D2 cell to find the plus (+) sign. Click on that, and drag the sign to the D11 cell to fill the entire column and calculate the monthly payment for all of the options.
➥ The second parameter, C2, is the loan terms in years; we need to multiply it by 12 because there will be 12 payments per year. The last parameter, A2, is the loan amount. We need to put a minus (-) at the beginning of the formula because Excel calculates PMT in negative, and putting a negative sign makes the result positive.
Manually Calculating the Monthly Payment on a Loan in Excel
The PMT function, as we used earlier, is derived from a formula that is used to calculate the monthly payment. In this method, we are going to use the original formula that manually takes the cell references and calculates the monthly payment. Let’s begin:
➤ Use a separate column like before to use the following formula:
=A2 * ((B2/12) * (1 + (B2/12))^(C2*12)) / ((1 + (B2/12))^(C2*12) – 1)
➤ Autofill the other rows in the column to get the monthly payment for all of the loan options.
➥ Here, P is A2, r is B2, and n is C2. The rate (r) is divided by 12 to calculate the monthly rate, and the period (n) is multiplied by 12 because there are 12 payments in a year.
Frequently Asked Questions
What is monthly loan repayment?
When you take a loan from an institution, the institution needs you to pay the money back to them with interest. However, you are not able to give the money back right away because if you could, you wouldn’t have taken the loan. Instead, you are given the option to pay the money back on a monthly basis. That is the monthly loan repayment for you.
How to calculate monthly interest on a loan?
You can calculate the monthly interest on a loan by dividing the yearly interest by 12. Here is the formula to do that in Excel:
=0.1/12
Here, 0.1 represents a 10% interest rate, and it is divided by 12 to get the monthly interest rate.
Which loan has the lowest interest rate?
The interest rate of the loan will depend on your credit score, your financial status, your mortgage, and the terms of repayment. Collect information from your local banks to find the lowest interest rate suitable for your purpose.
How to calculate daily interest on a loan?
In this article, we have calculated the monthly payment, so we had to divide the interest by 12 and multiply the terms by 12. But if you have to calculate the daily interest, you need to use 365 instead of 12 for both cases. If you just want to know the interest rate, divide the yearly rate by 365, unless it’s compounded.
How to calculate interest in Excel?
The simplest formula to calculate interest in Excel is the following:
=A1 * A2 * A3
You need to replace A1 with the loan amount, A2 with the interest rate, and A3 with the number of years the loan is for.
Wrapping Up
In this article, we have learned how to calculate the monthly payment on a loan in Excel. If you still have confusion, leave your concerns in the comments below. Bookmark our site so that you can come back for more Excel tutorials like this one.
