You must use Excel commands/functions whenever possible to complete this worksheet for full credit. Your family: Unmarried couple, 1 working and 1 attending college Salary: Income Expenses House: Food: Utilities: $28,000 (only from one partner, the other is unemployed) You owe $85,000 on your house with a 30 year mortgage at 4.5% interest $450/month TV/Internet - $960 per year Cell Phone - $160 per month Power/ Water Sewer - $150 per month You are paying off your two $15,000 (each) cars over 5 years at 1% interest College Tuition - $1000 per month Gym Membership - $300 semi-annually Car: Other Expenses: Step 1: Determine the family's AGI for the year. AGI = #N/A Step 2: Determine the family's tax liability for the year. (Filing status: single) Use the following table to fill in the cells below. Tax Rate Single Married Filing Separately Married Filing Jointly Head of Household 10% 15% 25% 28% 33% 35% up to $8700 Up to $8700 $8700 to $35,350 $8700 to $35,350 $35,350 to $85,650 $35,350 to $71,350 $85,650 to $178,650 $71,350 to $108,725 $178,650 to $388,350 $108,725 to $194,175 more than $388,350 more than $194,175 up to $17,400 $17,400 to $70,700 $70,700 to $142,700 $142,700 to $217,450 $217,450 to $388,350 more than $388,350 up to $12,400 $12,400 to $47,350 $47,350 to $122,300 $122,300 to $198,050 $198,050 to $388,350 more than $388,350 Tax Rate (%) Taxable income range based on filing status $ amount taxed in each bracket Tax rate (decimal) Total tax from each bracket 10% 15% to #N/A #N/A #N/A Tax Liability = Step 3: Determine the family's monthly take-home pay. Monthly Take-home Pay = (AGI - Tax Liability)/12 = #N/A Step 4: Determine your mortgage payment. Monthly mortgage payment = #N/A Step 5: Determine your car payment. Monthly car payment = #N/A Step 6: Determine the family's monthly budget. Monthly Income Monthly take-home pay: Monthly Expenses Mortgage: Food: TV/Internet: Cell Phone: Power/Water/Sewer: Car 1: Car 2: College Tuition: Gym Membership: Total Monthly Expenses = Monthly Total Income = #N/A Monthly Total Income - Monthly Total Expenses = Monthly Net Income = #N/A Step 7: Percentages Calculate the percentage of monthly total income that goes towards the mortgage. #N/A Calculate the percentage of monthly total income that goes towards the non-essential bills (tv/internet, cell phone, college tuition, & gym membership)- #N/A Step 8: Conclusion Using the number you found for the Monthly Net Income, explain what this number means. Give at least one piece of financial advice for your family-BE SPECIFIC!! You must use Excel commands/functions whenever possible to complete this worksheet for full credit. Your family: Unmarried couple, 1 working and 1 attending college Salary: Income Expenses House: Food: Utilities: $28,000 (only from one partner, the other is unemployed) You owe $85,000 on your house with a 30 year mortgage at 4.5% interest $450/month TV/Internet - $960 per year Cell Phone - $160 per month Power/ Water Sewer - $150 per month You are paying off your two $15,000 (each) cars over 5 years at 1% interest College Tuition - $1000 per month Gym Membership - $300 semi-annually Car: Other Expenses: Step 1: Determine the family's AGI for the year. AGI = #N/A Step 2: Determine the family's tax liability for the year. (Filing status: single) Use the following table to fill in the cells below. Tax Rate Single Married Filing Separately Married Filing Jointly Head of Household 10% 15% 25% 28% 33% 35% up to $8700 Up to $8700 $8700 to $35,350 $8700 to $35,350 $35,350 to $85,650 $35,350 to $71,350 $85,650 to $178,650 $71,350 to $108,725 $178,650 to $388,350 $108,725 to $194,175 more than $388,350 more than $194,175 up to $17,400 $17,400 to $70,700 $70,700 to $142,700 $142,700 to $217,450 $217,450 to $388,350 more than $388,350 up to $12,400 $12,400 to $47,350 $47,350 to $122,300 $122,300 to $198,050 $198,050 to $388,350 more than $388,350 Tax Rate (%) Taxable income range based on filing status $ amount taxed in each bracket Tax rate (decimal) Total tax from each bracket 10% 15% to #N/A #N/A #N/A Tax Liability = Step 3: Determine the family's monthly take-home pay. Monthly Take-home Pay = (AGI - Tax Liability)/12 = #N/A Step 4: Determine your mortgage payment. Monthly mortgage payment = #N/A Step 5: Determine your car payment. Monthly car payment = #N/A Step 6: Determine the family's monthly budget. Monthly Income Monthly take-home pay: Monthly Expenses Mortgage: Food: TV/Internet: Cell Phone: Power/Water/Sewer: Car 1: Car 2: College Tuition: Gym Membership: Total Monthly Expenses = Monthly Total Income = #N/A Monthly Total Income - Monthly Total Expenses = Monthly Net Income = #N/A Step 7: Percentages Calculate the percentage of monthly total income that goes towards the mortgage. #N/A Calculate the percentage of monthly total income that goes towards the non-essential bills (tv/internet, cell phone, college tuition, & gym membership)- #N/A Step 8: Conclusion Using the number you found for the Monthly Net Income, explain what this number means. Give at least one piece of financial advice for your family-BE SPECIFIC