D E 1 2 3 percent down APR Introduction This project will the use an Excel spreadsheet to show how periodic payments to an account over time will increase the account balance to reach some monetary goal in the future. It will also show how the amount of debt will decrease over time with periodic payments towards the debt.* Developing these payment schedules is important in understanding how interest affects the balance of an accounts over long periods of time.| Part I-A There are many reasons for a savings plan. This part of the project assumes a person hopes to purchase a new truck estimated to be $40,000 in two years. It would be good to have a down payment of 15%; the current savings rate is only about 1%. The following shows how to use these parameters in Excel to find the monthly amount to deposit into a savings account, and then to show an investment schedule of the growing balance. The spreadsheet should be setup like the example at the right. Cells B1, B7 - B9, and C12-E15 are all formatted as truck price $40,000.00 currency, cells B2 and B3 are formatted as percentage, and everything else is general formatting. pmt/yr Enter formulas for the remaining 7 information so the spreadsheet can be used for different parameters. Find the down 7 monthly deposit payment in cell B7 by entering =B1*B2. Find the amount of the loan to 1 saving schedule pay the remaining cost in cell B8 by entering =B1-B7. The monthly deposit is found by dividing the amount down by the future value of one dollar, so the formula in cell B9 is =-B7/FV(B3/B4,B4*B5.1) The formulas for the schedule are: cell B13 enter =1+B12, cell C13 enter =B$9, 11 saving schedule PMT # $ BAL cell D13 enter =E12*B$3/BS4, and cell E13 enter =E12+C13+D13. Select cells 13 $247.61 | $0.00 $247.61 B13-E13, put the cross on the green square at the lower right, right click the mouse and drag the curser to cell E36. 15.00% 1.00% 12 2 . yr 5 5 amount down amount loan 3 0 PMT # $ PMT SINT SBAL 2 3 4 5 $ PMT SINT 12 1 Excel will calculate the total of cells C13 - C36 by entering =sum(C13:C36) in cell C38. The total interest can be similarly calculated in cell D38. Answer the following questions on your spreadsheet. 1. How much money is deposited? 2. What is the total amount of interest is earned? 3. What percent of the amount deposited is the interest earned? (Write answer to four decimal places.) 4. How does the percent in #3 above compare to the APR? Explain
