1. Cash Flow Diagrams and Compounded Interest: John decides to take out a loan for $10,000 for school at an annual interest rate of 10%. He agrees to make four equal payments, but will defer these payments for four years while in school. Starting at the end of the 5th year, he makes four equal annual payments (years 5, 6, 7 and 8). At the end of the 8th year, the loan is paid off.
(a) Create a table that depicts the cash flow diagram for this problem. It should cover years 0 to 8, and should show the balance, interest charges, each payment, how much of that payment went towards interest and how much towards the principal. The balance should be zero at the end of the 8th year. Use trial and error to find the required payment.
(b) Compute the annual payment using one of the built-in Excel functions discussed in class. Show that this gives the same answer as (a).
(c) Compute the present worth and future worth of this investment.
(d) Suppose interest is compounded twice per year instead of annually, but he still makes four annual payments starting at the end of the 5th year. What is the new annual payment?
2. Kate will purchase a house for $500,000 with a 10% down payment and $10,000 in closing costs. The closing costs are included in the loan. A 10-year loan is arranged at 5% interest compounded monthly.
(a) What is the monthly payment?
(b) By choosing to include the closing costs in the loan, how much money was paid in interest on the closing costs over the life of the loan.
(c) What is the effective interest rate for this loan? (Hint: you must take into account the monthly compounding AND the closing costs.)
(d) Suppose closing costs were paid at closing. How does this change the overall cost of the loan? How does this change the effective interest rate?
(NOTE: EVERYTHING SHOULD BE DONE IN EXCEL. OTHERWISE NO POINTS. INCLUDE THE FUNCTION USED AS WELL)
