In this video we're going to figure out how to calculate our loan payments using the payment formula in Microsoft Excel.
So, what you have here is an imaginary scenario, that I created, where we have a 3.25%
interest rate, a 5 year loan and the loan amount is going to be $50,000.
And then some loans have additional charges so I just made up some insurance and tax numbers. But, to get going, to figure out the
base loan payment, we're going to use the payment formula and that formula is =PMT. So,
this formula request a number of things, so we're going to click up here so I can walk you through what those are.
The Rate, the first one, the Rate, is your interest rate. So in this case it's 3.25%. We're going to just click on the cell that has a 3.25%.
If you type this in manually and you don't reference a cell, just know that you do have to put the % sign at the
end of it or Excel won't calculate this as .0325, you'll end up
with a number 100 times, um, greater than it should be. So, once we do that, we need to let
Excel know how many payments we're making in the period.
So the period here is a year. We're going to make make monthly payments, so I'm going to divide that by 12.
So Rate is
F2 ,or 3.25%, divided by 12. The next thing is the Nper. So that's the number of payments.
In this case, it's a 5 year loan, 12 payments in a year, so that's 60 total payments.
So, I'm actually going to put in a formula of 5, I'm going to reference the cell that says 5, and I'm going to
multiply that by 12. And you see how, here, Excel's already calculated that it's 60.
Um,
so that's our total number of payments. Then the PV is your Present Value - that's the amount that you're financing. In this case, it's
$50,000. So, I'll reference that, um, $50,000.
Future Value is the amount you want your loan to be when you're done with all of your payments.
So, for most loans, that's going to be zero, so we'll just type in 0.
Then Type refers to when you make your payment - at the beginning or end of a period.
So, the default is 0, which is what we'll use. You can use 1 if you know that you're going to be making your payment at the beginning of the
period. To be completely honest, if you type the wrong one in, you're only going to be off by a few dollars,
so, you'll still have a realistic range of what you're going to be paying.
So, we do this and we click Ok. And we can see that we're paying $904 every month,
for a $50,000 loan,
over 5 years at 3.25%.
Now, one thing that I'm going to do here is, Excel automatically makes this a negative formula. Why? Because this is money you're
paying out, um, so it makes it negative. So, I want it to be positive, just for my own, um, calculation here. So I'm just going to type a
negative sign here and now we have a positive number.
So, the reason I reference these cells instead of actually typing them in is because
this way I can see how my formula changes if I change what's in those cells. For example,
I'm currently paying $904
on a 3.25% loan. But, let's say I instead qualify for a 4.5% interest rate. Now my payment has gone
up to $932. But, let's say I want to take it out to 15 years,
now my payment for the $50,000 loan is $382.
And, if the loan changes, let's say $75,000 it's now $573.
So, doing that lets you play around with the loan amount a little bit easier and you can just change your reference points, um,
however your situation, your scenario changes.
So, let's pretend, for example, that this is a house, and we have additional, um, payments that we'll need to make every year. So we'll
have insurance and taxes. These numbers may not make sense. I made them up, um,
it's just for illustration. So, um, but to see how that happens or how that changes what we pay out every month,
we'll say equals the $800/12 and that's all we do. And that'll add $66.00.
And then we can do the same thing for the taxes $416, $5,000/12. And then what we can do is say, ok, so if our loan payment is
$573.74, we're paying additional insurance of $66.67 and
taxes of $417, we just add all those together and we know what our total monthly, um, payment will be
for all of the items. So, if you're calculating just your loan,
all you have to worry about is this top part and all you'll pay is $573. If you want to add in other
variables other expenses that'll be associated with the loan, um,
you can calculate those and then just add them in. So, I hope you found this video to be helpful and, if you enjoyed it, please do make sure to
give it a Like and subscribe to this channel so you get the video notifications soon as new videos come out. Thanks!