Kim and John’s dream for their ten year old daughter, Kyleigh, is that one day she will attend their alma mater, Dayton University. For the next 8 years, they plan to make monthly payment deposits to a long term savings account at a local bank. The account pays 4.25% annual interest. Create a worksheet for Kim and John that uses a financial function to show the future value of their investment and a formula to determine the percentage of the college’s tuition saved. Kim and John have supplied the following information:
Rate per year = 4.25%
Payment = $375
Percentage of Tuition saved = FV/Sum of Tuition for four years
Kim and John are not sure how much they will be able to save each month. Create a data table that shows the future value and percentage of tuition saved for monthly payments from $275 to $875, in $50 increments. Insert a graphic file and assign it a hyperlink to a financial institution of your choice. Protect the worksheet.
Save it as Saving_for_College_Your_Name.
Five years later, Kim was laid off from work leaving them with one income. John is a New York City Police Officer making $70,000 a year. But he is up for a raise in two months. Please create a spreadsheet outlining their expenses with the following information:
Mortgage: $2200 a month
Utilities (water, heat, electricity, and gas): $500 a month
Cable TV: $100 a month
Telephone: $ 50 a month
Internet: $40 a month
Food: $600 a month
Car payment: $200 a month
Cell Phones: $45 a month (2)
Car Insurance: $135a month
Kyleigh’s Day Care: $1000 a month
Gas: $500 a month
Misc: $500 a month
Can Kim and John survive with one income and still put money away for Kyleigh’s college fund? If not, what is the minimum that John would have to make a year for them to just break even each month? Please outline increase in John’s salary in $1000 increments to $20,000 more than just breaking even, based off of their expenditures and need to save for their daughter’s education.
Save as Saving_for_College_Part_II_Your_Name.