Question
Education Savings Your friends, Edwin and Emma Silva, have a dream that their young son will one day attend college, for a four-year degree. For
Education Savings Your friends, Edwin and Emma Silva, have a dream that their young son will one day attend college, for a four-year degree. For the next 12 years, they plan to make monthly deposits to a 529 College Savings plan at a local bank. The familys annual household income is $90,000. They plan to invest a part of their income each month. The special college savings plan pays 4.75% annual interest, compounded monthly. They want to know how much the investment will be worth after 12 years, and what percentage of the 4-year tuition that would be. Create a worksheet for your friends. They have supplied the following information: Annual College Tuition = $50,000; Monthly Deposit (savings) = 8% of monthly income, Annual Interest Rate on the investment = 4.75%. Your worksheet should show at least the Annual Out-of-State Tuition, Monthly Deposit, Annual Interest Rate, Term (i.e. number of years of savings). Assign appropriate names to the cells that have the annual interest rate, the term of the loan, annual tuition, and monthly deposit. Be sure to name the cells before using them in formulas. Calculations: A. Use a formula to determine the four-year tuition (assume the annual tuition stays the same for each of the four years). Name this cell. B. Use a financial function to find the future value (FV) of the investment after the 12 year period. Name this cell. C. Use a formula to determine the percentage of the 4-year college tuition they would have saved after the 12 year period. Name this cell. D. Your friends also want to know what interest rate they would need in order to save 75% of the 4-year tuition after 12 years if all other values remain the same. Use the Goal Seek analysis tool to determine the rate. Do not save the new rate but include a statement at the bottom of your sheet that says what the rate must be in order to save 75% of the tuition. E. Your friends are not quite sure they will be able to save 6% of the monthly household income each month for 12 years. Create a data table that shows them what the value of investment will be for monthly savings ranging from 4% to 12% of the income in increments of 1%, and term ranging from 16 years to 11 years in increments of -1
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started