Skip to main content

The PMT Formula: How to Calculate Loan Payments in Excel(Without AI)


 

How to Calculate Loan Payments in Excel without AI

In the late 1980s, I worked as a general manager in the supply chain management department at a peripheral device distributor in Korea. Back then, no one in our department had any idea about the PMT formula, which calculates a fixed monthly loan payment. That formula was first introduced in 1985 in Excel 1.0 for Macintosh computers, so it already existed—we simply didn't know about it.

Our company often had to borrow large sums of money from local banks to pay for big shipments. Looking back, knowing the PMT formula would have saved us a lot of guesswork and helped us plan our finances far more accurately.


Now I teach this formula to my college students, and often they tell me how useful it is. It's not just students, either—according to Statistics Canada, *about 35.5% of Canadian homeowners currently carry a mortgage*. If more of them understood how to calculate their own monthly payments and grasped the logic behind the number, they'd be in a much stronger position when negotiating loans or planning a budget.

The formula

Suppose we have the screenshot of an Excel file with sample loan data as you see in the top image—principal amount, interest rate, and loan term—and you want to calculate the monthly payment for each loan. 

The formula looks like this:

=PMT(Monthly Rate, Number of Payments, Total Payment Amount)

So, for a loan listed in row 2, with the annual interest rate in cell B7, the loan term in years in column B, and the loan amount in column C, the actual formula in cell D2 would be:
=PMT($B$7, B2*12, -C2)

A few things are worth explaining here. First, the annual rate in B7 needs to be converted to a monthly rate (usually by dividing by 12) before it's used, since PMT calculates payments per period, not per year. Second, B2*12 converts the loan term from years into months, since each payment period is one month. Third, the loan amount is entered as a negative number. 

This is because Excel treats the loan as money flowing out from the lender's perspective, so the result comes back as a positive value representing the payment you owe.
Once the formula is in D2, you can simply copy it down to D3, D4, and D5, and Excel will automatically recalculate the monthly payment for each loan using the corresponding row's data, while the dollar signs in $B$7 keep the interest rate reference fixed.

I've had several students who already work as accountants tell me they'd never encountered this formula before, and they were genuinely grateful to learn it. Understanding PMT, especially when combined with other essential formulas like XLOOKUP, VLOOKUP, and HLOOKUP, creates a real synergy for anyone working in accounting, finance, or paralegal roles. If you're interested, you can check out my earlier posts on those lookup formulas:

I know some people might push back on this and say AI can now handle these calculations for you. And that's true—it can. But what happens when there's no internet connection, and you need the answer right away? In that moment, relying entirely on AI leaves you stuck.
Even in this AI-driven era, I believe it's still worth understanding how things work under the hood. Knowing the structure behind a formula like PMT doesn't just help you get the right number—it gives you the ability to explain the process to someone else, troubleshoot when something looks off, and make sound decisions even without a tool in front of you. That kind of understanding never really goes out of style.


* The top image was created by Nano Banana 2

Labels:

Comments

Post a Comment

Popular posts from this blog

My Journey: From Korea To Canada

About 27 years ago, my family and I made one of the biggest decisions of our lives—we immigrated to Canada. Like many newcomers, we came with hope, determination, and dreams for a better future. Our primary goal was to provide a better education for our children, a happier and more stable life for our family, and greater career opportunities for me. Although we were excited about the future, starting over in a new country was never easy. Everything was unfamiliar. We had to adapt to a different culture, a different way of life, and, most importantly, a new language. The language barrier made even simple daily tasks challenging. Every day became an opportunity to learn something new, and every small success felt like a big achievement. Before coming to Canada I had already built a solid career in the information technology industry in Korea. I worked for several IT-related companies, including an Apple reseller, a server company, a distributor of computer peripherals, software, and stor...
Post a Comment
Read more

My YouTube Channel about Digital Technology

I started my career as a ranger instructor before moving to Canada. Since then, I have taught a lot of students who were soldiers, employees, children, or college students from around the world. My students have come from North America, Africa, Asia, and Europe. Even without travelling by myself, I have been able to experience different cultures through my students.  I love teaching, communicating, and troubleshooting.  That's why I started the YouTube channel to help students learn, grow, and succeed. These are the current playlists of my YouTube channel. I'll try to upload as often as possible. If you need any new posts that you are interested in, let me know.  As you can see on the thumbnail, there are six different categories:  Photoshop HTML and CSS Excel Word Illustrator InDesign I'm also planning to add more categories soon, including AI and a career coach.  If you are interested in a specific topic, you can go directly to that playlist to save time. 1. ...
Post a Comment
Read more

Fiction - I Lost My Combat Helmet

In South Korea, virtually all healthy men are required by law to complete mandatory military service, typically between the ages of 18 and 28. Today the term is around 18-21 months, but in the 1990s it was considerably longer—roughly 27 to 30 months, before being gradually shortened in later years. This series is based on my own experience serving during that era. About to receive my permanent base assignment, I still couldn't get the word “soldier” to roll naturally off my tongue. The training uniform didn’t fit my body well, and my combat boots always felt a size too big or too small. To a private second class who had enlisted only a few weeks earlier, the military was still less a “place to live” and more a “place to endure.” Yet the reason I ended up participating in Team Spirit (1988) training was simple. It was because I could speak English better than other Korean soldiers. That was the only reason. Since it was a joint training exercise with the US military, translation ass...
Post a Comment
Read more