How to calculate monthly payment in Excel (with Excel calculator)

In this article, I will show you how to calculate monthly payment in Excel with some examples and criteria.

How to calculate monthly payment in Excel (with Excel calculator)

Here are the criteria:

  • You have got $50,000 loan on June 05, 2018
  • Yearly interest rate of the loan is 12%
  • You will get 5 years’ time to pay off the loan
  • The set interest rate 12% will not change over the next 5 years for your loan
  • You have to pay an equal amount of money at the end of every month. In this case, your first payment will be on 4th July 2018 (end of the month). In reality, most of the banks will give you some more days to pay the monthly payment.

Here what we are going to find:

How much money (principal + interest) will you pay at the end of every month for the next 5 years?

You get the answer from the above image: ($1112.22) every month.

By default, the amount is shown in the negative format as it is cash outflow. You can change it to positive by just adding a negative sign after the equal sign in the formula.

And here is the result. The value is now positive.

Excel’s PMT function will output you the monthly payment for these types of cases.

So, let’s understand how PMT function works.

Syntax of Excel PMT Function

Here goes the syntax of Excel’s PMT function:

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

So, PMT function has 5 arguments. 3 of them (rate, nper, pv) are required, and 2 of them (fv, type) are optional.

ArgumentDescription
rate (Required)Interest rate per period. Say, you got a loan at the 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 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 will calculate the loan payment, in most cases, this value will be 0. As 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)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.

Let’s check out the mathematics behind the scene

I know it is convenient to use the Excel’s PMT function to find out the monthly payments of a loan. But what if you want to know how the whole formula works behind the scene?

I will not show you the complex mathematics formula here like this one:

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

To know more about this formula, check out this link.

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

I will not show you how this formula has got this face after passing several phases before.

What I am going to show is a clear understanding of the PMT function and its internal states. I found it enjoying when I did it. I hope you will also like it.

In the following image, you’re seeing some data for a loan you took from a bank:

  • Yearly interest rate is 12%. So, per month interest rate is 12%/12 = 1%. So, PMT function’s rate argument is 1%.
  • Principal amount, the amount you took from the bank is $10,000. So, PMT function’s pv is 10,000.
  • Number of years you’re getting to pay off the Principal and Interest is 3 years. 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.

These are the 3 required arguments of PMT function.

And in cell C10, we have used the PMT function and got the value $332.14. The value is positive as I have used negative sign (-ve) before the PMT function. Otherwise, PMT function gives negative values.

Now the enjoying part. I will show you how the whole thing actually works behind the scene.

Think along with me. You will also enjoy this part.

1st payment done. What happens actually?

When you will make your first payment, you will pay the interest for 1 month (compounding interest) and some of the principal amount.

Our principal is $10,000

One-month interest rate is 1% (bankers are clever, they divide the yearly rate by 12, so you have to pay higher than should pay).

So, one-month interest is $10,000*1% = $100

You’re paying $332.14 at the end of first period or month.

So, you’re paying principal = $332.14 – $100 = $232.14.

Then your new/residual principal will be = $10,000 – $232.14 = $9767.86.

In the image, the value 9768 (rounded value of 9767.86) is showing in the cell G2 and the formula is showing on the right.

2nd Payments done. What happens behind the scene?

When you’re making your second payment, you’re going to pay your interest based on the first-period-end principal amount and that is 9767.86.

So, you pay interest 9767.86*1% = 97.6786

Your monthly payment is same and it is 332.14.

So, you pay principal = 332.14 – 97.6786 = 234.46

Then at the end of the 2nd payment, your new or residual principal will be = 9767.86 – 234.46 = 9533.40

See the following image. Also, take a look at the formula that I have used to get this value.

We have used this formula in cell G3: =G2-(m_payment-F3)

m_payment = C10, it denotes the monthly payment

And this formula in cell F3 is to calculate the interest based on last principal: =G2*interest

Interest = C3, it denotes the monthly interest rate.

Now we copy down these two formulas for the rest of cells in the column and we get the following result.

Check all the iterations or the payment periods. You will find, in details, what is actually happening every period.

When you will pay your last payment (36th period), you will pay the interest of amount only $3 and the previous period’s residual principal 329. So, your last payment is 329 + 3 = 332

Calculating monthly payment by using the direct mathematical formula in Excel

This is the mathematical formula that calculates monthly payments:

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

To know more about this formula, check out this link.

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

We can use this formula in Excel to find the monthly payments. Check out the following image.

In cell D7, we have used this formula: =(P*i)/(q*(1-(1+(i/q))^(-n*q))) and in cell D8, we have used this formula: =PMT(i/12,n*q,P,0,0). Both cells are giving the same results. PMT is giving negative value as it is cash outflow. But the numeric value is same.

Where, P = D2, i = D3, n = D4, q = D5

What if payment is done monthly but the interest rate is compounded semi-annually?

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

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:

  • The interest rate will be compounded semi-annually (in every 6 months), right? So, divide 12% by 2 that returns 6%
  • Now payments are monthly. So, over the 6 months of payments, you will pay 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. It is easy now to calculate the value of x from this equation => x = 1.06^(1/6) – 1 = 0.00975879. So, the value of x is 0.00975879

Why cannot we divide the interest rate by 12

We cannot divide the interest rate by 12 when interest compounding is happening two times a year. If you do, you will pay more interest.

For example, say we divide the interest rate 12% by 12 (=1) and calculate the interest for 6 months.

It is: 10,000 (1+1/100)^6 – 10000 = 615.20. So, we are paying total $615.20 interest when we are compounding the interest rate every month. At the end of 6 months, we are paying actually 6.15% interest

But in our case, we should pay 6% after 6 months.

Let’s use now the newly calculate interest rate (0.975879%) for this calculation.

10,000*(1+0.975879/100)^6 – 10000 = 600

So, with the newly calculated interest rate, we are paying overall 6% interest rate at the end of 6 months.

I hope this logic is now clear to you.

Let’s calculate now the monthly payment for a semi-annually compounded interest rate

This was our data.

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

And this is the solution.

We have used this formula in cell C6 = PMT((C2/2+1)^(1/6)-1,C4,C3)

Except for the rate argument, (C2/2+1)^(1/6)-1, you can easily understand the other arguments.

Let’s explain the rate argument:

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

So, this is how you can calculate the monthly payments of a loan when the interest rate is compounded semi-annually.

Let’s make a complete loan calculator using Excel

Using the above Excel calculator, you can easily calculate any kind of loan payment, it can be weekly, monthly, Bi-weekly, etc.

We have used a VLOOKUP table like the following.

Using this VLOOKUP table, we have created a formula in cell E6: = VLOOKUP(C6,List!A1:C9,3,0)

This formula finds out the number of payments per year.

Then we have calculated the total no. of payments in cell E7: E6*C7

The rest is simple. Just check out the file if you have already downloaded it from the download section.

Download Working File

So, these are my ways of calculating monthly payments for loan taken for several years. If you have any feedback, please let me know in the comment box.

Related Online Courses

Read more


Hello! Welcome to my Excel blog! I am conducting deep dives into the world of Excel. Please join with me and explore Excel deeply. Keep in mind this African proverb: "If you want to go fast, go alone, If you want to go far, go together." Let's together explore Excel deeply! ☕

We will be happy to hear your thoughts

      Leave a reply