Project AMP Dr. Antonio Quesada – Director, Project AMP
Worksheet 2: Interest, The Cost of Money
Create this spreadsheet. Save the file as Loans and Repayment.
Information about these formulas
In cell E4 and also in cell E8, the number of months is multiplied times the payment per month.
This gives you the total $ you pay for the loan, principal and interest.
In cell E5 and also in cell E9, the amount of the loan is subtracted from the total $ you pay for the loan.
This gives you the interest you paid.
In cell C5, the Payment function requires 3 arguments--
the interest rate for the period (here, annual rate E2 divided by12 gives the monthly interest rate),
how many periods,
and the amount borrowed. =PMT(E2/12,C4,-C2)
In cell C9, the Number of Periods function requires 3 arguments
the interest rate for the period,
how much you plan to pay each period,
and the amount borrowed.=NPER(E2/12,-C8,C2)
======
Now play “what-if”:
Change the values in cell C4 and E2, and C8 and E2, and record your answers on the next pages.
Use your spreadsheet file: Loans and Repayment
Suppose you have a Credit Card balance of $1,000.00 and you want to pay it all off.
This spreadsheet lets you play with repayment.
The 18% interest rate in E2 is common for consumer credit. (You can change it. of course.)
1) The value in C4 is how many months you want to take to pay off the loan.
Given C4, the function in C5 will calculate the size of your payment.
•Enter 5 different values into C4 (Term in months).
Keep the interest rate 18.0%. Record the results in the table below.
•Now change the interest rate to 15.9%, and repeat with the same Term values in C4
•Use an even lower interest rate, and repeat.
2) On the other hand, suppose you know how much you can conveniently pay. Put that value in C8. The function in C9 will calculate how many months it will take to pay the loan. Try it!
•Enter 5 different values into C8 (your desired payment).
Keep the interest rate 18.0%. Record the results in the table below.
•Now change the interest rate to 15.9%, and repeat with the same payment values.
•Use lower payment values. Find a payment value so low that the term will be longer than 200 years (more than 2400 months)
Hint: if you go too low, you will see #NUM in the cell.
What is happening with this payment value??
3.What have your learned from these explorations? Note your conclusions here.