DISCOVER
×

Video transcription

Hi, my name is Dave Andrews. Today I'm going to show you how to use Excel to calculate APR. Let's open up Excel. I'm going to go to our start button. Let's go to all programs. Let's find Microsoft Office, we want to open up Excel. Now to calculate APR, there are two functions that you're going to be using. The first one is PMT, does calculate what your payments will be for your loan. And the second one is rate which will calculate the actual APR that you're paying on your loan. So let's use our first one. In the A one here, I'm going to type in equals to begin a formula. I'm going to type in PMT for payment. So, let's say the rate that we have been quoted is let's say oh, six point five percent. What we need to do here is type in zero point zero six five which would be six point five percent. We're going to divide that by twelve months, so I just do a divided by and then say twelve. Then a comma. Now the nper is the number of payments period. Let's say we're going to do a thirty year loan. So that will be three hundred and sixty payments. The PV is your principal value, so let's just say it's a two hundred thousand dollar loan. Let's close out our formula here and just say enter, and as you can see, our payment, based upon what we've given, will be twelve hundred sixty four dollars and fourteen cents. Now let's calculate the actual APR that we'll be paying on that. To do so, in the next field here just do an equals sign. Let's type in rate. Now the very first value that rate wants is the number of payments, it's going to be three hundred and sixty payments. Now the payment amount, that's what we just calculated here, you have to do negative because this is money that's going to be coming from you. And type in the answer for what you got above. Now, the PV is going to be your outstanding value on your loan. Let's say two hundred thousand. And we want to calculate it per year so multiply your answer by twelve. And just say enter. As you can see your actual APR is around what we were quoted. Zero point zero six five or six point five percent, but there's going to be a little bit of an increase there actually since we've brought it up to the yearly APR as opposed to the monthly rate. My name is Dave Andrews and I've just showed you how to calculate APR in Excel.