How to Calculate Loan Payments in Excel Without AI

-

PMT formula

 
PMT formula sample

In the late 1990s, 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.

Looking back, knowing the PMT formula would have let us walk into the bank with an exact number instead of guessing our way through every repayment plan. What I never learned back then, I now teach my college students.

A Formula Even the Pros Often Miss

I'm not the only one who found this formula unfamiliar. Several of my students who already work as accountants told me they'd never encountered PMT before, and they were genuinely grateful to learn it—proof that even seasoned practitioners often miss it.

According to Statistics Canada, where I currently live, 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 sample loan data in Excel—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. 

1. 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. 

2. B2*12 converts the loan term from years into months, since each payment period is one month. 

3. 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.

Why This Still Matters in the Age of AI

Some people might push back and say AI can now handle these calculations for you. And that's true—it can. But accepting a number AI hands you is a very different thing from understanding how that number came to be.

Understanding the structure of the formula lets you verify the result yourself when something looks off, ask a loan officer to explain—or explain it to them yourself—why a payment came out the way it did, and make sound decisions even when the internet is down or no tool is in front of you. Someone who only receives an answer and someone who understands the mechanics behind it stand in very different positions, whether at the negotiating table or drawing up a budget.

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 other post, After 35 Years With Apple, I’m Thinking About Switching to Samsung



* The top image was created by Nano Banana 2

Comments

Post a Comment

Popular posts from this blog

Starting This Blog Again.

-
Image
Hello, and welcome.  A lot has changed in technology since I started working in IT, and I've changed along with it. Over the years, I've worked in different IT roles, from technician to business owner, tutor, and eventually college instructor. Through all these years, one thing has remained constant: learning. That's one of the reasons I've decided to start writing again. This blog will mainly focus on practical IT knowledge and real-world experience—computers, networking, troubleshooting, software, hardware, and other technology-related topics. I'll try to keep things clear, practical, and accessible. But it won't be only about technology. I'll also be sharing stories from my military days . In South Korea, military service is compulsory for most men—and I went in later than most. And as if going late wasn't enough, I ended up at a Ranger School known for exactly one thing: turning you into someone who crawls, rolls, sweats, and questions every life cho...
Read more

Where Do Army Helmets Go?— A Strange Equation Called "Military Math"

-
Image
I went into the army later than most. While other guys came in after their freshman or sophomore year of college, I showed up after finishing grad school—married, no less. So soldiers years younger than me outranked me, and they called me "old man." Being the oldest rookie in the unit made me an easy target for teasing. I'd just been assigned to my unit when the order came down: report for Team Spirit training—the joint U.S.-Korean military exercise. I'd been pulled out as an interpreter. Barely settled in, and already being shipped off again—the thought of adjusting to yet another new environment put me on edge. On my first day at the training site, I was wandering around checking out the base when someone called my name. I turned around—a friend from college! "Hey! Long time no see. You're here for Team Spirit too, huh?" Seeing an old friend there, in uniform and in the middle of a war exercise, felt strangely like meeting up on a real battlefield. He...
Read more

Slide for Life

-
Image
Three Shouts Between Life and Death This happened when I was serving in the military as a Ranger instructor for U.S. soldiers. The American soldiers camped out in a valley at the Ranger school. There were more than thirty courses, ranging from PT and basic obstacles to mountain obstacles, and none of them could be called easy. On top of that, the training was dangerous. What worried me most wasn't the training itself, but safety. On some of the courses, one small mistake could lead to a serious accident, so I repeated the safety instructions again and again. The most dangerous course was called "Slide for Life."  In this exercise, soldiers rode a metal cable about 150 meters down a hillside, dropped into a pond, and then swam to shore. It was a tough course. The hill itself was about the height of a ten-story building, so simply standing at the top could be psychologically overwhelming.  Soldiers with a fear of heights were allowed to do PT instead. But in fact, the most ...
Read more