Chapter Review 1.11-3 Solutions
For all questions use cell references wherever possible:
1. (2 points) Write a formula for cell F2 which can be copied down, to determine the monthly payment on this loan (the car is purchased for $19.500, a down payment is made now of 10% and the rest is financed at 6.9% annual interest compounded monthly.)
=pmt(c2/12,e2,b2-b2*d2,0) or =pmt(c2/12,e2,(1-d2)*b2)
2. (1 point) The GM dealer is willing to negotiate on the loan duration. I told him I could afford $450 per month with no money down. Write a formula to calculate how many years it would take me to pay off this loan at 1.9% annual interest compounded monthly.?
=nper(c5/12,-450,b5,)/12
3. (1 point) The Chrysler dealer told me he would sell me the car for $400 per month with a 5-year payback – and no down payment. What is the resulting annual interest rate I would be paying – assume the loan is compounded monthly?
=12 * rate(5*12,-400,b6,)
4. (2 points) A distant aunt has just left me a bank CD that she purchased ten years ago for $5000. The CD has been accruing interest at the rate of 8% per year compounded quarterly. Write a formula to determine if I have enough money to buy the Honda for cash (true/false).
=b3<=fv(.08/4,10*4,,-5000) or =fv(8%/4,10*4,,-5000)>=b3
5. (1 point) My friend said he put money away five years ago in a guaranteed return fund paying 6% annual interest compounded monthly. Each month since then he has deposited another $200. Now he has $18000 available to buy a car. Write a formula to determine how much he put into the fund five years ago.
=pv(.06/12,5*12,-200,18000)
6. (2 point) Another option the Ford dealer has offered me is to sell the car to me with financing for 3 years at 3.9% annual rate compounded monthly with no down payment, but with a $2000 balloon payment at the end of the loan. Write a formula to calculate the monthly payment of this loan.
=pmt(.039/12,12*3,b4,-2000)
7. (1 point) You decided to put off buying the car and are have put the $9500 into a zero coupon bond that accrues 5% interest each year (compounded annually) at the beginning of each year. These bonds make no payments until they mature in two years. What is the Future Value of this bond after two years.
= FV(.05, 2, 0, -9500, 1)