Math 1090 Project Future and Present Value with Periodic Payments Introduction This project demonstrations the use of an Excel spreadsheet to show how periodic payments increase for a monetary goal in the future, and decrease a present amount of debt. Setting up these problems and developing the schedules is important to understand what the programs and websites do which are commonly used to deal with these procedures. 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 currency, cells B2 and B3 are formatted as percentage, and everything else is general formatting. Enter formulas for the remaining information so the spreadsheet can be used for different parameters. Find the down payment in cell B7 by entering =B1*B2. Find the amount of the loan to 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, cell D13 enter =E12*B$3/B$4, and cell E13 enter =E12+C13+D13. Select cells B13 -E13, put the cross on the green square at the lower right, right click the mouse and drag the curser to cell E36. 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. Copy and print the spreadsheet, and answer the following questions. 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. Part I-B Change the parameters in Part I-A to saving for a 20% down payment of a $250,000 home in 3 years when a 1.5% APR can be found. Find the amount of the down payment, the amount of the mortgage, and the monthly savings deposit. Although the complete schedule needs to be worked out on a scratch spreadsheet, only payment numbers 1 (row 13), payment 12 (initially in row 24), payment 24 (initially in row 36), and payment 36 (initially in row 48) should be copied and printed as shown below, along with answers to the following questions. Payment numbers 2 - 11 (initially in rows 14 - 23), payment numbers 13 - 23, and payment numbers 25 - 35 should be hidden by selecting the rows, right clicking on the rows, and clicking Hide.] 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. 5. How do the answers in #4 Part I-A and #4 in Part I-B compare? Explain. Part II-A This part of the project uses Excel to find the monthly payments for a loan at 2.25% APR for a 5-year term. The future value spreadsheet for the truck down payment in Part I-A above, can be extended like the example below. Cells G1, G9, and G12 - J15 are all formatted as currency, cell G2 is formatted as percentage, and everything else is general formatting. Enter formulas referencing the information in Part I-A. In cell G1 enter =B8. Find the amount of the monthly payment using the Excel formula in cell G9 by entering the formula = -PMT(G2/G3,G3*G4,G1). Show the beginning balance in cell K12 by entering =G1. In row 13 write these formulas for the schedule. In cell G13 enter =1+G12, in cell H13 enter =G$9, in cell I13 enter =G$2/G$3*K12, in cell J13 enter =H13-I13, and in cell K13 enter =K12-J13. Highlight cells G13 to K13, put the over the green square in the lower right corner, left click the mouse, hold the bottom down, and drag the formulas down until there is zero balance. All sixty payments don't need to be shown, only payments 1, 12, 24, 36, 48 and 60, so the payments between should be hidden like the hidden payment in Part I-B. Copy and print these payments and answer the following questions: 1. 2. 3. 4. What is the total amount of principle paid for the sixty payments? What is the total amount of interest paid for the sixty payments? What is the total amount paid for the sixty payments? The total interest is what percent of the amount of the loan? Part II-B Assume that the home in Part I-A will be financed at 3.75% APR for a 30 year mortgage. Find the monthly payment and create the payment schedule. Show this information like in Part II-A above, for payment numbers 1, 12, 60, 120, 240, 360, and answer the following questions: 1. 2. 3. 4. What is the total amount of principle paid for the 360 payments? What is the total amount of interest paid for the360 payments? What is the total amount paid for the 360 payments? The total interest is what percent of the amount of the loan? 5. How does the total percent of interest paid for the truck loan compare to the total percent of interest paid for the mortgage? Part III Reflect: How has this project helped you learn and understand about future and present value? How do you think the results would change if the parameters \"number of years\" and \"number of payments per year\" at the beginning of the project were changed? How could you use this work to learn more about future value and present value? There are numerous programs online that produce future value and present value schedules like these in the project. Some such as http://dknelsonmathteacher.weebly.com/1030-links.html also show how paying extra principle to the regularly scheduled payments affects the time and interest paid. If you don't have access to Excel on your computer, and you're not going to use the computers at the college that have Excel, you may be able to access a similar program through one of the following options: 1) SLCC All Access https://allaccess.slcc.edu/vpn/index.html 2) Google online spreadsheet 3) Microsoft's free online Excel through "Microsoft OneDrive" When the cells are formatted as Percentage before entering the number, fifteen percent is entered as 15 and is displayed as 15.00%. If the number is entered before formatting as Percentage, enter .15, then format Percentage and it should be displayed as 15.00%