please help me with excel assignment on fin386 estate calculation

Sharon and Mark have been married for many years and have two children Shai and Michael. Sharon recently died in a car accident caused by a drunk driver. She is an Arizona resident at the time of her death. When she dies she and Mark had the following property. Assume that her primary beneficiary in her will is her husband Mark.

Personal residence valued at $500,000 held tenancy by the entirety with Mark. The house has a $200,000 outstanding mortgage.

Honda Accord owned by Sharon valued at $20,000 held fee simple by Sharon.

Ford F150 owned by Mark valued at $40,000 held fee simple by Mark.

Her wedding ring valued at $10,000 held fee simple by Sharon.

Boat valued at $100,000 held tenancy in common by Sharon and Mark with equal contributions from both of them.

Life insurance policy on Mark's life owned by Sharon. The fair market value is $150,000 and the death benefit is $300,000.

Life insurance policy on Sharon's life owned by Sharon. The fair market value is $100,000 and the death benefit was $600,000. Mark is the beneficiary.

Sharon's IRA with account value of $2,500,000 with Shai and Michael as 50/50 beneficiaries.

Irrevocable trust with a current value of $75,000 with Sharon as the grantor and Shai and Michael as the beneficiaries. There were no taxable gifts on the initial or subsequent transfers to the trust.

The accident was found to be the fault of the drunk driver so Sharon's heirs received $200,000 wrongful death and $600,000 pain and suffering.

Sharon's last medical expenses were $30,000, her funeral expenses were $15,000

The administration of the estate was $75,000

Sharon's outstanding debts were $100,000

Sharon left $75,000 to charity

Sharon's will states that debts and expenses will reduce the assets that will transfer to Mark.

Access Tax Rates

Here's the homework excel that you need to fill in

Complete the Excel workbook called Sharon Estate Tax Calculation. If you are thinking, this looks complex it is. However, if you read the chapter 6 you will realize there is an example that is just like this on pages 218 and 219. Walk through it before you begin.

Complete the Gross Estate Calculation for Sharon. For each asset put in the full value, the amount that is Sharon's interest, the amount that goes through probate, and the amount in her gross estate. The totals for Sharon's interest and amount in gross estate will be the same. The amount that goes through probate will be less. Check your answers by taking this survey that way you will know if you have it correct before moving on to step 2. Gross Estate Calculation Survey If you do not have it correct look at the feedback and check your answer again or reach out via Canvas inbox. Do not move forward until you get the correct answer.

Complete the Marital Deduction Summary. For each item in the gross estate determine if the asset is available for the spouse. If an asset does not transfer to the spouse at Sharon's death it is not available to her spouse. Put the gross estate amounts from the gross estate calculation in the first column and yes or no in the second column. If you put yes in the second column put the amount from column 1 in the available for marital deduction column. If you put no in the second column put zero in the available for marital deduction column. Check your answers by taking this survey that way you will know if you have it correct before moving on to step 3. Marital Deduction Summary Quiz If you do not have it correct look at the feedback and check your answer again or reach out via Canvas inbox. Do not move forward until you get the correct answer.

Complete the Abbreviated Estate Tax Formula.

Take the Gross estate from step 1 and put it in Gross estate. Next enter all the deductions and subtract them from the gross estate to get the adjusted gross estate. Check your answer by taking this survey that way you will know if you have it correct before moving on.Adjusted Gross Estate Do not move forward until you get the correct answer.

Next enter the charitable deduction, marital deduction and state death tax (if applicable) and total these deductions and subtract from the adjusted gross estate to get the taxable estate. Check your answer by taking this survey that way you will know if you have it correct before moving on.Taxable Estate Do not move forward until you get the correct answer.

Add any Post-1976 Gifts to get the Tentative tax base. Check your answer by taking this survey that way you will know if you have it correct before moving on.Tentative Tax Base Do not move forward until you get the correct answer.

Compute the tentative tax using the estate tax tables found in the link above. Check your answer by taking this survey that way you will know if you have it correct before moving on. Tentative Tax Do not move forward until you get the correct answer.

Complete the credit for previous gift tax paid, applicable estate tax credit (does she have enough applicable credit to cover the tentative tax? if so enter the amount of the tentative tax), enter the remaining credits and then compute the federal tax liability. Check your answer by taking this survey that way you will know if you have it correct before moving on. Applicable Estate Tax Credit Do not move forward until you get the correct answer.

Finally, determine how much unused estate tax credit will be available for her spouse. Compute the Total Credit Available in 2022 using the tax rate tables. Figure the tax on the 12.06 million exclusion from 2022. Subtract the amount used in the prior step to get the Unused Estate Transfer Credit. Check your answer by taking this survey that way you will know if you have it correct before moving on. Unused Estate Transfer Credit Do not move forward until you get the correct answer.

Submit your completed worksheet. Your grade is based on your completed worksheet. The survey's above were to help you know you were on track.

Rubric

Estate Tax Calculation

Criteria

Ratings

Pts

This criterion is linked to a Learning OutcomeGross Estate Calculation

Sharon's interest, probate estate and Gross Estate Correct

22 pts 2 point for every asset that is correct

0 pts No Marks

22 pts

This criterion is linked to a Learning OutcomeMarital Deduction Summary

11 pts Marital Deduction Summary is Correct 1 pt for every asset that is correct.

0 pts No Marks

11 pts

This criterion is linked to a Learning OutcomeAbbreviated Estate Tax Formula

12 pts Abbreviated estate tax formula correct Losses during administration 1pt Adjusted Gross Estate 1pt Taxable Estate 2 pt Equals Tentative Tax Base 2pt Allowable credits 2 pt Federal Estate Tax Liability 2pt Unused Estate Transfer Credit 2 pt

0 pts No Marks

12 pts

Total Points: 45

This presentation cannot be copied or redistributed without authorization from Arizona State University and the Arizona Board of Regents Gross Estate Calculation Asset Marital Deduction Summary Asset GrossEstateAvailableforSpouseAvailableforMaritalDeduction Honda Accord F150 Less Deductions from Marital Deduction Summary - Medical Expenses - Administrative Expenses - Funeral Expenses - Debts of Decedent - Charitable Contribution Total Deductions from Marital Deduction Summary Total Deductions from Marital Deduction Summary Total Marital Deduction Estate Tax Calculation Gross Estate Less Deductions: Last Medical Expenses Administrative Expenses Funeral Expenses Debts of Decedent Losses During Estate Administration Total Deducations Equals: Adjusted Gross Estate Less Charitable Deduction Less Marital Deduction Less State Death Tax Deductin Less Total Deductions: Equals: Taxable Estate Add: Previous Taxable Gifts Equals Tentative tax base Tentative Tax Less Allowable Credits Credit for Previous Gift Tax Paid Applicable Estate Tax Credit Credit for tax paid on prior transfers Foreign Death Tax Credit Debts of Decedent Losses During Estate Administration Total Deducations Equals: Adjusted Gross Estate Less Charitable Deduction Less Marital Deduction Less State Death Tax Deductin Less Total Deductions: Equals: Taxable Estate Add: Previous Taxable Gifts Equals Tentative tax base Tentative Tax Less Allowable Credits Credit for Previous Gift Tax Paid Applicable Estate Tax Credit Credit for tax paid on prior transfers Foreign Death Tax Credit Equals Federal Tax Liability Sharon's Unused Estate Transfer Credit Total Credit Current Year Amount Used Unused Estate Transfer Credit This presentation cannot be copied or redistributed without authorization from Arizona State University and the Arizona Board of Regents Gross Estate Calculation Asset Marital Deduction Summary Asset GrossEstateAvailableforSpouseAvailableforMaritalDeduction Honda Accord F150 Less Deductions from Marital Deduction Summary - Medical Expenses - Administrative Expenses - Funeral Expenses - Debts of Decedent - Charitable Contribution Total Deductions from Marital Deduction Summary Total Deductions from Marital Deduction Summary Total Marital Deduction Estate Tax Calculation Gross Estate Less Deductions: Last Medical Expenses Administrative Expenses Funeral Expenses Debts of Decedent Losses During Estate Administration Total Deducations Equals: Adjusted Gross Estate Less Charitable Deduction Less Marital Deduction Less State Death Tax Deductin Less Total Deductions: Equals: Taxable Estate Add: Previous Taxable Gifts Equals Tentative tax base Tentative Tax Less Allowable Credits Credit for Previous Gift Tax Paid Applicable Estate Tax Credit Credit for tax paid on prior transfers Foreign Death Tax Credit Debts of Decedent Losses During Estate Administration Total Deducations Equals: Adjusted Gross Estate Less Charitable Deduction Less Marital Deduction Less State Death Tax Deductin Less Total Deductions: Equals: Taxable Estate Add: Previous Taxable Gifts Equals Tentative tax base Tentative Tax Less Allowable Credits Credit for Previous Gift Tax Paid Applicable Estate Tax Credit Credit for tax paid on prior transfers Foreign Death Tax Credit Equals Federal Tax Liability Sharon's Unused Estate Transfer Credit Total Credit Current Year Amount Used Unused Estate Transfer Credit