On her 10th birthday, Linda Jones' parents decide to deposit $4,000 in a savings account for their daughter for fund her college expenses. They intend to put an additional $4,000 in the account each year on her birthdays unitl she graduates from high school. Starting on her 18th birthday, each year for the next 4 years they will withdraw $20,000 per year to pay for her college education. Is the $4,000 per year sufficient to cover the anticipated college expenses? If not, what is the amount that they need to deposit each year? ($9,748.97) Interest rate Annual Deposit Annual Cost of College Starting Balance 8.00% 6,227.78 20,000.00 $ In Bank on Birthday Before Deposit/Withdrawal Total at Beginning of Year Total at End of Year with Interest Deposit or Withdrawal at Birthda ginning of Yean 6,227.78 6,227.78 6,227.78 $ 12,953.77 $13,990.08-FV($B$13,1,,019,1) ALT R C 6,227.78 20,217.85 $21,835.28 -FV(SB$13,1,, D20,1) WHEN FORMULAS CHANGE IN 6,227.78 $ 28,063.06 $30,308.10-FV($B$13,1,,-D21,1) ASEQUENTIAL ORDER OF COLUMNS 6,227.78 $ 36,535.88 $39,458.75-FV($B$13,1,,-D224) OR ROWS, IT IS BENEFICIAL TO MAKE 6,227.78 45,686.52 $49,341.45FV(SB$13,1,-D23,1 A NOTE OF WHY THAT IS HAPPENING. 6,227.78 $55,569.22 $60,014.76-FV(B$13,1,, D24,1) 6,227.78 66,242.54 $71,541.94FV(SB$13,1,,-D25,1) 10 $6,726.00FV(SB$13,,-D18,1) QUICK KEYS FOR COMMENTS 12 13 14 15 16 17 18 19 20 21 $6,726.00 $ $13,990.08 $ $21,835.28 $ $30,308.10 $ $39,458.75 $ $49,341.45 $ $60,014.76 $ $71,541.94 $ $55,665.29 $ $38,518.52 $ $20,000.00 $ (20,000.00) $ (20,000.00) $ (20,000.00) $ (20,000.00) $ 51,541.94 35,665.29 18,518.52 $55,665.29 $38,518.52 $20,000.00 $0.00 -FV($B$13,1,,026,1) -FV($B$13,1,,027,1) -FV($B$13,1,,028,1) FV(SB$13,1,,-D29,1) NPV of Cash Flow $0.00 The future payouts are fully funded when the NPV of all the cash flows is ZERO UST EQUAL ZERO TO BE FULLY FUNDED USING EXCELS PV AND PMT FUCTIONS Interest Rate Linda's Age Today Age at Start of College Years of College Annual Cost of College $ PV of College at 18S71.541. 8.00% 10 18 4 20,000.00 VIB36.B39 PV1 PV2 PV3 FUNDING FUNDING 2FUNDING3 Anuradha Dixit just turned 50. Anuradha is planning to retire in 15 years, and she currently has $500,000 in her pension fund. Based on the longevity pattern of her family she assumes that she will live 20 years past her retirement age; during each of these years, she desires to withdraw $120,000 from her pension fund. If the interest rate is 5% annually: a. how much will Anuradha have to save Assume that the first deposit to her pension fund will be today, followed by forteen more annual deposits, and that the annual withdrawals from age 65 will occur at the beginning of each year Solve the problem 1) by constructing a table similar to the "FUNDING2" tab from Lecture1 and using either goal seek or solver, either goal seek or solver, 2) by directly using PMT function b. Calculate the required annual saving if the interest rate ranges from 0% to 20% with 1% increments. c. Draw a graph that shows the required saving with respect to these values of interest rate Make sure you build a model that looks good in addition to being correct. For the graph, use prope aband title annually for the next 15 years? On her 10th birthday, Linda Jones' parents decide to deposit $4,000 in a savings account for their daughter for fund her college expenses. They intend to put an additional $4,000 in the account each year on her birthdays unitl she graduates from high school. Starting on her 18th birthday, each year for the next 4 years they will withdraw $20,000 per year to pay for her college education. Is the $4,000 per year sufficient to cover the anticipated college expenses? If not, what is the amount that they need to deposit each year? ($9,748.97) Interest rate Annual Deposit Annual Cost of College Starting Balance 8.00% 6,227.78 20,000.00 $ In Bank on Birthday Before Deposit/Withdrawal Total at Beginning of Year Total at End of Year with Interest Deposit or Withdrawal at Birthda ginning of Yean 6,227.78 6,227.78 6,227.78 $ 12,953.77 $13,990.08-FV($B$13,1,,019,1) ALT R C 6,227.78 20,217.85 $21,835.28 -FV(SB$13,1,, D20,1) WHEN FORMULAS CHANGE IN 6,227.78 $ 28,063.06 $30,308.10-FV($B$13,1,,-D21,1) ASEQUENTIAL ORDER OF COLUMNS 6,227.78 $ 36,535.88 $39,458.75-FV($B$13,1,,-D224) OR ROWS, IT IS BENEFICIAL TO MAKE 6,227.78 45,686.52 $49,341.45FV(SB$13,1,-D23,1 A NOTE OF WHY THAT IS HAPPENING. 6,227.78 $55,569.22 $60,014.76-FV(B$13,1,, D24,1) 6,227.78 66,242.54 $71,541.94FV(SB$13,1,,-D25,1) 10 $6,726.00FV(SB$13,,-D18,1) QUICK KEYS FOR COMMENTS 12 13 14 15 16 17 18 19 20 21 $6,726.00 $ $13,990.08 $ $21,835.28 $ $30,308.10 $ $39,458.75 $ $49,341.45 $ $60,014.76 $ $71,541.94 $ $55,665.29 $ $38,518.52 $ $20,000.00 $ (20,000.00) $ (20,000.00) $ (20,000.00) $ (20,000.00) $ 51,541.94 35,665.29 18,518.52 $55,665.29 $38,518.52 $20,000.00 $0.00 -FV($B$13,1,,026,1) -FV($B$13,1,,027,1) -FV($B$13,1,,028,1) FV(SB$13,1,,-D29,1) NPV of Cash Flow $0.00 The future payouts are fully funded when the NPV of all the cash flows is ZERO UST EQUAL ZERO TO BE FULLY FUNDED USING EXCELS PV AND PMT FUCTIONS Interest Rate Linda's Age Today Age at Start of College Years of College Annual Cost of College $ PV of College at 18S71.541. 8.00% 10 18 4 20,000.00 VIB36.B39 PV1 PV2 PV3 FUNDING FUNDING 2FUNDING3 Anuradha Dixit just turned 50. Anuradha is planning to retire in 15 years, and she currently has $500,000 in her pension fund. Based on the longevity pattern of her family she assumes that she will live 20 years past her retirement age; during each of these years, she desires to withdraw $120,000 from her pension fund. If the interest rate is 5% annually: a. how much will Anuradha have to save Assume that the first deposit to her pension fund will be today, followed by forteen more annual deposits, and that the annual withdrawals from age 65 will occur at the beginning of each year Solve the problem 1) by constructing a table similar to the "FUNDING2" tab from Lecture1 and using either goal seek or solver, either goal seek or solver, 2) by directly using PMT function b. Calculate the required annual saving if the interest rate ranges from 0% to 20% with 1% increments. c. Draw a graph that shows the required saving with respect to these values of interest rate Make sure you build a model that looks good in addition to being correct. For the graph, use prope aband title annually for the next 15 years