Quick Tip: How to Use Excel to Calculate the Monthly Payments on a Loan

Most of us probably forget much of the math we learned in high school or college. There are times, however, when a little math can help us with our finances. One such time is when we need to calculate the monthly payment on a loan

Whether we are considering a car loan, student loan, or perhaps a home loan, it’s a snap to calculate the monthly payment using Excel.

Let’s say you’re thinking about buying a car. For regular readers of the Dough Roller, you know my strong recommendation is never to borrow to buy a car. But stick with me here.

There are of course online calculators that you can use to find the monthly payment. But, frankly, I prefer to use Excel because it’s super easy and you can quickly change the interest rate, loan amount or length of the loan to see how the monthly payment will change.

So here’s how you do it:

In Excel, you’ll see at the top, above all the cells, the function bar.

Excel function

(By the way, if you don’t have Excel, you can do this same thing for free in Google Drive. Just open up a spreadsheet in Drive and the functions are all the same. That’s actually what I’ve used to take these screenshots.)

In the function box, type the equal sign (=) and the function. In this case, we’re going to use the Payment function, which is abbreviated as PMT. So your function is “=pmt”:

pmt

Then, we’re going to enter three numbers in the parenthesis. The first is the interest rate. Since we’re going to calculate the monthly payment, we want the monthly interest rate.

The easiest way to do that is to enter the interest rate as a decimal divided by 12. For example, a 5% rate would look like this: .05/12.

Next, we’ll enter the number of months on the loan. For a five-year loan, for example, you would enter 60. Lastly, we’ll enter the amount of the loan. In this example, we’ll assume a $20,000 loan.

Close the parenthesis to end the function and hit enter.

Full equation

In the cell you’ll find your monthly payment. In this case, it’s about $377.

Payment

It’s as simple as that.

Note that the number is negative because it represents payments you’ll be making on the loan. If you prefer to see this as a positive number, you can enter the loan amount as a negative number in the formula (e.g., -20000).

To see how the payment would change with different assumptions, simply change some or all of the numbers in the formula. For example, if we change the loan amount to $10,000, Excel immediately changes the payment to $188.

You can also change the interest rate and loan term. For example, let’s assume we want a $10,000 loan over a 30-month period instead of 60 months. Changing the formula to reflect these loan terms results in a monthly payment of $355.

Changed payment

Who knew Excel could be so much fun?

Published or Updated: March 19, 2014
About Rob Berger

Rob founded the Dough Roller in 2007. A litigation attorney in the securities industry, he lives in Northern Virginia with his wife, their two teenagers, and the family mascot, a shih tzu named Sophie.

Comments

  1. David S. says:

    That excel formula is too simple, instead you need something like this:

    =-FV(H31/12, IF(YEAR(H34)=YEAR(NOW()),13-MONTH(H34),12), -B32, -FV(H31/12,MAX(0,13-MONTH(H34)+12*(YEAR(NOW())-YEAR(H34)-1)),-B32,H32))

    Where H31 is the APR, H34 is the Date of your first mortgage payment, B32 is your monthly mortgage payment (or -PMT(H31/12,H33*12,H32)), H33 if the loan length in years, H32 is the original loan amount.

    That way you can see what your balance will be by the end of the year if you just make the standard payment.

    • Rob Berger says:

      David, you’re an Excel Ninja!

Speak Your Mind

*