How to Calculate a Monthly Payment in Excel: A Quick Guide

Calculating financial transactions is pretty much a common thing for everyone. And you have to agree, taking loans and paying them off is a monthly scenario for most. However, bringing about your calculator and doing the math each time isn’t the best possible solution.

Microsoft Excel can make the job far easier. But how would you do it? That’s exactly where we come in. In this article, we’re about to discuss how to calculate a monthly payment in Excel. We’re going to elaborate on the basic fundamentals and formulas that make the whole procedure easy.

This article should make your monthly loan payment plans much more doable. So, you’ll want to go through each method thoroughly. With that said, I earnestly hope that you enjoy the read.

Quick and Easy ways to Calculate a Monthly Payment in Excel

Well, we’re gonna explore two effective ways to complete execute the task at hand. One is the PMT function method, and the other is the direct method. I’d advise you to read each procedure step by step to get a better understanding.

Using PMT Function to Calculate Monthly Payment

In this section we’ll try to give you a general idea about the PMT function, how it works, and how to use it. So, let’s get started:

What is the PMT Function?

Well, in this case, PMT stands for Payment. This Function of Excel is used as a financial function that works to calculate the payment for a loan that’s based on a constant interest rate. It also helps to calculate the period numbers and loan amount.

Note: The PMT function was first introduced in Excel 2007.

The Syntax and General Uses

A PMT function in the formula bar sort of has this format:

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

Now, let’s elaborate on the meaning of the Syntax. As we can see, the function requires three data points.

  • The “Rate” is used to denote the interest rate of the loan.
  • Here, “Nper” means number of periods.
  • “Pv” stands for present value. This represents the current value of the sum or the principal of future payments.

There are however two more data points that can be used to execute more specific calculations.

  • Within the PMT formula, “Fv” stands for future value. It basically stands for the balance you wish to achieve after the final payment is made. This value is omitted or assumed to be 0 if the loan is paid off.
  • In the place of “Type” 0 or 1 is placed to determine whether the payment will occur at the beginning or the end of the period.

Usage:

Now that we’ve gone through which term means what, let’s see how the function works in real life application.

Let’s set the values as the following:

  • Interest rate: 6.0%
  • Length of loan: 20 years
  • Amount borrowed: $300,000

The Syntax and General Uses 1

Now, let’s start by typing in “Monthly Payment” beneath the loan details. Next, we’ll have to type in the PMT function on the right of the Monthly payment cell. Here are the steps that you’ll need to follow.

1. Once you’ve written =PMT in the cell/ formula bar.

2. Once you do that, you’ll be asked to put in the data points that we discussed beforehand.

The Syntax and General Uses 2

3. In order to place the interest rate, we have to calculate the periodic rate for one month. In order to do this right, we’ll have to divide the interest rate by the exact number of periods. In this case, 12 (One year).

4. So, here the rate will be 6%/12 or .06/12.

The Syntax and General Uses 3

5. As for the value of Nper, we’ll be wanting the numbers in terms of months. Say, a 20 year mortgage loan will be paid in a total of 240 payments. In our case, we’ll have to multiply B3 with 12.

The Syntax and General Uses 4

6. As we know, determining the Fv or future value is optional. It can be used to calculate a savings goal in place of paying the loan. Since we’re willing to pay the loan off completely, the value will definitely be $0.

7. Finally comes the TYPE. This data too is optional. However, the timing of payments does have an impact on the amount of interest accrued within the month.

8. So, our PMT function should finally look like the image below.

The Syntax and General Uses 5

9. And that’s pretty much it. Once you press enter, you should be able to see the desired results.

The Syntax and General Uses 6

Using PMT Function with Compounded Period

Suppose you want to get the semi-annual payment rate. What would you do? In cases like this, applying compounded period might prove to be useful. Let’s discuss in detail.

1. Let’s change the arguments with the values given below:

  • Loan amount $10,000
  • Interest rate 14%
  • Monthly Payment
  • But the interest rate is compounded semi-annually
  • Payment period 1 year = 12 months

2. The first thing you want to do here is, compound the interest rate semi-annually.

3. This means, 14% will be divided by 2. Which returns 7%.

4. In this case, the payments are monthly. So, over six months of payments, the interest rate is 6%.

5. Suppose, x is the monthly interest over 6 months of payment. Then, the equation should be (1+x)^6 = 1.06.

6. Now, if you want to calculate the value of x, the equation should be: => x = 06^(1/6) – 1 = 0.00975879.

7. If applied in Excel, the formula should appear as the image below.

Using PMT Function with Compounded Period

8. Once the formula is applied, press enter to get the desired results.

Using Direct Formula

Look at the mathematical formula below. This is the basic way to calculate monthly payments.

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

Check the values given below for a clearer idea.

  • M = monthly payments
  • P = Principal amount
  • = the Interest rate
  • q = the number of times a year you’ll be making the payments
  • n =the number of years taken to pay off the entire loan and the interest.

Just follow the steps below to do it right.

  • To execute the formula, we’ll be using the sets of data given in the image below:

Using Direct Formula 1

  • Now, all we have to do is, place the formula in the Monthly Payment in cell C7:

=(C3*C4)/(C6*(1-(1+(C4/C6))^(-C5*C6)))

The results should come out as the image below:

Using Direct Formula 2

Concluding Words

As we’ve discussed all the possible ways to calculate a monthly payment in Excel, you should be well aware of which one is suited to you by now. The PMT method is the most common one used worldwide. The second one is more suited for a bit more complicated calculation.

Of course, if you want to go manual with your data, the best way to go about is the manual method. I earnestly hope that all the information helped you out. So, farewell and see you in the next one.

Leave a Comment