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.

Before we get to Excel, however, there is an easier way. Use a loan payment calculator. I created one that you can check out here.

Now back to 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.

(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":

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.

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

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.

Who knew Excel could be so much fun?