G J K L M Year 1 2 3 3 4 Premium Due $4,230 $4,570 $4.890 $5,160 $5,300 $5,580 $5,950 $6,180 $6,600 $7,160 5 6 7 8 9 10 A B C D E F H I 1 Mark Gunn's life is changing dramatically. He and his wife Jen 2 recently bought a duplex apartment in Worcester and are epecting their third child. 3 These new responsibilities have prompted Mark to think about some serious issues, 4 including life insurance. 5 6 10 years ago, Mark sold his drums and purchased an insurance policy that provides a death benefit of $400,000. 7 This policy is paid in full and will remain in effect for the rest of Mark's life. 8 Alternately, Mark can cash in the policy and receive an immediate payoff of $60,000 9 from the insurance company. 10 11 10 years ago, $400,000 seemed like it was enough. Now, he's not so sure. 12 Mark is investiagting a different insurance product hat would provide a death benefit 13 of $3,500,000 but he and Jen would need to make ongoing monthly payments. 14 15 The table to the right shows the annual premiums for the new policy 16 over the next 10 years. 17 18 To pay the premiums for the new policy, Mark had an idea, the first good idea 19 of his life. He could cash out the existing policy, take the $60,000 and invest it. 20 Using the after tax proceeds from the investment, he could then pay the premium on the new policy. 21 22 However, to see if this is possible, he wants to understand the minimum rate of return 23 that he would have to earn from this investment to be able to cover the after-tax Premium payments. 24 25 His tax rate is 28%. 26 27 a. Calculate the Investment earnings for each of the next 10 years, if the Annual Return is 15%. 28 The Annual interest is compounded quarterly. 29 Be sure to factor in that Mark would owe taxes on these earnings. 30 The formula for the annual interest earned, if it's compounded quarterly is: 31 = Beginning Balance *(1+Interest Rate / 4)^4 - Beginning Balance. 32 33 Factor in starting balance, investment earnings Pre-tax, tax costs, investment earnings net, premium due, and ending balance. 34 35 b. Using Solver, what's the minimum Annual return that Mark would need to 36 ensure he can cover his premium payments every single year using after-tax earnings from the investment of the 60K? 37 Use GRG Non-Linear. 38 G J K L M Year 1 2 3 3 4 Premium Due $4,230 $4,570 $4.890 $5,160 $5,300 $5,580 $5,950 $6,180 $6,600 $7,160 5 6 7 8 9 10 A B C D E F H I 1 Mark Gunn's life is changing dramatically. He and his wife Jen 2 recently bought a duplex apartment in Worcester and are epecting their third child. 3 These new responsibilities have prompted Mark to think about some serious issues, 4 including life insurance. 5 6 10 years ago, Mark sold his drums and purchased an insurance policy that provides a death benefit of $400,000. 7 This policy is paid in full and will remain in effect for the rest of Mark's life. 8 Alternately, Mark can cash in the policy and receive an immediate payoff of $60,000 9 from the insurance company. 10 11 10 years ago, $400,000 seemed like it was enough. Now, he's not so sure. 12 Mark is investiagting a different insurance product hat would provide a death benefit 13 of $3,500,000 but he and Jen would need to make ongoing monthly payments. 14 15 The table to the right shows the annual premiums for the new policy 16 over the next 10 years. 17 18 To pay the premiums for the new policy, Mark had an idea, the first good idea 19 of his life. He could cash out the existing policy, take the $60,000 and invest it. 20 Using the after tax proceeds from the investment, he could then pay the premium on the new policy. 21 22 However, to see if this is possible, he wants to understand the minimum rate of return 23 that he would have to earn from this investment to be able to cover the after-tax Premium payments. 24 25 His tax rate is 28%. 26 27 a. Calculate the Investment earnings for each of the next 10 years, if the Annual Return is 15%. 28 The Annual interest is compounded quarterly. 29 Be sure to factor in that Mark would owe taxes on these earnings. 30 The formula for the annual interest earned, if it's compounded quarterly is: 31 = Beginning Balance *(1+Interest Rate / 4)^4 - Beginning Balance. 32 33 Factor in starting balance, investment earnings Pre-tax, tax costs, investment earnings net, premium due, and ending balance. 34 35 b. Using Solver, what's the minimum Annual return that Mark would need to 36 ensure he can cover his premium payments every single year using after-tax earnings from the investment of the 60K? 37 Use GRG Non-Linear. 38
