Hello,

Looking for someone to complete the entire 8 tabs on the excel sheet attached. It is for a certificate of certified financial planning from an online Boston University. The entire 8 tabs, as well as written information would need to be completed.

Thank you

John and Mary Kemp You are a financial planner and you have met with new clients John and Mary Kemp several times to gather information for the purpose of developing a comprehensive financial plan. John Kemp is 68 years old, and his wife, Mary, is 68. They have two children, Steven, age 38, and Robert, age 36. They also have three grandchildren. Steven has two sons, Michael, age 15, and James, age 10. Robert has a daughter Sara, age 11. Robert's wife, Sara's mother, died in an automobile accident approximately 2 years ago. All of the Kemp family members are in excellent health. John had his own business in marketing and was extremely successful. He sold his business 3 weeks ago to his son Robert for $10,000,000 using a self-canceling installment note (SCIN). The terms of the agreement are: 20% down payment, 10-year, 4.5% note that will start paying quarterly installment payments on May 1, 2016. The Kemp's live in a home that they had built in 1983. The home is located in River Edge, NJ and is titled in both spouse's names. John inherited a summer home in Burlington, Vermont. His dad had the home built in 1972 for $35,000. He received the home in 1983 when his dad died. The property was worth $50,000 at his father's death. Since this time, John has made $130,000 of improvements. John owned a marketing company with his 50% partner Jerry Nolan for the majority of his career. Mary was a bookkeeper at a local CPA firm for the past 20 years. Both spouses retired when John sold his business and will begin collecting Social Security benefits. The monthly Social Security benefits are $1,200 for John and $750 for Mary. They both had a qualified 401(k) retirement account at their previous jobs. Because John has been so successful with his business, starting in 2011, John make taxable gifts totaling $1.5 million to family members. In 2014, John established two irrevocable trusts. He transferred $2,000,000 of mutual fund shares from his mutual fund portfolio into one trust to benefit both sons. Mary did not elect to split the gifts made to this trust. John transferred ownership of his universal life insurance policy to an ILIT, and transferred $2,275,000 of interest-bearing bonds to the trust to pay for the premiums each year. Mary can receive income from the trust, if needed. The trust will terminate upon Mary's death and each son will receive one-half of the trust assets. For the tax year 2011 For the tax year 2014 Total Gift Taxes Paid Gift Tax Paid $175,000 $630,800 $805,800 Taxable Gift $1,500,000 $6,000,000 $7,500,000 Paid 4/15/12 Paid 4/15/15 The couple has just returned from a two week vacation in Europe. The current credit card balance on Mary's card from this trip and will be paid in full when the bill is received. John used to collect stamps as a child. He decided to go to the latest stamp auction in NYC and won a bid on a 1918, mint, 24 cent, "inverted Jenny" stamp for $725,000. He believes this is a great investment that will appreciate approximately 8% per year. Scope of the engagement: The Kemps have asked you to develop a comprehensive financial plan that will help them meet their goals and help them improve their financial position. The Kemps want you to prepare a statement of net worth and review their various insurance policies for proper coverage. They expressed an interest in acquiring long-term care insurance and they want you to recommend the type of policy they should purchase. They also want you to review their investment portfolio and recommend an asset allocation strategy that maximizes investment performance, is diversified, and matches their risk tolerance level. The couple is very concerned about how the additional income from the installment agreement will be taxed. They would like you invest the 20% down payment and future installment payments. Their only liquidity need is $40,000 for a new car (this is net of any trade-in). Their current marginal tax bracket is at the highest federal rate. In addition, the couple has asked you to review and recommend estate and gift tax minimization strategies. Lastly, the Kemps are not concerned about saving for retirement. However, they would like you to evaluate how they can grow the 401(k) tax-free and distribute the maximum amount left in the account to their two children upon death. Goals: John and Mary have worked with you to prioritize their goals in the following order. 1) Reduce any estate and gift tax liability and insure all estate planning documents are in order. 2) Review insurance contracts and discuss the implications of purchasing a long-term care policy. 3) Establish a college funding plan for the three grandchildren. 4) Reduce personal income taxes. 5) Based on their risk tolerance level, recommend a diversified portfolio that meets the Kemp's investment needs and expectations. Risk tolerance: The Kemps are very willing to take investment risks. Although the Kemps invest in the stock market using both mutual funds and individual stocks, they do not want you to invest in tobacco stocks as both of their parents died from lung cancer. They have asked you to reconcile the portfolio with their ability and willingness to accept risk, and develop an investment strategy and asset allocation that will achieve their investment goals. Current and Future Economic Information: Risk-free Rate 2.75% Market Risk Premium 7.00% Market Standard Deviation 10.25% Inflation Rate 3.00% Note - Interest rates and inflation look to increase in both the short-term and long-term forecasts. Assets: Cash & Checking Account - Joint Account Savings Account - Joint Account Certificate of Deposits - John FMV 5,000 2,475,000 180,000 Policy #1 was transferred to an ILIT in 2014 Cash Value of Universal Life Policy #1 - John Policy #1 - Interpolated terminal cash reserve value 1,400,000 1,725,000 Cash Value of Universal Life Policy #2 - Mary Policy #2 - Interpolated terminal cash reserve value Individual Stock Portfolio Mutual Fund Portfolio Installment Receivable 401(k) Account - John 401(k) Account - Mary Annuity Primary Residence - FMV Land Primary Residence - FMV Building Cost Basis of Primary Residence Vacation Home - FMV Land Vacation Home - FMV Building Cost Basis of Vacation Home Auto - Chevy Automobile Auto - Chevy SUV Boat - 2015 Sea Ray 24" Boat & Trailer Furniture & Personal Property 1918 Inverted Jenny Stamp Monthly Mortgage on Primary Residence Original Mortgage Amount Interest Rate on Primary Residence Mortgage Monthly Mortgage on Vacation Home Original Mortgage Amount Interest Rate on Vacation Home Mortgage Current Credit Card Debt 155,000 175,000 940,000 2,907,682 1,054,540 105,000 72,000 125,000 775,000 370,000 200,000 550,000 20,000 35,000 55,000 150,000 725,000 0.00% 626.96 95,000 5.00% 14,708 (Details Provided in Attached Schedule) (Details Provided in Attached Schedule) (10-year note - see terms found above) (Details Provided in Investment Portfolios Tab) (Details Provided in Investment Portfolios Tab) (Details Provided in Attached Policy Information) (Owned as Joint Tenants) (Owned as Joint Tenants) (Owned as Joint Tenants) (Owned by John) (Owned by John) (Owned by John) (Owned by Mary) (Owned by John) (Owned by John) (Owned as Joint Tenants) (Owned by John) (20-yr mortgage with 207 payments completed, 33 payments remaining as of balance sheet date) (Owned by Mary) Cost of administration, burial expense and state death taxes for each spouse upon death: Administrative expense $150,000 Burial Expenses $25,000 Wills: John's estate will pass to his wife Mary, as primary beneficiary of his will. Mary has executed a will that leaves her estate to John, as primary beneficiary. The second beneficiaries for both spouse's estates are the children Steven and Robert, per stripes, in equal proportions. Homeowners Policy - Primary Residence Type of Policy Face Amount Premium Deductible Liability Medical Payments Anniversary Date Endorsements HO-3 $400,000 $1,825 $500 $250,000 $50,000 Per Person Per Occurrence July 15th None Homeowners Policy - Vacation Home Type of Policy Face Amount Premium Deductible Liability Medical Payments Anniversary Date Endorsements HO-3 $460,000 $3,000 $500 $175,000 $50,000 Per Person Per Occurrence July 15th None Automobile Policy Premium Bodily Injury & Property Damage Comprehensive Deductible Collision Deductible Anniversary Date Umbrella Policy Premium Coverage Anniversary Date Health Insurance Policy Premium Coverage $3,300 $200,000 / $600,000 / $15,000 $350 $500 July 15th $325 $1,000,000 July 15th $2,400 Total yearly premium for John & Mary Medicare Part B Policy #1 was transferred to an ILIT in 2014 Insured Owner Beneficiary Contingent Beneficiary Face Amount Type of Policy Anniversary Date Total Contributions Annual Premium John Kemp John Kemp Mary Kemp Steven and Robert Kemp - 50% each $4,000,000 Universal Life June 09, 1985 $1,595,000 $55,000 Life Insurance - Policy #2 Insured Owner Beneficiary Contingent Beneficiary Face Amount Type of Policy Anniversary Date Total Contributions Annual Premium Mary Kemp Mary Kemp John Kemp Steven and Robert Kemp - 50% each $400,000 Universal Life June 12, 1985 $145,000 $5,000 Annuity Policy Statement of Financial Position John and Mary Kemp Balance Sheet / Statement of Financial Worth As of December 31, 2016 Assets(1) Cash and Equivalents JT Cash & Checking JT Savings Account H Certificate of Deposits H Cash Value of Universal Life Insurance #1 W Cash Value of Universal Life Insurance #2 Total Cash and Equivalents Invested Assets JT Stock Portfolio H Mutual Fund Portfolio H Installment Receivable H 401(k) Account - John (3) W 401(k) Account - Mary (3) H Annuity Policy Total Invested Assets JT H H W JT H H Liabilities & Net Worth(2) $ $ $ $ 5,000 2,475,000 180,000 2,660,000 940,000 2,907,682 1,054,540 105,000 72,000 5,079,222 Personal-Use Assets Primary Residence (4) Vacation Home (5) Auto - 2009 Chevy SUV Auto - 2008 Chevy Automobile Furniture & Personal Property 2009 Sea Ray 14" Boat 1918 Inverted Jenny Stamp Total Use Assets $ 900,000 750,000 35,000 20,000 150,000 55,000 725,000 2,635,000 Total Assets $ 10,374,222 $ Liabilities (Current) Credit Card Debt Current Portion - Primary Residence Current Portion - Vacation Home Total Current Liabilities $ 14,708 14,708 $ - $ - Total Liabilities $ 14,708 Net Worth $ 10,359,515 Total Liabilities & Net Worth $ 10,374,222 Liabilities (Long-Term) Long-Term Portion - Primary Residence Long-Term Portion - Vacation Home Total Liabilities Long-Term Notes to Financial Statements: (1) = All assets are stated at fair market value (2) = Liabilities are stated at principal only (3) = Primary Beneficiary Designation is spouse. Contingent beneficiary is children in equal percentages (4) = FMV of Land is $125,000. Cost basis is $370,000 (5) = FMV of Land is $200,000. 2) Calculate the current ratio as of 12/31/16 and explain any strengths or weaknesses of this ratio: Current Assets/ $ John and Mary Kemp Statement of Cash Flow For the Period January 1 - December 31, 2016 Cash Inflows Social Security Benefits - John Social Security Benefits - Mary Installment Receivable - John Investment Income - Interest, Dividends and Capital Gains Total Income Cash Outflows Mortgage Payments (Principal & Interest) - Residence Mortgage Payments (Principal & Interest) - Vacation Home Real Estate Taxes - Residence Real Estate Taxes - Vacation Home Food Utilities, Cable, Etc. Auto Excise Cell Phone Home Maintenance (Lawn, Snow, Etc.) & Repairs Charity (Cash) Out of Pocket Medical Personal Care Clothing and Cleaning Homeowners Insurance Premiums - Primary Residence Homeowners Insurance Premiums - Vacation Home Auto Insurance Premiums Umbrella Policy Premiums Premiums on Universal Life Insurance Policy - John Premiums on Universal Life Insurance Policy - Mary Vacation Gas, Tolls, Auto Repairs Entertainment, Dining Out, Fun Money John and Mary Kemp Insurance Analysis If the Winooski River floods causing $100,000 of damage to the vacation home, what amount will the homeowners policy cover? 1) Homeowners Benefit Calculation: Assume that John want to annuitize the annuity and is told that he can receive a straight life annuity for $350 a month for life. If the actuarial number of payments is 300, how much of the first $350 amount is taxable and how much is the return of basis? 2) Tax consequences of receiving the 1st annuity payment of $350: Payment Received Less: Return of Basis Taxable Amount $350.00 $350.00 3) The tax consequences of gifting the annuity: Because the monthly straight amount is so low, John would like to know the tax consequences and filing requirements if he gifts this to his granddaughter Sara. Assume that Mary will elect to gift spilt. John and Mary want you to explain any generation skipping tax liability or tax consequences to this gift to Sara. For generation skipping transfer tax purposes, what is the taxable amount of the gift? Please note, do not assume this is being gifted when calculating the gross estate. Total Total FMV at Time of Gift Less: Gift Tax Exclusion - John Less: Gift Tax Exclusion - Mary Net Taxable gift John $0 $0 $0 $0 Gift Tax Mary $0 $0 $0 $0 $4,240 Gift Tax Liability $0 Note - No GST is due. John's unified credit remaining in 2016 $72,000 What tax form is required and when is it due? You recommend a long-term care policy for both John and Mary. The policy is a comprehensive policy that allows each spouse to share the other spouse's policy benefits. The benefits will be paid for 3 years or up to $360,000, whichever come first. The annual premiums are $4,750 for John and $3,000 for Mary. Ignoring any AGI limitation, what amount of the premium is allowed as a medical deduction if the couple itemizes their income taxes in 2016? 4) Allowable Long-Term Premium: Total Premium Less: Allowed Amount Disallowable Premium Amount John Mary $0 $0 Total $0 0 $0 $0 0 $0 John and Mary Kemp Supplemental Investment Information As of December 31, 2016 Non-Qualified Accounts Stock Portfolio (a) Date Shares Cost FMV as of Stock Acquired owned Basis Today A 03/15/01 3,500 $225,000 $7,000 1.35 B 04/26/05 7,500 $125,000 $183,000 0.95 C 07/26/06 1,000 $110,000 $195,000 1.65 D 08/15/11 1,000 $500,000 $555,000 1.03 $960,000 $940,000 Totals Beta (a) = All dividends and capital gain distributions are received in cash. Mutual Fund Portfolio Mutual Fund Balanced Growth Bond International Totals Coefficient of Determination 0.98 0.93 0.95 0.94 Expected Return 8.90% 13.55% 9.58% 15.73% Beta 0.82 1.44 0.91 1.73 Cost Basis $750,000 $150,000 $350,000 $750,000 $2,000,000 FMV as of Today $1,054,540 $331,314 $421,820 $1,100,008 $2,907,682 Qualified Accounts John's 401(k) Account: Mutual Coefficient of Fund Determination S&P 500 Index 1.00 Totals Expected Return 9.65% Beta 1 Cost Basis $605,000 $605,000 FMV as of Today $1,054,540 $1,054,540 Expected Return 8.90% Beta 0.82 Cost Basis $50,000 $50,000 FMV as of Today $105,000 $105,000 Beneficiary is John's estate. Mary's 401(k) Account: Mutual Fund 2015 Fund Totals Coefficient of Determination 0.98 Beneficiary is Mary's estate. John and Mary Kemp Tax Analysis John would like to know how the down payment and 1st installment for the sale of his business will be treated for tax purposes. The business was sold 3 weeks ago to his son for $10,000,000. The terms of the agreement are: 20% down payment, 10-year, 4.5% note that will start paying quarterly on May 1, 2016. The tax basis is $4,245,000. 1) Calculate the tax treatment realized on the down payment: Down Payment Less: Return of Basis Realized & Recognized Long-Term Capital Gain $0 $0 2) Calculate the tax treatment realized on the 1st installment payment (round to the nearest dollar): Installment Payment Less: Interest Income Net Less: Return of Basis Realized & Recognized Long-Term Capital Gain $0 $0 $0 3) Provide two income tax recommendations for the John and Mary to use to avoid and/or reduce tax? 4) Assume that John sells the 1918 inverted Jenny stamp in 10 years for $1,550,000 and there is a 15% commission and they are in the highest marginal tax bracket. What is the federal tax liability on this sale? 1918 Inverted Jenny Stamp Less: 15% Commission Adjusted Sales Price Less: Cost Basis Realized & Recognized Gain Tax Rate Federal Tax Liability $1,550,000 ($232,500) $1,317,500 $1,317,500 0.00% 0 John and Mary Kemp Retirement Analysis Assume John cashed in his 401(k) when the FMV was $1,054,540. He receives a check and deposits the proceeds a few days later into his IRA account. What will the value be after 3 years if the account grows at a 3% guaranteed CD rate, ignoring any required minimum distributions that may apply? 1) Future value of John's IRA account: 2) How can John and Mary grow the retirement assets tax-free? 3) If the ultimate goal is to provide any amounts remaining in the retirement assets to his children, what would you recommend if the children are in the 25% federal bracket (assume that John and Mary are in the 39.6% federal bracket)? Given the following assumptions, which choice would you recommend? Ignore state taxes for this illustration. Choice 1 - Leave Assets Alone Amount at death not subject to required minimum distributions: Assume John lives 20 years Assume a 6% rate for 20 years PV today (assume yearly compounding) $200,000 20 6 $0 Child cashes in retirement asset Less: Federal Tax Liability Net After-Tax Cash $200,000 $0 $200,000 Choice 2 - Do a Roth Conversion PV Today - Before Roth Conversion Less: Federal Tax Liability Net After-Tax Investment in Roth Conversion PV Today - After Roth Conversion Assume John lives 20 years Assume a 6% rate for 20 years FV today (assume yearly compounding) $0.00 $0 $0 $0 20 6 $0 Choose the option you would recommend by deleting the other two choices. (a) - Choice #1 (b) - Choice #2 (c) - Does not matter as both choices are worth the same. Statement of Financial Position John and Mary Kemp Balance Sheet / Statement of Financial Worth As of December 31, 2016 Assets(1) Cash and Equivalents JT Cash & Checking JT Savings Account H Certificate of Deposits H Cash Value of Universal Life Insurance #1 W Cash Value of Universal Life Insurance #2 Total Cash and Equivalents Invested Assets JT Stock Portfolio H Mutual Fund Portfolio H Installment Receivable H 401(k) Account W 401(k) Account H Annuity Policy (3) Total Invested Assets JT H H W JT H H Personal-Use Assets Primary Residence (4) Vacation Home (5) Auto - Chevy SUV Auto - Chevy Automobile Furniture & Personal Property 2015 Sea Ray 24" Boat & Trailer 1918 Inverted Jenny Stamp Total Use Assets Total Assets Liabilities & Net Worth(2) $ $ $ $ $ Liabilities (Current) Credit Card Debt Current Portion - Primary Residence Current Portion - Vacation Home Total Current Liabilities 5,000 2,475,000 180,000 2,660,000 W JT H $ 14,708 14,708 $ - JT H $ - Total Liabilities $ 14,708 Net Worth $ 10,359,515 Total Liabilities & Net Worth $ 10,374,222 Liabilities (Long-Term) Long-Term Portion - Primary Residence Long-Term Portion - Vacation Home Total Liabilities Long-Term 940,000 2,907,682 1,054,540 105,000 72,000 5,079,222 900,000 750,000 35,000 20,000 150,000 55,000 725,000 2,635,000 $ 10,374,222 $ Notes to Financial Statements: (1) = All assets are stated at fair market value (2) = Liabilities are stated at principal only (3) = Primary Beneficiary Designation is spouse. Contingent beneficiary is children in equal percentages (4) = FMV of Land is $125,000. Cost basis is $370,000 (5) = FMV of Land is $200,000. 1) The Gross Estate, Probate Estate, the Marital Deduction and the FMV of Wife's Inherited Asset Calculations for John: Property Life Insurance Gift Taxes Paid Cash & Checking Savings Account Certificate of Deposits Cash Value of Universal Life Insurance #1 Stock Portfolio Mutual Fund Portfolio Installment Receivable 401(k) Account Annuity Policy FMV at DOD $ 4,000,000 $630,800 5,000 2,475,000 180,000 940,000 2,907,682 1,054,540 72,000 Title H H JT JT H H JT H H H H Gross Estate Probate - 630,800 2,500 1,237,500 180,000 470,000 2,907,682 1,054,540 72,000 Marital Deduction 2,500 1,237,500 180,000 470,000 2,907,682 1,054,540 72,000 FMV of Mary's Inherited Assets 5,000 2,475,000 180,000 2,907,682

Answer rating: 100% (QA)

There wa... View the full answer