Tom & Vicky ask about using Excel for basic household finance. Excel has a great suite of functions for this. Today, a look at using NPER to pay off your credit cards faster. Episode 998 shows you how.
This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Basically, we start out with massive amount of data.
How we're gonna analyze as well plus fire up a pivot table.
See if we can solve this problem.
Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today's question, Tom and Vicki my friends say, you know "Hey! can we use Excel for solving basic household finance things." and here's a great use for Excel let's say, that you have some sort of a credit card or revolving loan with the department store or something like that and you get the statement and here's the balance 4622.37. Here's your interest rate 13.2 for and the credit card company, you know they want you to pay a bunch of interest.
So, they always suggest the minimum payment that really is going to have you paying for ever.
Let's use Excel's power to figure out just how long you're going to be paying on that.
Now, in the past I've used the PMT function but today, we're going to use the NPER, N P E R number of periods function.
Now, once I get that opening parenthesis there I can press [ ctrl + A ] or hit the [ FX ] button and it's going to walk me right through this.
So, the rate is this cell here B3 divided by 12 because we're talking about monthly periods.
The payment is the 92 here and then the present value.
One of these has to be negative.
So, we're going to 4622.37 and you see that we're going to be paying this loan off for the next 73 months.
So, the total that we're going to pay over the life of the loan is 67 76.
That's a lot of interest, let's just do a quick interest calculation here.
You know almost fifty percent as much of the loan value is...
Now, we're going to be paying in interest over the life of the loan and then of course in Excel, it's easy to come over and say okay well, you know what if we double up payments, but instead of making that minimum.
What if we can do, 184.
So, let's do equal NPER the rate divided by 12.
The Payment is going to be the 92 plus the 92 and then the present value is minus the current balance.
Look at that, we can pay this off in 29 months instead of 74 months.
So, you know a little over 2 years instead of 6 plus years, total payoff 5442.
I'm just saving you know, a massive amount of interest in this case by simply making a little bit more payment, and you can actually now use this to do some, What-If analysis.
You know we can say, hey! You know, I really can't afford another 92 but I can pay another 20.
And you can see that. That's still going to cut more than a year off.
the life of the loan just by making that little bit an extra payment, or you know if you want to go really big type 400 and you have the whole thing paid off in 9 months.
So, really cool here using Excel financial functions.
Not necessarily for Bankers or anything like that just to solve basic household finance problems.
I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
Thanks for stopping by, we'll see you next time for another netcast from MrExcel.
I'm Bill Jelen.
Basically, we start out with massive amount of data.
How we're gonna analyze as well plus fire up a pivot table.
See if we can solve this problem.
Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today's question, Tom and Vicki my friends say, you know "Hey! can we use Excel for solving basic household finance things." and here's a great use for Excel let's say, that you have some sort of a credit card or revolving loan with the department store or something like that and you get the statement and here's the balance 4622.37. Here's your interest rate 13.2 for and the credit card company, you know they want you to pay a bunch of interest.
So, they always suggest the minimum payment that really is going to have you paying for ever.
Let's use Excel's power to figure out just how long you're going to be paying on that.
Now, in the past I've used the PMT function but today, we're going to use the NPER, N P E R number of periods function.
Now, once I get that opening parenthesis there I can press [ ctrl + A ] or hit the [ FX ] button and it's going to walk me right through this.
So, the rate is this cell here B3 divided by 12 because we're talking about monthly periods.
The payment is the 92 here and then the present value.
One of these has to be negative.
So, we're going to 4622.37 and you see that we're going to be paying this loan off for the next 73 months.
So, the total that we're going to pay over the life of the loan is 67 76.
That's a lot of interest, let's just do a quick interest calculation here.
You know almost fifty percent as much of the loan value is...
Now, we're going to be paying in interest over the life of the loan and then of course in Excel, it's easy to come over and say okay well, you know what if we double up payments, but instead of making that minimum.
What if we can do, 184.
So, let's do equal NPER the rate divided by 12.
The Payment is going to be the 92 plus the 92 and then the present value is minus the current balance.
Look at that, we can pay this off in 29 months instead of 74 months.
So, you know a little over 2 years instead of 6 plus years, total payoff 5442.
I'm just saving you know, a massive amount of interest in this case by simply making a little bit more payment, and you can actually now use this to do some, What-If analysis.
You know we can say, hey! You know, I really can't afford another 92 but I can pay another 20.
And you can see that. That's still going to cut more than a year off.
the life of the loan just by making that little bit an extra payment, or you know if you want to go really big type 400 and you have the whole thing paid off in 9 months.
So, really cool here using Excel financial functions.
Not necessarily for Bankers or anything like that just to solve basic household finance problems.
I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
Thanks for stopping by, we'll see you next time for another netcast from MrExcel.
