Question: Annuities and the time value of money: Kristen and her husband Adam just got married and want to buy their first home in 5 years. They want to figure out how much of their combined income they need to save so they can have $100,000 for a down payment on the house. Right now, Kristen makes $75,000 and expects her income to grow by 2% per year starting next year and Adam makes $70,000 and expects his income to grow by 3% per year starting next year.
a. If Kristen and Adam each save 10% of their income (assume each year's deposit into the savings account is at the end of each year), how much will they have in total after 5 years? Assume that money in their bank account grows at 4% per year.
b. Use the data solver in Excel to determine how much as a percent of income both Kristen and Adam must save to reach their goal of $100,000 exactly after 5 years. Assume both of them save the same percentage. Create a new cell with the assumed savings percentage for this problem (you can type in any number as a starting point). Make sure all of the cell references are correct and not referring to the work in la. In the "Data" tab, Click on the "What If Analysis" icon and choose "Goal Seek". Then "Set Cell" should be a cell with the total amount saved at the end of the 5 years, "To Value" should be "100000" and "By Changing Cell" should be the cell with the savings percentage.)
c. The real estate agent has told them that their monthly mortgage payment must be less than or equal to 28% of their combined gross monthly salary. They are considering a 30-year amortizing mortgage with an interest rate of 4.25% compounded monthly. Using the projected salary of both Kristen and Adam at the end of 5 years, how much mortgage could they afford to obtain? Be sure to do all of the calculations on a monthly basis.