John, 38, makes $125,000 per year. He has a 35 year old wife, Nancy, and a daughter who just turned 7. Johns share of the familys consumption is 21%, and he pays an average tax rate of 28%. He plans to work another 30 years and expects salary increases equal to inflation, which he expects to be 3% annually. He expects to earn an 8% nominal rate of return on his investments.

In the event of Johns death, Social Security will contribute $20,000 per year to his family while his daughter is 16 or younger (10 years) and $10,000 per year while his daughter is 17 & 18 (2 years). The family will need total income equal to the Familys Share of Earnings (FSE) until the empty nest period begins, at which point the familys annual funding needs will decrease by $12,000. Nancy will require $64 ,000 in annual total income during retirement beginning at age 66. She will receive $20,000 in annual retirement benefits from Social Security and expects to live until age 85 (20 years of retirement). Finally, the family has $75,000 in liquid assets and lump sum funding needs of $182,500. *** assume that Johns daughters college education is already being funded from the FSE and therefore does not need to be explicitly considered in the analysis. *** assume all monetary figures above are quoted in real terms, i.e. todays dollars.Calculate the amount of life insurance coverage John needs according to the Financial Needs Method. Show your work (an Excel file or document detailing your calculator inputs is fine). I am not specifying a readjustment period, and more importantly I am not asking you to assume that the cash flows need to come at the beginning of each period (BGN mode not necessary). This will make the problem much easier to do on your financial calculator or in Excel since you can use the NPV function.For each year, take the familys total income need and subtract the amount that Social Security will contribute. The leftover amount is what needs to be funded by the life insurance policy. Set the amount needed while the daughter is 16 or younger as C01, set F01=10, and so on C02 and F02 for when the daughter is 17 & 18, C03 and F03 for the empty nest period (19 years), and C04 and F04 for the wifes retirement. If you dont have a financial calculator, youll need to plot the familys future annual funding needs in Excel. Finally, calculate the NPV using the real interest rate, add the lump sum funding needed, and subtract the familys liquid assets to calculate the coverage needed.