Calculating credit card payments in Excel 2010

Shows how to calculate the credit card payments and acrued interest using Excel 2010.

This video will show you how to use excel to calculate your credit card payments so let’s consider the following situation the following problem where we buy an item for $1000 let’s say we have a credit card where the annual interest rate is twelve point nine nine percent and let’s say that we can make a monthly payment of one hundred dollars the question we have

Is how many months will take us to pay off $1,000 item that we bought and how much interest will we have to pay over that period of time we’ve created a set of columns to help us in this in this calculation so we have a column that will hold the month column that will hold the payment the column that will hold the interest that we pay at the end of that month and

The balance on our account so for month one the payment that we will make is a hundred dollars because again that’s the monthly payment we’re making the interest that we have to pay on that payment is going to be the item cost which is a thousand dollars times the annual interest rate divided by twelve we divide the annual interest rate by twelve because we want

To determine the interest rate that we pay on the balance during that one month if we take the amount that we started with and subtract our $100 payment and add the interest payment then that will tell us what the balance is so you notice that the balance is the item cost minus the payment plus the interest so after the first month our balance is nine hundred and

$10.83 the interest we paid over the first month was $10.83 okay during the second month again we make a payment of $100 because a balance is greater than or equal to $100 if the balance have been less than $100 then our payment would be less than $100 can the interest we pay is going to be the annual interest rate vitamin twelve times the balance which in this

Case is nine hundred and ten dollars and eighty three cents so the payment during the second month is a hundred dollars in the interest that’s accrued on our account is nine dollars and 86 cents so the balance is going to be simply the balance after the first month – our payment plus the interest and we continue that process down to the tenth month and again we

Can do that by simply taking that and dragging it down now in the eleventh month if we try to drag if we try to write the ten months eleven month what you’ll notice is our balance is negative so during the eleventh month we don’t have to pay a hundred dollars because the balance at the beginning of the levant month is less than a hundred dollars okay the amount

That we have to pay in the eleventh month is going to be the balance at the end of the tenth month plus the interest at accrues during the eleventh month so if we take 63 53 + 69 cents that gives us 64 dollars and 22 cents we do that but we do that you’ll notice that our balance is zero which is what we want so we want to balance be zero after after the for the

11th month so for this particular problem we notice that it takes us 11 months to pay off the item okay okay the amount of interest that we pay over those 11 months is $64 and 22 cents and we also have to obviously pay the cost of the items so the total amount that we pay is one thousand one thousand dollars and sixty one thousand sixty four dollars and 22 cents

Let’s change a problem little bit let’s go ahead and increase the the cost of our item to two thousand dollars and see what happens to our our payments and amount of interest that we pay and the number of months it takes for us to pay off this particular loan so again we do all the same things we drag down and what we notice is that it’s going to take 23 months

The amount of interest paid is two hundred sixty six dollars and 19 cents and the total amount that we pay is 2226 sorry two thousand two hundred sixty six dollars and 19 cents what’s interesting about this as we doubled the cost of the item but the amount of interest that we have to pay one up by more than a factor of four so the final problem we want to look at

Is what happens if we increase our monthly payment to two hundred dollars that should reduce the number of months that we have to pay and indeed it does you’ll notice that in this calculation here that the amount of interest paid is one hundred twenty dollars forty four cents the total amount of our payments is two thousand one hundred twenty eight dollars and

Forty four cents two thousand dollars of that is just the cost of the item plus we have to pay the interest this concludes our video on how to use excel to determine the payment and interest on a credit card loan

Transcribed from video
Calculating credit card payments in Excel 2010 By Tom Brown