PMT Function
Never buy a home/car without it
So I figured I’d start helping good ole John Q. Public by giving some insight on one of the most helpful personal (and business) use Excel functions there is: the payment function. This function (“=PMT()”), is INCREDIBLY USEFUL…AND ACCURATE. And when I say accurate, I mean within pennies of the mortgage broker’s official numbers.
Now remember: Garbage in, Garbage out, though. With bad inputs, you'll get an inaccurate output.
What this function does is tell you what you’re expected payment will be on any loan based on the three values you need: (1) loan amount (or present value), (2) interest rate, and (3) length of the loan. As long as you have good information (educated guesses count, too), you’ll get good output.
So let’s break it down: You’re wanting to buy a house. And that house is looking like you’ll have to buy it for $300,000. While that part will forever be as uncomfortable as swimming with old European dudes, at least we can have some solace in knowing what your payments will be before you even get to the showing.
Next, you’ll need the rate and terms. These are usually easy numbers to get. There are plenty of sites that will tell you the base mortgage rates for the type of loan you’re looking to get. Here’s a tip though—add half a percent to account for the fact that the advertised rate for people who went to school to learn how to have an 800 credit score. So if the going rate is 4%, then you’re at least looking at 4.5% with good credit.
With bad credit, you maybe should rethink this decision altogether.
Lastly, you’ll need the length of the loan (or Pv). This part is easy enough—how long do you want to put off having fun on hold—10 years? 15? 30 years sounds fantastic.
So the last thing you need to do is remember that the rate and length need to be shown in their monthly versions. Why? Because you’re trying to figure out your monthly payment. Since your interest will be reduced every month you pay, you can just list these at the yearly amount, then divide by 12.
To make the rate and term into monthly figures, you multiply the term by 12, and divide the rate by 12. Once that’s done, you can finally get to the meat of this post.
Above, I have shown two different methods you can used to get the right numbers.
And now, ladies and gentlemen, it’s time for the magic show.
Functions in Excel can be accessed in one of two ways: (1) by hitting the equal sign, then typing the formula, or by hitting the ‘fx” box next to the formula bar in Excel (the green thing below).
Lastly, just type in the total loan amount as your “Pv” (present value), and your monthly rate and loan length as “Nper” (number of periods). Ignore “Fv” and “Type.”
And they’re you have it! Sticker Shock!
This works even better for cars, as there aren’t as many added fees like property tax, closing costs, and insurance that will seriously affect the loan amount and monthly payment.
Thanks for reading!
Legal Notice: LampPost Office Automations is not a mortgage broker, and the value of this function is only as good as your research. The numbers will not be exact, but with good research should be close enough to not be too surprised on closing day.