Please find the attached for the questions.

1a) John wanted to know what their expenses would be the year they retired (future value), given 12 years to retirement, $123,000 in household expenses in todays dollar terms, and 3% inflation.

1b) What would their expenses be after 30 years in retirement (future value)?

2) What would be the value of the McMurtreys retirement portfolio in 12 years, given the current $420,000 value, $25,000 in annual savings (no growth in contributions), and a 7% average return on their investments?

Retirement distribution research indicates that a portfolio can distribute 5% of the portfolio per year with payments growing with inflation, and have an 80% chance of not being depleted.For example, a retirement portfolio of $1 million at retirement could support a first year distribution of $50,000.

3a) What first year distribution amount is suggested, then, in the first year of retirement, given the result of question 2 above?

3b) What is the distribution amount determined in 3A relative to their expected spending needs in the first year of retirement (from Question 1a)? Express in percentage terms.

3c) Will the McMurtreys have enough saved to enjoy a successful retirement, given their current plan?

4) The McMurtreys have life insurance that will pay a total of $100,000 upon their death, for the benefit of their children. Assuming they die in 42 years (at age 96), what is the equivalent value of this sum in todays terms assuming 3% inflation?

5) The couple also plans to live in their home until their death at age 96, and was planning on leaving the home to both children, jointly. What amount would each child inherit if the house appreciated by 3.5% per year for 42 years?

6) What advice or suggestions do you have for the McMurtreys in regard to achieving a successful retirement?This can be answered in general terms, without using specific numbers; however, youre welcome to be specific based on working with the spreadsheet model (next).Please specify 5 possible suggestions.

7) Given the base case spreadsheet (see adjacent worksheet) established for the McMurtreys, create a scenario that ends the planning period with a positive value. That is, make reasonable changes to the inputs (in RED) that will make the ending balance in year 2059 greater than zero. To the right of the model, indicate the changes you made and explanation for each. When completed, save the worksheet.

8) Once 7 above is completed, in the adjacent Forecast model worksheet, create a line graph of the annual portfolio balances (column H) by age (column A). Include an appropriate title, and label both axes. Place the chart adjacent to the completed model in that worksheet in the indicated area.

\fqattachments_5618012c09fc3e8338db912da14db379aae94f13.xls Financial Stmts 02/09/2017 McMurtrey Personal Financial Statements Note: gross estimates for 2017 in $000s Income statement Balance sheet Income John salary (employer retirement match (5%)) 190 80 4 Use assets Residence Cars Personal property (gross estimate) Melissa salary (employer retirement match (6%)) 510 55 45 100 Invested Assets--nonretirement Credit Union account 6 Expenses* Taxes (fed, state, SS, property) 610 165 Vanguard brokerage account 110 35 75 42 Invested Assets--retirement* Household Non-discretionary* Discretionary John Work 401k 91 32 Savings 25 John retirement contribution and match 15 Melissa retirement contribution and match 10 420 140 Fidelity IRA Melissa 75 Work 403b Roth IRA 170 35 Debt Income net expenses and current saving * $25k payments per year to mortgage 0 Residence (paid off in 12 years) Credit card Net Worth 280 250 30 860 Life Insurance-whole life: John: $50k face, $35k cash value Melissa: $50k face, $34k cash value Have health, disability, dental coverage, umbrella $2M Have wills with medical and financial POA, with directives McCurtrey Personal Finance Case Questions Note: show interim calculations along with final solution/answer 1a) John wanted to know what their expenses would be the year they retired (future value), given 12 years to retirement, $123,000 in household expenses in today's dollar terms, and 3% inflation. 1b) What would their expenses be after 30 years in retirement (future value)? 2) What would be the value of the McMurtrey's retirement portfolio in 12 years, given the current $420,000 value, $25,000 in annual savings (no growth in contributions), and a 7% average return on their investments? Retirement distribution research indicates that a portfolio can distribute 5% of the portfolio per year with payments growing with inflation, and have an 80% chance of not being depleted. For example, a retirement portfolio of $1 million at retirement could support a first year distribution of $50,000. 3a) What first year distribution amount is suggested, then, in the first year of retirement, given the result of question 2 above? 3b) What is the distribution amount determined in 3A relative to their expected spending needs in the first year of retirement (from Question 1a)? Express in percentage terms. 3c) Will the McMurtreys have enough saved to enjoy a successful retirement, given their current plan? 4) The McMurtreys have life insurance that will pay a total of $100,000 upon their death, for the benefit of their children. Assuming they die in 42 years (at age 96), what is the equivalent value of this sum in today's terms assuming 3% inflation? 5) The couple also plans to live in their home until their death at age 96, and was planning on leaving the home to both children, jointly. What amount would each child inherit if the house appreciated by 3.5% per year for 42 years? 6) What advice or suggestions do you have for the McMurtrey's in regard to achieving a successful retirement? This can be answered in general terms, without using specific numbers; however, you're welcome to be specific based on working with the spreadsheet model (next). Please specify 5 possible suggestions. Item 1 Item 2 Item 3 Item 4 Item 5 extra? 7) Given the \"base case\" spreadsheet (see adjacent worksheet) established for the McMurtreys, create a scenario that ends the planning period with a positive value. That is, make \"reasonable\" changes to the inputs (in RED) that will make the ending balance in year 2059 greater than zero. To the right of the model, indicate the changes you made and explanation for each. When completed, save the worksheet. McMurtrey Cashflow Forecast Model* *home not included in model, all values in thousands of dollars Note: user inputs in red Invest Accounts Household Expenses at retirement Start----> 420 Increases--- Age Year Beg Bal Portfolio end value -3,039 present value--> Input Specify change Explanation for change $420k begin balance (878) 123 3.0% 50 3.0% 25 0.0% 7.0% All spending Social Security Savings Investment Growth Portfolio Balance $123k spending 3% inflation $0 social security 0% annual increase 54 2017 420 25 29 474 55 2018 474 25 33 533 $25k savings 56 2019 533 25 37 595 0% annual increase 57 2020 595 25 42 662 58 2021 662 25 46 733 59 2022 733 25 51 809 60 2023 809 25 57 891 61 2024 891 25 62 978 62 2025 978 25 68 1,072 63 2026 1,072 25 75 1,172 64 2027 1,172 25 82 1,279 65 2028 1,279 25 90 1,393 66 2029 1,393 175 73 98 1,389 67 2030 1,389 181 76 97 1,381 68 2031 1,381 186 78 97 1,369 69 2032 1,369 192 80 96 1,354 70 2033 1,354 197 83 95 1,334 71 2034 1,334 203 85 93 1,309 72 2035 1,309 209 88 92 1,279 73 2036 1,279 216 90 90 1,243 74 2037 1,243 222 93 87 1,201 75 2038 1,201 229 96 84 1,152 76 2039 1,152 236 99 81 1,096 77 2040 1,096 243 102 77 1,031 78 2041 1,031 250 105 72 958 79 2042 958 258 108 67 876 80 2043 876 265 111 61 783 81 2044 783 273 114 55 679 82 2045 679 281 118 48 563 83 2046 563 290 121 39 434 84 2047 434 299 125 30 290 85 2048 290 308 129 20 132 86 2049 132 317 133 9 -43 87 2050 -43 326 137 (3) -236 88 2051 -236 336 141 (16) -448 89 2052 -448 346 145 (31) -680 90 2053 -680 356 149 (48) -935 91 2054 -935 367 154 (65) -1,214 92 2055 -1,214 378 158 (85) -1,519 93 2056 -1,519 390 163 (106) -1,851 94 2057 -1,851 401 168 (130) -2,214 95 96 2058 2059 -2,214 -2,609 413 426 173 178 (155) (183) -2,609 -3,039 7% return on investments 140 PLACE GRAPH BELOW HERE 2,000 1,000 0 -1,000 -2,000 -3,000 -4,000 50 55 60 65 70 75 80 85 90 95 100