Presentation Transcript

1. Let’s say we take out a $5,000 loan on our credit card, which charges a 19.99% Annual Percentage Rate for its interest. They also charge us a minimum monthly payment of either 2% of our balance or $25, whichever is larger. A. How long does it take to pay off the loan using the minimum payment option? B. How much interest do we pay over the course of the payments?
2. This is the first slide given to us in our “final notes.” Let’s follow along. First we need to copy our column headings into excel. The end balance in the zeroth month is the original loan amount. The beginning balance is the previous month’s ending balance. Then enter in numbers in the month column starting with zero to indicate which month we are in. The note below reminds you to start with the zeroth month.
3. After typing in “5000” in cell F2, formatting all cells but the month cells to currency, and typing “=F2” in the first month of the Beginning Balance column it should look like this: The next step is to do the payment column. Since part a asks us to do the minimum payment option, let’s do that. The notes say the following : Minimum payment: where you use the “=max(one value, second value)” feature of excel. One value is a fixed dollar amount.
4. So in the payment column in the first month type in =max(25, .02*B3). This says that excel should choose the larger of two values: either $25 or 2% of the beginning balance of that month. This is what the question states as the minimum payment that the credit card company will charge so we’re doing good.
5. Next step is the interest column, which is 19.99% APR or a monthly interest amount of (.1999/12)*the beginning balance in that month. So in the interest column we’ll type in =(.1999/12)*B3. Note the notes say: In the “interest” column we calculate the monthly interest rate times the beginning balance for that month.
6. The notes then say: The “principal” column is the amount of our payment that doesn’t get paid in interest to the bank. It goes toward paying off the balance. So it’s the payment minus the interest payment. =c3-d3 And then the ending balance is the beginning balance minus the principal. =B3-E3 Note above that it says the principal goes toward paying off the balance.
7. Copy and Paste!! Takes 523 months to get to a zero (or subzero) balance. And if you autosum the interest column, the interest paid is $20,151.45. If the question asked you how much you would have to pay each month if you wanted to pay it off in 6 years you would do everything the same except for the payment column. The notes on time limit payment say: Time limit payment: where you use the “=pmt(monthly interest rate, number of months to pay off the loan, the negative of the original loan amount) “ feature in excel. All of these values are fixed amounts. For this you would insert =pmt(.1999/12, 72, -5000) They’re all fixed amounts so no cell references. $119.74 should be the dollar amount. If you don’t get a zero balance in the 72 month something went wrong. That’s the whole point of this payment method.