# How to Calculate Monthly Payment in Excel (2 Handy Ways)

Get FREE Advanced Excel Exercises with Solutions!

To calculate the monthly payment for repaying a loan, you may use a direct formula or use a pre-existing function in Microsoft Excel. Both ways, you can get your desired result. In this article, I will show you how to calculate monthly payment in Excel.

## How to Calculate Monthly Payment in Excel: 2 Handy Ways

In this article, you will see two different ways to calculate monthly payments in Excel. Firstly, I will use the conventional or direct formula to calculate the monthly payment. Then, I will take the help of an Excel function in my second method to do the same.

To illustrate my further procedures, I will use the following data set. Consequently, I have the loan amount, yearly interest rate, total number of years to repay the loan, and payments per year to be made. ### 1. Using Direct Formula to Calculate Monthly Payment

This is the mathematical formula that calculates monthly payments:

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

Here,

• M is monthly payments
• P is the Principal amount
• i is the Interest rate
• q is the number of times a year you will make the payments
• n is the number of years you get to pay off the whole loan and its interest

Consequently, we can use this formula in Excel to find the monthly payments. Check out the following steps.

Step 1:

• First of all, write the following formula in cell D9.
`=(D5*D6)/(D8*(1-(1+(D6/D8))^(-D7*D8)))`
• Additionally, I will assume the cell values are the terms from the main formula. Step 2:

• Secondly, press Enter to see the monthly payment for repaying the loan.
• Furthermore, the user has to pay this amount for three years to repay the loan. Read More: How to Calculate Loan Payment in Excel

### 2. Applying PMT Function to Calculate Monthly Payment

In my second approach, I will use an Excel function which is the PMT function. This function after inserting the correct arguments and giving proper syntax will show the payment to repay a loan as a result.

Summary:

• The PMT function determines the payment to repay a loan where a fixed interest rate is provided.
• Available from Excel 2007.

Syntax:

The formula or syntax for the PMT function in Excel is,

`PMT(rate, nper, pv, [fv], [type])` Arguments:

Arguments Required or Optional Description
rate Required Interest rate per period. Say, you got a loan at a yearly interest rate of 12%.
• Make payment monthly. Per period interest rate is 12%/12 = 1%.
• Make payment quarterly (every 3 months). Per period interest rate is 12%/4 = 3%.
• Make payment half-yearly (every 6 months). Per period interest rate is 12%/2 = 6%.
nper Required The total number of payment periods. Say you got the above loan for the next 5 years.
• Make payments monthly: Number of payments = 5*12 = 60.
•  Make payments quarterly: Number of payments = 5*4 = 20.
• Make payments half-yearly: Number of payments = 5*2 = 10.
pv Required The present value. Simply, it is the loan amount you receive.
fv Optional Future value. When you calculate the loan payment, in most cases, this value will be 0. At the end of your last payment, there will be no balance with the bank. If you don’t use this value, PMT will assume this value as 0.
type Optional The type takes two values:
• 0 or omitted: When you use 0 or omit this argument it means that payments are made (or due) at the end of the period.
• 1: When the type is 1, it means payment will be due at the beginning of the loan period.

Return:

The PMT function returns the payments to repay the loan as a value.

#### 2.1 Utilizing PMT Function

Now, after discussing the PMT function, I will demonstrate its application to calculate the monthly payment. For that, see the below-given steps.

Step 1:

• First of all, insert the following formula of the PMT function in cell D9.
`=PMT(D6/12,D7*D8,-D5,0,0)` Step 2:

• Secondly, after pressing Enter, considering all the given arguments, the function will show the monthly payment.
• Here, The yearly interest rate is 12%. So, the per-month interest rate is 12%/12 = 1%. So, the PMT function’s rate argument is 1%.
• The principal amount, the amount you took from the bank, is \$10,000. So, the PMT function’s pv is 10,000.
• The number of years you’re getting to pay off the principal and interest is 3 This is a monthly payment, so the total number of periods you will get is 3 years x 12 = 36 months. So, nper is 60.
• Finally, in cell C10, the PMT function will show a value of \$332.14. The value is positive as I have used a negative sign (-ve) before the loan amount. Otherwise, the PMT function gives negative values. #### 2.2 PMT Function with Compounded Period

Let’s show you something different than what we have done so far.

Check out this scenario:

• Loan amount \$10,000
• Interest rate 12%
• Monthly Payment
• But the interest rate is compounded semi-annually
• Payment periods 3 years = 36 months

A little bit critical case.

Think along with me:

• Firstly, the interest rate will be compounded semi-annually (every 6 months), right? So, divide 12% by 2 which returns 6%.
• Then, payments are monthly. So, over the 6 months of payments, you will pay an overall 6% interest rate. If you think mathematically then, it will be like (1+x)^6 = 1.06 where is x is your monthly interest over the 6 months of payments. So, it is easy now to calculate the value of x from this equation => x = 06^(1/6) – 1 = 0.00975879. So, the value of x is 0.00975879.

Now, see the following steps to apply this concept in Excel.

Step 1:

• Firstly, insert the following formula in cell C7 to calculate the monthly payment.
`=PMT((C4/2+1)^(1/6)-1,C6,-C5)` Step 2:

• Then, hit Enter to get the desired result. Formula Breakdown

(C2/2+1)^(1/6)-1:

• The value of C2 is 12%, so C2/2 = 12%/2 = 6%
• C2/2+1 = 6% + 1 = 06
• So, this part of the formula comes into this form: 06^(1/6) – 1 which results in the value 00975879.

## Calculating Monthly Interest Rate in Excel

Aside from calculating the monthly payment, you can also calculate the monthly interest rate in Excel. In order to do that, you will have to use the RATE function of Excel which returns the interest rate per period of a loan. See the below-given steps to calculate the monthly interest rate on loans in Excel.

Step 1:

• Firstly, to calculate the interest rate, take the following data set with all the necessary arguments.
• Then, in cell D8, use the following formula of the RATE function.
`=RATE(D5,-D6,D7)` Step 2:

• Secondly, after pressing Enter, you will see the monthly interest rate is 1%. ## Calculating Principal and Interest on a Loan in Excel

Additionally, you can calculate the principal and interest on a particular loan in Excel. In order to do that, you will need the help of two different Excel functions to do. To calculate the principal, you will have to use the PPMT function, and to find out the interest, you will need the IMPT function of Excel.

Step 1:

• In the beginning, take the following data set with all the necessary arguments. Step 2:

• Secondly, to calculate the rate, use the following formula in cell C8.
`=C6/C7` Step 3:

• Thirdly, press Enter to get the value for rate in cell C8. Step 4:

• Fourthly, to calculate nper, input the following formula in cell C11.
`=C10*C7` Step 5:

• Then, after pressing Enter, you will get the result in a numeric value for nper. Step 6:

• Afterward, I will calculate the principal and interest after finding out all those previous values.
• To calculate the principal, type the following formula of the PPMT function in cell C4.
`=PPMT(C8,C9,C11,-C5,C12,C13)` Step 7:

• Afterward, press the Enter button to get the desired result. Step 8:

• Then, to calculate interest, insert the following formula of the IPMT function in cell C15.
`=IPMT(C8,C9,C11,-C5,C12,C13)` Step 9:

• Finally, get the desired result by pressing Enter, after inserting the above formula. ## Conclusion

Therefore, after commenting, please give us some moments to solve your issues, and we will reply to your queries with the best possible solutions ever.

Compound interest is the interest that’s calculated both on the initial principal of a deposit or loan and on all previously accumulated interest. In this article, we are going to learn the compound interest formula in Excel.

## 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 projects. Kawser Ahmed

Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share them here. Not only a how-to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned! You can check out my courses at Udemy: udemy.com/user/exceldemy/

1. Reply i was thinking if you can make a dashboard for a few rental property just the thought

• Reply Thanks for the suggestion 🙂 I will keep it on my list.

2. Reply Do you have daily rest interest calculation? I don’t see in your worksheet.
Thanknyou

3. Reply In case it wasn’t posted.
Home loan has daily compounding interest calculation.
Thank you

4. Reply Would you mind if you show how to calculate interest compounded monthly on an outstanding late invoices.
for example, several invoices haven’t been paid after the due date, for a number of customers, and we’re going to charge 7% (Yearly), compounded monthly. how to keep track for the ongoing balance on a table for all customers ?
Hope you can find way to make it on one table..
Thank you …

• Reply Hello Wasim,
I am keeping this on my to-do list. Will try to give you a solution of your problem.
Thanks. Advanced Excel Exercises with Solutions PDF  