Case A - You want to buy a new $30,000 car. You will make a $2,000 down payment and finance the rest by making payments at the END of each month for 5 years. You will be charged 6.0% interest compounded MONTHLY. Prepare a spreadsheet for a 5 year loan with monthly payments. The spreadsheet should include columns for the (1) month, (2) beginning balance each month, (3) the amount of the payment, (4) the amount of principal in the payment, (5) the amount of interest in the payment, and (6) the ending balance for each month of the loan. Format each of the amounts with two decimals. Please include totals at the bottom of your spreadsheet for the amount of payments, the amount of interest, and the amount of principal which were paid during the term of the loan. Case B - You have been to several banks to arrange financing for your car in Case A. The banks have offered a variety of terms and you want to analyze which loan might best fit your budget. Prepare another spreadsheet to include the following: 1. A column for each of the following possible interest rates: A. 3.0% B 4.0% C. 4.5% D. 5.0% 2. A column for (1) the amount of each monthly payment assuming the above annual interest rates and a 5 year loan, (2) the total amount of all 60 of your payments, (3) the total amount of all 60 amounts of interest, and (4) the total amount of principal you will pay over the life of the 5 vear loan. 3. The amount of each monthly payment assuming the above annual interest rates and 4 year loan. 4. A column for (1) the amount of each monthly payment assuming the above annual interest rates and a 4 year loan, (2) the total amount of all 48 of your payments, (3) the total amount of all 48 amounts of interest, and (4) the total amount of principal you will pay over the life of the 4 year loan. Case C-William is 24 years old now. William wants to retire at age 50 and receive $80,000 per year at the end of each year for 40 years (at the end of his 50th through 89th year hoping that he will live to age 90). William plans to invest money into a retirement annuity at the end of each year from age 24 to age 49. William wants to leave $200,000 to his children when he dies. The annuity earns 8% interest compounded annually. Prepare a spreadsheet for this fund from age 24 to age 90 showing the following each year: 1. William's age each year 2. The beginning balance each year 3. The amount deposited (ages 24 - 49) or withdrawn (ages 50 - 89) each year 4. The amount of interest earned each year 5. The ending balance each year (which should be $200,000 when William becomes 90)
