D E APR 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, C and C12-E15 are all formatted as 1 truck price $40,000.00 currency, cells B2 and B3 are formatted as 2 percent down percentage, and everything else is general formatting. pmt/yr Enter formulas for the remaining information so the spreadsheet can be used amount loan for different parameters. Find the down 9 monthly deposit payment in cell B7 by entering =B1*B2. Find the amount of the loan to 11 saving schedule SPMT pay the remaining cost in cell B8 by entering =B1-B7. The monthly deposit is 13 found by dividing the amount down by the 14 future value of one dollar, so the formula in cell B9 is =-B7/FV(B3/B4,B4*B5,1) 3 4 5 15.00% 1.00% 12 2 6 amount down 7 8 10 PMT # SINT SBAL 12 15 SPMT $ INT S BAL 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. 11 saving schedule PMT # 12 13 1 $247.61 $0.00 $247.61 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. 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 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) need to be shown. To hide the other payments 2-11 (initially in rows 14 - 23), payments 13 - 23, and payments 25 - 35 right clicking on the rows, and clicking Hide. D E E 12 A B 1 house price $ 250,000.00 2 percent down 20.00% 3 APR 1.50% 4 pmt/yr 5 yr 3 6 7 amount down $XX,XXX.XX 8 amount loan $XXX,XXX.XX 9 monthly deposit $X.XXX.XX 10 11 saving schedule PMT # 12 13 1 24 12 36 24 48 36 $ PMT SINT $ BAL 1 $X,XXX.XX $ $X,XXX.XX $X,XXX.XX $X,XXX.xx $XX,XXX.XX $X,XXX.xx $X,XXX.XX $XX,XXX.XX $X,XXX.XX $X,XXX.XX $XX,XXX.XX 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. 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 H12 - K15 are all formatted as currency, cell G2 is formatted as percentage, and everything else is general formatting. H G $ XX, XXX.XX 2.25% 12 5 C D 1 truck price $40,000.00 loan amount 2 percent down 15.00% APR 3 APR 1.00% pmt/yr 4 pmt/yr 12 YT 5 yr 2 6 7 amount down $ X.XXX.XX 8 amount loan $ XX.XXX.XX 9 monthly deposit $ XXX.XX monthly payment 10 11 saving schedule PMT # SPMT SINT SBAL payment schedule 12 13 1 $ XXX.XXS $ XXX.XX 14 2 $ XXX.XX $ X.XXS XXX.XX 15 3 s XXX.XXS Xxx S XXX.XX PMT SPMT SINT PDS PRINC PD SBAL $ XX.XXX.XX loan amount APR pmt/y yr $ XX.XXX.XX 2.25% 12 5 monthly payment $ X.XXX.XX payment schedule PMT # 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 113 enter =G$2/G$3*K12, in cell J13 enter =H13-113, 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. SPMT SINT PD SPRINC PD SBAL S XX.XXX.XX $ XXX.XX $ XX.XX $ XXX.XX $ X.XXX.XX $ XXX.XX $ XX.XX $ XXX.XXS X.XXX.XX $ XXX.XX $ XX.XXS XXXxx S X.XXX.XX $ XXX.XXS XX.XXS XXX.XXS X.XXX.XX S XXX.XXS XX.XX $ XXX.XXS X.XXX.xx $ XXX.XX $ XX.XX $ XXX.XX $0.00 24 36 48 60 Answer the following questions on your spreadsheet. 1. What is the total amount of principle paid for the sixty payments? 2. What is the total amount of interest paid for the sixty payments? 3. What is the total amount paid for the sixty payments? 4. The total interest is what percent of the amount of the loan? Part II-B Assume that the home in Part I-B 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. What is the total amount of principle paid for the 360 payments? 2. What is the total amount of interest paid for the 360 payments? 3. What is the total amount paid for the 360 payments? 4. 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? 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? |