John, 38, makes $125,000 per year. He has a 35 year old wife, Nancy, and a daughter who just turned 7. John’s share of the family’s 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. (15 pts) In the event of John’s 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 Family’s Share of Earnings (FSE) until the empty nest period begins, at which point the family’s 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 John’s daughter’s 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. today’s 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). This problem is slightly simplified compared to the example in the Powerpoint. 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 family’s 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 wife’s retirement. If you don’t have a financial calculator, you’ll need to plot the family’s future annual funding needs in Excel. Finally, calculate the NPV using the real interest rate, add the lump sum funding needed, and subtract the family’s liquid assets to calculate the coverage needed.

Excel Applications for Accounting Principles
4th Edition
ISBN:9781111581565
Author:Gaylord N. Smith
Publisher:Gaylord N. Smith
Chapter27: Time Value Of Money (compound)
Section: Chapter Questions
Problem 6E
icon
Related questions
Question

John, 38, makes $125,000 per year. He has a 35 year old wife, Nancy, and a daughter who just turned 7. John’s share of the family’s 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.

(15 pts) In the event of John’s 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 Family’s Share of Earnings (FSE) until the empty nest period begins, at which point the family’s 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 John’s daughter’s 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. today’s 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). This problem is slightly simplified compared to the example in the Powerpoint. 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 family’s 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 wife’s retirement. If you don’t have a financial calculator, you’ll need to plot the family’s future annual funding needs in Excel. Finally, calculate the NPV using the real interest rate, add the lump sum funding needed, and subtract the family’s liquid assets to calculate the coverage needed.

Expert Solution
trending now

Trending now

This is a popular solution!

steps

Step by step

Solved in 3 steps

Blurred answer
Knowledge Booster
Tax Planning and Strategies
Learn more about
Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, accounting and related others by exploring similar questions and additional content below.
Similar questions
  • SEE MORE QUESTIONS
Recommended textbooks for you
Excel Applications for Accounting Principles
Excel Applications for Accounting Principles
Accounting
ISBN:
9781111581565
Author:
Gaylord N. Smith
Publisher:
Cengage Learning