I am having a hard time answering the tabs 5 thru 7 on the attached excel spread sheet.

I am new at this and would love to be able to use it as an example going forward. Can you help me complete so I have something reference back to later.

spread sheet attached.

A 1 B C D E F G H FUTURE VALUE OF A SINGLE PAYMENT 2 3 4 5 6 7 You just purchased a $2,000, 3 year certificate of deposit (CD) that pays 4% interest, compounded annually. How much will you have when the CD matures Solve using three approaches 1 Step by Step 4% 8 9 Time 0 1 2 3 -$2,000.00 $80.00 $80.00 $80.00 10 11 12 13 14 2 Use the Formula FV=PV*(1+i)^n 15 16 17 18 19 3 Use Excels FV function $2,249.73 Rate Nper Pmt PV Type 20 21 22 23 24 FV 4% 3 $0.00 -$2,000.00 0 2249.73 25 26 Complete the table below to show how the length of time to maturity and the interest rate will affect the value of the CD 27 28 Begin by typing in the row and column labels as shown below. We could fill in the table by inserting formulas in all the cells, but a better way is to use an Excel data table First set cell C31 equal to Cell F23. Then, select the range C31:G41, then on your tool bar click Data, then What If Analysis, then Data Table, Note that the Row Input Cell is F17 and the Column Input Cell is F18, click OK and the table will be completed. 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 Years F18 2249.73 1 2 3 4 5 6 7 8 9 10 2% Interest Rates F17 4% 6% 8% Note: If your data table did not work, make sure that F23 has only cell references in the formula no typed numbers I FUTURE VALUE OF AN ORDINARY ANNUITY Assume you plan to buy a beach condo 5 years from now,and you estimate that you can save $2,50 per year toward a down payment. You plan to deposit the money in a bank that pays 4% interest, an you wil make the first deposit at the end of this year. How much will you have after 5 years Do this problem two ways, (1) take each amount and calculate its future value in year 5 using the F formula and then sum them; (2) use Excel's FV function. (1) Year 1 2 3 4 5 Total Summing (2) $ $ $ $ $ Deposit 2,500.00 2,500.00 2,500.00 2,500.00 2,500.00 Future Value Year 5 $ 2,924.65 $ 2,812.16 $ 2,704.00 $ 2,600.00 $ 2,500.00 $ 13,540.81 Using FV Function Rate Nper Pmt PV Type FV 4.00% 5 $ (2,500.00) $ $ $13,540.81 at you can save $2,500 at pays 4% interest, and u have after 5 years e in year 5 using the FV n. FV=PV*(1+i)^n FUTURE VALUE OF AN ANNUITY DUE Assume you plan to buy a beach condo 5 years from now,and you estimate that you can save $2 per year toward a down payment. You plan to deposit the money in a bank that pays 4% interest you wil make the first deposit today. How much will you have after 5 years Do this problem two ways, (1) take each amount and calculate its future value in year 5 using th formula and then sum them; (2) use Excel's FV function. (1) Year 1 2 3 4 5 Total Summing (2) $ $ $ $ $ Deposit 2,500.00 2,500.00 2,500.00 2,500.00 2,500.00 Future Value Year 5 $ 3,041.63 $ 2,924.65 $ 2,812.16 $ 2,704.00 $ 2,600.00 $ 14,082.44 Using FV Function Rate Nper Pmt PV Type FV $ 1.00 you estimate that you can save $2,500 ey in a bank that pays 4% interest, and will you have after 5 years its future value in year 5 using the FV el's FV function. A B C D E F G PRESENT VALUE OF AN ORDINARY ANNUITY 1 2 Your uncle just announced he is going to give you $10,000 per year at the end of each of the next 4 years. If the relevant interest rate is 7%,what is the value today of his promise ? 3 4 Do this problem two ways, (1) take each amount and caculate its present value using the PV formula and then sum them; (2) Use Excel's PV function. 5 6 7 8 9 10 11 12 13 14 15 Interest Rate 1) 7.00% Year Gift Present Value 10,000 $9,345.79 PV=FV/(1+i)^n 10,000 $8,734.39 10,000 $8,162.98 10,000 $7,628.95 $33,872.11 1 2 3 4 16 17 18 19 20 21 22 23 24 2) Rate Nper Pmt Fv Type PV 9345.79 A B C D E F G PRESENT VALUE OF AN ANNUITY DUE 1 2 Your uncle just gave you $10,000 and promised to give you and additional $10,000 per year for 3 additional years starting one year from today. If the relevant interest rate is 7%,what is the value today of his promise ? 3 4 Do this problem two ways, (1) take each amount and caculate its present value using the PV formula and then sum them; (2) Use Excel's PV function. 5 6 7 8 9 10 11 12 13 14 15 Interest Rate 1) 7.00% Year Gift 0 1 2 3 16 17 18 19 20 21 22 23 24 2) Rate Nper Pmt Fv Type PV Present Value PAYMENT You just graduated and have 10 years to pay off your student loan of $100,000. The loan has a interest rate of 7% and will be paid off in 10 equal annual installments starting 1 year from toda will be the amount of your payment? Use excels payment function Rate Nper PV FV Type Pmt LOAN AMORTIZATION Complete the following table Principal at beginning of year Year Payment at end of year 1 2 3 4 5 6 7 8 9 10 Totals 0.00 n of $100,000. The loan has an annual ments starting 1 year from today. What ment? Part of Part of payment that payment that is interest is principal 0.00 0.00 A 1 B C D E F Mary has just completed her undergraduate degree from Kennesaw State Universtiy and is alread planning to enter an MBA program 4 year from today. The MBA tuition will be $20,000 per year for 2 years, paid at the beginning of each year. In addition Mary would like to retire 15 years from today and receive a pension of $60,000 every year for 20 years, with the first pension payment paid out 15 years from today. Mary can borrow and lend as much as she likes at a rate of 7% compunded annually. To fund here expenditures. Mary will save money at the end of years 0-3 and at the end of years 6-14 2 3 Caculate the constant annual dollar amount that Mary must save at the end of each of these years to cover all of her expenditures tuition and retirement. 4 5 6 7 8 9 10 11 Tuition of MBA program Annual desired pension payout Annual payment Interest rate $ $ 20,000.00 60,000.00 7% First enter a Guess amount in cell C8, Then make E14:E17 and E6:20-E28 equal to C8. In cell D14 write the equation =B14+C14 copy to D15:D48. In cell F14 write the equation = D14*(1+$C$9)+E14, copy to F15:F48. In cell B15 enter =F14 copy to B16:B48. On tool bar select Data, What If Analysis, Goal Seek Set F48 = 0 by changing C8 click OK . 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 Year 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 Balance at beginning of year before withdrawal 0.00 Withdrawal beginning of year 0.00 0.00 0.00 0.00 (20,000.00) (20,000.00) 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 (60,000.00) (60,000.00) (60,000.00) (60,000.00) (60,000.00) (60,000.00) (60,000.00) (60,000.00) (60,000.00) (60,000.00) (60,000.00) (60,000.00) (60,000.00) (60,000.00) (60,000.00) (60,000.00) (60,000.00) (60,000.00) (60,000.00) (60,000.00) Net balance beginning of year Savings at end of year Account end of year