Life Insurance Needs Analysis Case

Your friend John asks you for advice concerning life insurance. John is 26 years old and graduated from law school last year. He currently earns $43,000 per year. Johns wife, Mary, is a graphics designer who currently earns $51,500 per year. Mary is 28 years old. The couple have three children: Billy, age 2, Cindy, age 4, and Sally, age 6. John and Mary pay $1,075 per month for their home mortgage, which will be paid off in 25 years. The interest rate on their mortgage is 5.7%. (Their current equity in the home is $33,000.) The couple owns two cars, one is 10 years old and in relatively good condition. The other is new, and their car loan payments are $420 per month for the next 44 months. The interest rate on the car loan is 4%. The couples personal property (such as clothes, electronics, furniture, etc.) is valued at $43,000. Their investments include checking, savings, and mutual fund accounts equal to $5,700. John has no life insurance. Mary has a $65,000 whole life insurance policy provided as a fringe benefit from her employer. If John should die, Mary would receive approximately $4,000 per year for each child 18 years of age or younger from Social Security.

John and Mary spend almost all of their take-home pay each month. They are able to save $100 per month that they deposit into a tax-deferred college fund for the children. The college fund has been earning a respectable rate of return of approximately 6.8% per year. The fund balance right now is $7,000. Their other investments earn approximately 5% per year.

John is worried about what may happen to his family is he should die. He is considering the purchase of life insurance and asks your advice. Assuming neither John nor Mary will receive large inheritances, how much life insurance do you think John needs on his life? (Use the needs approach.) Show all calculations and explain your answer. Make any assumptions you believe are reasonable, and make sure your assumptions are clearly stated. Also indicate the type of insurance you would recommend, whole life or term, and explain why.

Please show all calculations, in Excel if possible.

Thanks in advance