Please help me write out the formula in excel! Thank you!

TEMPLATE 2023 Home Insert Draw Page Layout Formulas Data Review View 9 Tell me a Share Cl Comments v A Calibri (Body) v 11 v x _ : Z : '-' x ' Wrap Text - Custom v I 7 u /,l- a, ' ' v L v _ v / V / V v in] L- v i / ' l l 1 v ' \\ a B " v e v 0' v v E E E i i : & v 1: v 9 w 1 22m: :35; $5.; o v in: 23:3. A223? BS : t 2. Assuming Jill makes the maximum allowable contributions to a traditional IRA and a 401(k), how much in Federal taxes will she owe for the current tax year? (Hint make cell (15 100% to max out the contribution for A B C D E F G H | J K L M N O P Q R S T ' '1 ANSWERS MUST BE HARDCODED ' Scenario 1 Jill (25) is single, does not have any dependents, and expects to retire at 60. Her annual salary is $60,000 per year. Use the \"Tax Calculations" worksheet to answer questions 1-3. Use the \"Retirement Calculations\" worksheet to answer questions 4-91 1. Assuming Jill takes the standard deduction and does n_ot make any contributions to any IRA or a 401(k), how much in Federal income taxes will she owe for the current tax year? 4 5 Answer: :Iplease review 6 7 2. Assuming Jill makes the maximum allowable contributions to a traditional IRA and a 401(k), how much in Federal 8 taxes will she owe for the current tax year? (Hint make cell Q5 100% to max out the contribution for 401K) 9 10 Answer: please review 11 3. Assuming Jill makes the maximum allowable contributions to a Roth IRA m a 401(k), how much in Federal taxes 12 will she owe for the current tax year? (No contribution to a traditional IRA, maximum contribution to a Roth IRA) 13 14 Answer: please review 15 4. IfJill saves a total of$29,000 per year in her 401(k) ($22,500) (C19) and traditional IRA ($6,500)(L20) accounts for the next 35 years (retiring at 60) (C11-C13) and her investments grow by 5% (C15) per year, what will be the future value 16 (128)? (Assume it is an ordinary annuity payment) 17 18 Answer: please review 19 6 Scenario 1 3 Scenario 2 IRS Data 6 Tax Calculations Retirement Calculations + El J.I +127% Ready Insert Draw Page Layout Formulas Data Review View 9 Tell me Calibri (Body) v 11 v . , : , WrapText Custom v B T 7" V V 9' v a v E E E g Merge&Centerv , v '2 44a Conditional Format Formatting as Table Cell Styles Insert Delete v /, Format v 0 i9 Share 5 Comments v i ,\\w v vii V; Sort & Find 81 V Filter Select Il' \\ Analyze Data r 2. Assuming Jill makes the maximum allowable contributions to a traditional IRA and a 401(k), how much in Federal taxes will she owe for the current tax year? (Hint make cell (15 100% to max out the contribution for 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 3 7 38 39 40 41 B C D E F G H | J K 5. Based on the future value calculated in 4 above, assume Jill will receive annual payments for 20 years (C14) (at the beginning of each year) in retirement, how much annual alter-tax income will Jill receive in retirement (134)? Assume a 20% (C18) tax bracket and that her retirement investments will grow by 5% (C16) per year. Answer: please review 6. IfJill saves $22,500 in her 401(k) (C19) and has $6,500 (pretax) to contribute aer tax to a Roth IRA (C22 : Multiply by 1working years tax rate to get the after tax contribution to this Roth Account) accounts for the next 35 years (retiring at 60) (C11-C13) and her investments grow by 5% (C15) per year, what will be the future value? (Assume it is an ordinary annuity payment) (Hint make sure to clear L20) Notice this answer compared to question 4! Answer: please review 7. Based on the future value calculated in 6 above, assume Jill will receive annual payments for 20 years (C14) (at the beginning of each year) in retirement, how much annual alter-tax income will Jill receive in retirement (134)? Assume a 20% tax bracket (C18) and that her retirement investments grow by 5% (C16) per year. Answer: please review 8. Based on your answers above, would you recommend Jill contribute currently to a Roth IRA or traditional IRA? Answer: Why? 9. Assuming the income tax bracket during retirement years was 30% (C18) would your answer to number 8 above change? Answer: Why? 6 Scenario 1 3 Scenario 2 IRS Data 6 Tax Calculations Retirement Calculations + L M N O p Q R S Ready _ + 127% AutoSave OFF A APP. CG ... Income Tax Spreadsheet - TEMPLATE 2023 Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments Calibri (Body) v 11 AA ab Wrap Text v Custom LY AP- O. Paste BI U V V LA v Merge & Center Conditional Format Cell Insert Delete Format Sort & Find & Analyze Formatting as Table Styles Filter Select Data C5 4 X V fx B C D E F G H K L M N O P Scenario 2 Zach is 40, married with a non-working spouse (Sarah, 40), has no dependents, and expects to retire at 65. His current salary is $150,000. He is covered by a retirement plan at "Big 4." Sarah is not covered by a retirement plan and has no income for the year. Update the "Tax Calculations" worksheet for the "Married Filing Jointly" (MFJ) status and use it to answer questions 9-12. The 'Retirement Calculations" worksheet will be used to answer questions 13-17. IN HINT - USE THE SINGLE FORMULAS (SNG) TO RECREATE THE MARRIED FILING JOINT FORMULAS 3 (MFJ)INCLUDING EMPLOYEE and SPOUSE 9. Assuming Zach makes the maximum contribution to his 401(k), how much is Zach eligible to deduct if he contributes to a traditional IRA? (In other words, what is Zach's maximum eligible deductible IRA contribution? S12) on UT Answer: please review 10. Assuming Zach makes the maximum contribution to his 401(k), what is the maximum eligible deductible traditional IRA contribution for Sarah? ($13) 60 00 V Answer: please review 1 1. Assuming Zach makes the maximum contribution to his 401(k) and that Zach and Sarah do not contribute to a traditional IRA, what is 10 the maximum amount Zach and Sarah could contribute to a Roth IRA? 11 Answer: 12 Zach: please review 13 Sarah: please review 14 12. How much will Zach and Sarah owe in Federal taxes and how much will be their annual take-home pay (not including State and 15 Employment taxes) if: a. Zach does not contribute to an IRA or 401(k) and does not make a contribution on behalf of Sarah to a Scenario 1 Scenario 2 IRS Data Tax Calculations Retirement Calculations + Ready + 150%AutoSave OFF A A F ?. CG ... Income Tax Spreadsheet - TEMPLATE 2023 Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments Calibri (Body) V 11 AA ab Wrap Text Custom LY AP- O. Paste BI U V V GAV Merge & Center $ ~ % 9 408 28 Conditional Format Cell Insert Delete Format Sort & Styles DV Find & Analyze Formatting as Table Filter Select Data C5 X V fx B C D E F G H K L M N O P 12. How much will Zach and Sarah owe in Federal taxes and how much will be their annual take-home pay (not including State and 15 Employment taxes) if: a. Zach does not contribute to an IRA or 401(k) and does not make a contribution on behalf of Sarah to a 16 Spousal IRA. 17 Answer 18 Federal Taxes: please review 19 Annual Take-Home Pay: please review 20 b. Zach contributes the maximum amount to both a 401(k) and to a traditional IRA and makes the maximum contribution on behalf of Sarah to a Spousal traditional IRA. The contributions to the traditional IRA are the maximum deductible contribution for which Zach and Sarah are eligible. Zach and Sarah do not make any 21 nondeductible contributions. 22 Answer: 23 Federal Taxes: please review 24 Annual Take-Home Pay: please review 25 c. Zach contributes the maximum amount to both a 401(k) and to a Roth IRA and makes the maximum 26 contribution on behalf of Sarah to a Spousal Roth IRA. No contribution to traditional IRA 27 Answer 28 Federal Taxes: please review 29 Annual Take-Home Pay: please review 30 13. Assume Zach and Sarah contribute the maximum deductible amounts you calculated in 9 and 10 above, as well as any allowed non- deductible traditional IRA contributions calculated, to a traditional IRA annually for the next 25 years. Remember that Zach is 40 years old and will retire when he is 65 (in 25 years) and that he made the maximum contribution to his 401k ($22,500). Assuming a growth rate 31 of 7% per year, what will be the future value of their total retirement accounts? Scenario 1 Scenario 2 IRS Data Tax Calculations Retirement Calculations + Ready + 150%AutoSave .0\" Q [23) 9 v G ['3] aIncomeTaXSpreadsheel-TEMPLATE2023 Q 3}\" Home Insert Draw Page Layout Formulas Data Review View 9 Tell me a Share Cl Comments f\" v A Calibri (Body) v 11 v , r 7 7 Wrap Text Custom v ' " "7 Fir W" e '7\" v v Z V , ' p v D3 v ' ' t v Paste ' l , 7 7 7 7 7 * Merge & Cam, 1 V Conditional Format Cell Insert Delete Format Sort & Find a. Analyze 5 * Formatting as Table Styles 0 v Filter Select Data cs ; fx B C D E F G H | J K L M N O P 13. Assume Zach and Sarah contribute the maximum deductible amounts you calculated in 9 and 10 above, as well as any allowed non- deductible traditional IRA contributions calculated, to a traditional IRA annually for the next 25 years. Remember that Zach is 40 years old and will retire when he is 65 (in 25 years) and that he made the maximum conhibution to his 401k ($22,500). Assuming a growth rate 31 of 7% per year, what will be the future value of their total retirement accounts? 32 Answer: please review 3 3 14. Based on the future value calculated in 13 above, assume Zach and Sarah will receive annual payments for 20 years (at the beginning of each year) in retirement, how much annual after-tax income will they receive in retirement? Assume a 25% tax bracket and a growth 34 rate of 7% per year. 35 Answer: please review 36 15. Assume Zach and Sarah have $13,000 pretax that they want to contribute post tax to a Roth IRA annually for the next 25 years. Remember that Zach made the maximum contribution to his 401(k) and assume no contributions to a traditional IRA. Assuming a growth 37 rate of 7% per year, what will be the future value? 38 Answer: please review 39 16. Based on the rture value calculated in 15 above, assume Zach and Sarah will receive annual payments for 20 years (at the beginning of each year) in retirement, how much annual after-tax income will they receive in retirement? Assume a 25% tax bracket and 7% growth 40 rate 4 1 Answer: please review 4 2 43 17. Based on your answers above, would you recommend Zach and Sarah contribute currently to a Roth IRA or traditional IRA? Why? 44 Answer: 45 Why? 3 Scenario 1 Scenario 2 IRS Data 6 Tax Calculations Retirement Calculations + Ready El J.I __ __ + 150% AutoSave OFF A APP. CG ... Income Tax Spreadsheet - TEMPLATE 2023 Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments Calibri (Body) v 11 AA = ab Wrap Text General ZY AP - O. v Z Paste BIUV DVAv E = Merge & Center v $ ~ % " 00 20 Conditional Format Cell Insert Delete Format Sort & Find & Analyze Formatting as Table Styles Filter Select Data 136 fx B C D E F G H 22 23 24 401(k) Elective Deferral Limit $22,500 Phase Out Begins Phase Out Ends 25 $218,000.00 $228,000.00 26 IRA Contribution Limits Spouse Spouse 27 Age = 50 $7,500 29 30 31 32 33 34 Phase Out Begins Phase Out Ends 35 $138,000.00 $153,000.00 36 37 ROTH IRA PHASE OUT FOR SINGLES 38 39 40 41 42 43 44 45 Scenario 1 Scenario 2 IRS Data Tax Calculations Retirement Calculations + Ready + 170%AutoSave on Q [,9,' 9 (j 3 Income Tax Sprea sheet TEMPLATE 2023 Q go Home Insert Draw Page Layout Formulas Data Review View 9 Tell me )9 Share Cl Comments f\" v A Calibri (Body) v 11 v . , 7 7 Wrap Text General v ' if "if L 7 V v Z V , ' p v i (I [a v v ' Paste ' I e 7 7 7 7 7 7 I Conditional Format Cell Delete Format Sort 8. Find & Analyze 5 7 Merge 8' \"my ' V Formatting as Table Styles 0 v Filter Select Data (MFJ_E|igi... ; fx A B C D E F G H | K M 0 P 2 3 INSTRUL'noNS Income Tax Calculator Singlea MFJh 4 401K Calculations 5 Blue Students Hard Code these values (or can modify) Gross Salary Contribution Percentage 6 Orange Formulas to be updated by student 401K Contribution $ - $ - Contribution Amount (based on percentage above) 7 W~2 Box 1 (Salaries, Tips, other) 5 - S - Max Eligible Contribution 8 Income Tax Calculator IRA Contribution for Employee 5 - $ - 401K Contribution (actual) 9 IRA Contribution for Spouse n/a 5 - 10 The income tax calculator assumes that a taxpayer takes the standard Adjusted Gross Income (AGI) S - S - Traditional IRA Calculations 11 deduction. User enters the gross salary. Standard Deduction 5 13,850 3 27,700 Modified AGI 12 Taxable Income 5 (13,850) $ (27,700.00) Maximum Eligible DeductibIeIRA Contribution (Employee) 13 W Maximum Eligible Deductible IRA Contribution (Spouse) 14 The user determines the amount of the 401K contribution (Cell QB for Single) Total Federal Tax #N/A 5 - IRA Contribution (Employee) 15 based on the 401K calculations (contribution percentage, amount, and eligible IRA Contribution (Spouse) 16 contribution). Users can vary the percentages for the 401K, but should not Take Home Annual Pay (Less Federal Taxes) #N/A 5 _ 17 go over the maximum eligible contribution. Roth IRA Contributions 18 Modified AGI 19 Maximum Roth IRA Contribution (Employee) 20 Traditional IRA Calculations a) Used for Scenario 1 Maximum Roth IRA Contribution (Spouse) 21 b) Used for Scenario 2 Roth IRA Contribution (Employee) 22 Roth IRA Contribution (Spouse) The user enters the amount of IRA contributions for a single filer in cell 014. 23 This should not exceed the maximum eligible contribution In cell Q12. 24 Federal Tax Calculation ZS Roth IRA Calculations Base Amount from Table 26 Federal Tax Bracket 27 The user enters the amount of Roth IRA contributions for a single filer in cell Marginal amount applicable to bracket 28 (121. This should not exceed the maximum eligible contribution in cell Q19. Plus % 29 Total Tax 30 Federal Tax Calculation 31 32 The cells in this section calculate the federal tax based on thetax tables. The 33 total amount in cell (129 should match cell K14. 34 Ready 5 Scenario 1 0 Scenario 2 IRS Data 8 Tax Calculations Retirement Calculations + __ + 119% AutoSave OFF A APP. CG ... Income Tax Spreadsheet - TEMPLATE 2023 Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments Calibri (Body) v 11 AA ab Wrap Text General ZY AP- O. Paste BI U V V GAV Merge & Center $ ~ % 9 408 28 Conditional Format Cell Insert Delete Format Sort & Find & Analyze Formatting as Table Styles Filter Select Data MFJ_Eligi... X V f. K M NO P Q R S T U V single MFJb Single MFJ 401K Calculations Contribution Percentage Contribution Amount (based on percentage above) $ Max Eligible Contribution 22,500 Please Review |401K Contribution (actual) S 9 na 10 Traditional IRA Calculations 11 13,850 27,700 Modified AGI S 12 13,850) (27,700.00) Maximum Eligible DeductibleIRA Contribution (Employee) 6500 Please Review 13 Maximum Eligible Deductible IRA Contribution (Spouse) n/a Please Review 14 #N/A IRA Contribution (Employee) 15 IRA Contribution (Spouse) n/a 16 #N/A 17 Roth IRA Contributions 18 Modified AGI $ 19 Maximum Roth IRA Contribution (Employee) 6500 Please Review 20 Maximum Roth IRA Contribution (Spouse) na Please Review 21 Roth IRA Contribution (Employee) 22 Roth IRA Contribution (Spouse) n/a 23 24 Federal Tax Calculation 25 Base Amount from Table #N/A 26 Federal Tax Bracket IN/A 27 |Marginal amount applicable to bracket IN/A 28 Plus % IN/A 29 Total Tax #N/A 0 30 31 D Scenario 1 Scenario 2 IRS Data Tax Calculations Retirement Calculations + Ready + 125%AutoSave . as: a Income Tax Spreadsheet - TEMPLATE 2023 Home Insert Draw Page Layout Formulas Data Review View 9 Tell me i9 Share Cl Comments f" v? Calibri v 11 v A\" AV E E 3 1'7 v 253 WrapTextv General v Lji'l v J'v /'v g H\" 'erLX' v v Z V ? v p v E3 IE v 1 v Paste U (I; v A v E E E : : I v 0 a co Conditional Format Cell Insert Delete Format Sort& Find& Analyze 6 B I _ v v L L 5': Merge&Center $ V A) ' '00 9'0 Formatting as Table Styles 0 V Filter Select Data A1 ; )9: A B C D E F G H | J 2 INSTRUCTIONS 3 4 This retirement calculator calculates the annual income after taxes during the retirement years based on 401K and IRA contributions. 5 6 hardcode required 7 8 9 10 ASSUMPTIONS 1 1 Current Age (Employee) 12 Retirement Age (Employee) 13 Number of Years Until Retirement 14 Number of Expected Retirement Years 15 Before Tax Return on Savings during Working Years 16 Before Tax Return on Savings during Retirement Years Withdraws made at the beginning of each year 17 Income Tax Bracket during Working Years 18 Income Tax Bracket during Retirement Years 19 Annual 401k Contributions 20 Total Deductible IRA Contributions (Employee and Spouse) Empli 21 Total Nondeductible IRA Contributions (Employee and Spouse) Spou' 22 Total After Tax Roth IRA Contributions (Employee and Spouse) TOTA 23 TOTAL Annual Contribution (Employee and Spouse) Contributions made at the W 24 25 26 401K IRA Roth IRA TOTAL 27 Total annual contribution (after tax) '5 - VS - '5 - $ - 28 Account Value at Retirement hint: use future value formula (FV) VS - r$ - V$ - $ - 29 3 Scenario 1 9 Scenario 2 IRS Data 8 Tax Calculations Retirement Calculations + Ready (a J.I _ + 150% AutoSave OFF A A P Y. CG ... Income Tax Spreadsheet - TEMPLATE 2023 Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments Calibri v 11 AA = ab Wrap Text General ZY AP - O. Paste BIU BY DVAv E Merge & Center v $ ~ % " 00 20 Conditional Format Cell Insert Delete Format Sort & Find & Analyze Formatting as Table Styles Filter Select Data A1 4 X V fx A B C D E F G H I J K L M N 12 Retirement Age (Employee 13 Number of Years Until Retirement 14 Number of Expected Retirement Years 15 Before Tax Return on Savings during Working Years 16 Before Tax Return on Savings during Retirement Years Withdraws made at the beginning of each year Income Tax Bracket during Working Years 25% Income Tax Bracket during Retirement Years Annual 401k Contributions Deductible IRA Non-Deductible IRA Total 20 Total Deductible IRA Contributions (Employee and Spouse) $ Employee 21 Total Nondeductible IRA Contributions (Employee and Spouse) Spouse 22 Total After Tax Roth IRA Contributions (Employee and Spouse) TOTAL 0 0 23 TOTAL Annual Contribution (Employee and Spouse) Contributions made at the end of each year 24 25 26 401K IRA Roth IRA TOTAL 27 Total annual contribution (after tax) $ 28 Account Value at Retirement hint: use future value formula (FV) 29 30 Annual Income Before Taxes during Retirement Years hint: use payment of an annuity formula (PMT) #NUM! #NUM! #NUM! #NUM! 31 Basis in traditional IRA contributions (Total nondeductible traditional IRA contributions 32 Nontaxable Return of Basis Percentage 0.0% 33 34 Annual Income After Taxes during Retirement Years #NUM! #NUM! #NUM! #NUM! 35 Monthly Income After Taxes during Retirement Years #NUM #NUM! #NUM! #NUM! 36 37 38 39 40 41 44 45 46 48 49 Scenario 1 Scenario 2 IRS Data Tax Calculations Retirement Calculations + Ready + 114%