i need help finding the Roth 401k

im currently right here (first photo)

The second photo is what the table should look like when completed.

Im going to post the formulas for each Columns directly after the second picture in order

The final investment (the 401k) does not have a max amount so it only has two possible situations: extra contributions or not. You should only check the second to last investment (the savings) and if it has been maxed out, you should add ALL of the contributions from all three prior investments.

3. In the Investment worksheet, you will be calculating the monthly balance of a persons investments over a 30 year period. So, you will enter the formulas for each investment vehicle in the first row (row 7). You then will autofill those down for 359 more cells. These 360 total cells represent every month of this persons investments over 30 years. Follow each of the steps below

Manually enter a formula that will calculate the balance of the IRA investment in B7. To do this, take the previous balance, add the contribution, then add the earned interest for the month (which is the balance times the interest rate divided by 12). One additional aspect is that you have decided to have a maximum amount for your investments. For this investment, it is the amount in B3 ($10,000). You should adjust your formula to account for this by having an IF function that stops adding the monthly contribution once the current balance exceeds the maximum amount. It should still add the interest though. Once this is done, autofill the formula downwards (down column B) until you have reached 360 total balance listings.

The formula for the next two investments will have the same core (previous balance plus contribution plus interest). A significant difference though is that there are now four possible calculations instead of two. You still have the maximum balance situation but you also will be adding the payment from the previous investment once that balance has reached its own maximum. The 2x2 set of possibilities would be as follows

o Situation 1: balance is not maxed out & no extra payment from the previous investment

o Situation 2: balance is maxed out & no extra payment from the previous investment

o Situation 3: balance is not maxed out & an extra payment from the previous investment

o Situation 4: balance is maxed out & an extra payment from the previous investment

So, you would have four IF statements that address each of these situations and has an appropriate formula for each (not having or having a contribution depending on the balance being maxed out AND not having or having an extra contribution if the previous investment(s) have been maxed out)

The final investment (the 401k) does not have a max amount so it only has two possible situations: extra contributions or not. You should only check the second to last investment (the savings) and if it has been maxed out, you should add ALL of the contributions from all three prior investments.

With all the formulas entered and autofilled, you need to then autofill the date (column A) down for the entire 360 month rows. Each row should represent a month so each date should be one month advanced from the previous cell. Then, enter a SUM function in cell G6 and autofill that down to where the investment contributions end.

The final total at the end (2/1/2050) should be $2,351,041.86

Accounting AA = EE A Wrap Text Merge & Center BIUR $ % 978- Conditional Format as a Formatting Table Styl Styles Font Alignment Number X =IF((17)>=D$3, (E6+D$24E$2)+(Ech 8.2096 mated Growth thly Contributi =D$3. (E6+D$2+$2)+(E6 3/1/2020 $ 4/1/2020 S 5/1/2020 $ 6/1/2020 $ 7/1/2020 $ 8/1/2020 $ 9/1/2020 S 10/1/2020 $ 11/1/2020 $ 12/1/2020 $ 1/1/2021 S 2/1/2021 3/1/2021 S 4/1/20215 5/1/2021 IRA - S 50.00 100.25 150.74 201.48 252.47 303.71 355.21 406.95 $250.00 $ S501.65 $ $754.95 $ $1.009.92 $ $1,266.57 $ $1,524.91 $ $1,784.94 S $2,046.70 S $2,310.17 S $2,575.38 S $2,042.335 53.111.04 $3,381.53S 53,653.795 160.00 320.25 480.76 641.52 802.54 963.81 1,125.33 1.287.12 1.449.15 1,611.45 1.721.00 1,936.81 2,099.88 2.263.20 511.21 563.72 616.49 669.52 722.82 Logical functions Payments Type here to search Estimated Growth Monthly Contributie Max Balance $ 5.90% $50.00 10,000.00 7.90% $250.00 40,000.00 1.90% $160.00 50,000.00 N/A 8.20% $1,156.00 $ $ Dale IRA 529 Savings Roth 401 Total Investment S S $ $ $ 3/1/2020 $ 4/1/2020 $ 5/1/2020 $ 6/1/2020 $ 7/1/2020 $ 8/1/2020 $ 9/1/2020 $ 10/1/2020 $ 11/1/2020 $ 12/1/2020 $ 1/1/2021 $ 2/1/2021 $ 3/1/2021 $ 4/1/2021 $ 5/1/2021 $ 6/1/2021 $ 7/1/2021 $ 8/1/2021 s 9/1/2021 $ 10/1/2021 $ 11/1/2021 $ 12/1/2021 $ 50.00 $ 100.25 $ 150.74 $ 201.49 $ 252.47 $ 302.71 $ 255.21 $ 406.95 $ 458.95 $ 511.21 $ 563.72 $ 616.49 $ 669.52 $ 722.82 $ 776.37 $ 830.19 $ 834.27 $ 250.00 501.65 754.95 1,009.92 1,266.57 1,524.91 1,784.94 2,046.70 2,310.17 2,575.33 2,342.33 3,111.04 3,381.53 3,653.79 3,927.84 4,203.70 4,481.37 5 4,760.68 5,042.22 5,325.41 5,610.47 $ $ $ $ $ $ $ $ S $ $ $ $ $ $ $ 160.00 $ 320.25 $ 480.76 $ 641.52 $ 802.54 $ 963.81 $ 1,125.33 $ 1,287.12 $ 1,449.15 $ 1,611.45 $ 1,774.00 $ 1,936.81 $ 2,099.88 $ 2,263.20 $ 2,426.78 $ 2.590.63 $ ,754.73 S 2,919.09 $ 3,083.71 $ 3,248.59 $ 3,413.74 $ 1,156.00 2,319.90 3,491.75 4,671.61 5,859.53 7,055.58 8,259.79 9.472.23 10,692.96 11,922.03 13,159.49 14,405.42 15,659.85 16,922.86 19,194.50 19,474.83 20,763.91 22,051.80 23,368.55 24,684.24 26,008.91 1,616,00 2,242.04 4,878.20 6,524.53 8,191.11 9,848.00 11,525.27 13,212.99 14,911.22 16,620.06 18.329.55 20,069.76 21,810.78 23,562.67 25,325.50 27,099.34 28,884.28 30,680.38 32,487.71 34,306.36 36.136.39 2 993.23 1,048.12 1.103.27 $ $ $ $ $ $ $ $ Search Assignment 2v2 template (1).xlsx out Formulas Data View Help Review Accounting A A A Z== 0 323 ab Wrap Text Merge & Center - $ - % Alignment Numb =IF(B6>=B$3,B6+(B6*B$1/12),(B6+B$2)+(B6*B$1/12)) 5.90% $50.00 000.00 7.90% $250.00 40,000.00 1.90% $160.00 50,000.00 N/A $1,1 $ $ IRA 529 Savings Roth 50.00 100.25 150.74 201.48 252.47 303.71 $250.00 $ $501.65 $ $754.95 $ $1,009.92 s $1,266.57 $ $1,524,91 s 160.00 $ 320.25 $ 480.76 $ 641.52 $ 802.54 $ 963.81 in en is nisises 1,31 2,64 3,97 5,318 6,670 8,032 25 ' 2. Wrap Text Currency Insert Delete- A E s Merge & Center - $ -% % -99 Conditional Format as Cell Formatting Table Styles Format Alignment 55 Number Styles Cells =IF(B7>=B$3,1F(C6>=C$3,C6 (C6*C$1/12),(B$2+C6+C$2)+C6*C$1/12)),IF(C6>=C$3,C6+(C6*C$1/12),(C6+$2)+(C6*C$1/12))) E F C 0% 0.00 00 257.90% $250.00 40,000.00 D 1.90% $160.00 50,000.00 N/A 8.20% $1,156.00 $ $ Roth 401k Total Investment Savings RAE $ R 529 $ $250 $ $501.65 $ $754.95 $ $1,009.92 $ $1,266.57 $ $1,524.91 $ $1,784.94 $ $2,046.70 $ $2,310.17 $ $2,575.38 $ $2,842.33 $ $3,111.04 S $3,381.53 $3,653.79 2017042 160.00 $ 320.25 480.76 $ 641.52 $ 802.54 963.81 1,125.33 1,287.12 $ 1,316.00 2,640.99 3,975.04 5,318.20 6,670.54 8,032.13 9,403.01 10,783.27 12,172.95 13,572.13 14,980.88 16,399.25 17,827.31 nnnnnn 1,611.45 $ 1,774,00 $ 1,936.81 $ 2,099.88 $ 19,265.13 he Layout Formulas Data Review View Help - 11 AA EEE P 2Wrap Text Accounting Insert Delete A A Merge & Center - $ -% 8-9 Conditional Format as Cell Formatting Table Styles Format - Alignment 5 Number Styles Cells =IF(C7>=C$3,1F(D6>=D$3,06+(D6*D$1/12).(C$2+06+D$2)+(D6*D$1/12)),1F(D6>D$3,06+(D6*D$1/12).(06+0$2)*(D6*D$1/12) D 5.90% $50.00 10,000.00 7.90% $250.00 40,000.00 1.90% $160.00 50,000.00 N/A 8.20% $1,156.00 $ $ IRA Savings Roth 401K Total Investment S 50.00 100.25 150.74 201.48 252.47 303.71 355.21 406.95 458.95 511.21 563.72 616.49 669.52 722.82 529 - $250.00 $ $501.65 $ $754.95 $ $1,009.92 $ $1,266.57 $ $1,524.91 $ $1,784.94 $ $2,046.70 $ $2,310.17 $ $2,575.38 S $2,842.33 S $3,111.04 S $3,381.53 $ $3,653.79 $ 160.00 $ 320.25 $ 480.76 $ 641.52 $ 802.54 $ 963.81 S 1,125.33 $ 1.287.12 $ 1,449.15S 1.611.45 S 1.774.00 $ 1.936.81 $ 2,099.88 $ 2.263.20 $ 1,316.00 2.640.99 3,975.04 5,318.20 6,670.54 8,032.13 9,403.01 10,783.27 12,172.95 13,572.13 14,980.88 16.39925 17,827.31 19.265.13 Accounting AA = EE A Wrap Text Merge & Center BIUR $ % 978- Conditional Format as a Formatting Table Styl Styles Font Alignment Number X =IF((17)>=D$3, (E6+D$24E$2)+(Ech 8.2096 mated Growth thly Contributi =D$3. (E6+D$2+$2)+(E6 3/1/2020 $ 4/1/2020 S 5/1/2020 $ 6/1/2020 $ 7/1/2020 $ 8/1/2020 $ 9/1/2020 S 10/1/2020 $ 11/1/2020 $ 12/1/2020 $ 1/1/2021 S 2/1/2021 3/1/2021 S 4/1/20215 5/1/2021 IRA - S 50.00 100.25 150.74 201.48 252.47 303.71 355.21 406.95 $250.00 $ S501.65 $ $754.95 $ $1.009.92 $ $1,266.57 $ $1,524.91 $ $1,784.94 S $2,046.70 S $2,310.17 S $2,575.38 S $2,042.335 53.111.04 $3,381.53S 53,653.795 160.00 320.25 480.76 641.52 802.54 963.81 1,125.33 1.287.12 1.449.15 1,611.45 1.721.00 1,936.81 2,099.88 2.263.20 511.21 563.72 616.49 669.52 722.82 Logical functions Payments Type here to search Estimated Growth Monthly Contributie Max Balance $ 5.90% $50.00 10,000.00 7.90% $250.00 40,000.00 1.90% $160.00 50,000.00 N/A 8.20% $1,156.00 $ $ Dale IRA 529 Savings Roth 401 Total Investment S S $ $ $ 3/1/2020 $ 4/1/2020 $ 5/1/2020 $ 6/1/2020 $ 7/1/2020 $ 8/1/2020 $ 9/1/2020 $ 10/1/2020 $ 11/1/2020 $ 12/1/2020 $ 1/1/2021 $ 2/1/2021 $ 3/1/2021 $ 4/1/2021 $ 5/1/2021 $ 6/1/2021 $ 7/1/2021 $ 8/1/2021 s 9/1/2021 $ 10/1/2021 $ 11/1/2021 $ 12/1/2021 $ 50.00 $ 100.25 $ 150.74 $ 201.49 $ 252.47 $ 302.71 $ 255.21 $ 406.95 $ 458.95 $ 511.21 $ 563.72 $ 616.49 $ 669.52 $ 722.82 $ 776.37 $ 830.19 $ 834.27 $ 250.00 501.65 754.95 1,009.92 1,266.57 1,524.91 1,784.94 2,046.70 2,310.17 2,575.33 2,342.33 3,111.04 3,381.53 3,653.79 3,927.84 4,203.70 4,481.37 5 4,760.68 5,042.22 5,325.41 5,610.47 $ $ $ $ $ $ $ $ S $ $ $ $ $ $ $ 160.00 $ 320.25 $ 480.76 $ 641.52 $ 802.54 $ 963.81 $ 1,125.33 $ 1,287.12 $ 1,449.15 $ 1,611.45 $ 1,774.00 $ 1,936.81 $ 2,099.88 $ 2,263.20 $ 2,426.78 $ 2.590.63 $ ,754.73 S 2,919.09 $ 3,083.71 $ 3,248.59 $ 3,413.74 $ 1,156.00 2,319.90 3,491.75 4,671.61 5,859.53 7,055.58 8,259.79 9.472.23 10,692.96 11,922.03 13,159.49 14,405.42 15,659.85 16,922.86 19,194.50 19,474.83 20,763.91 22,051.80 23,368.55 24,684.24 26,008.91 1,616,00 2,242.04 4,878.20 6,524.53 8,191.11 9,848.00 11,525.27 13,212.99 14,911.22 16,620.06 18.329.55 20,069.76 21,810.78 23,562.67 25,325.50 27,099.34 28,884.28 30,680.38 32,487.71 34,306.36 36.136.39 2 993.23 1,048.12 1.103.27 $ $ $ $ $ $ $ $ Search Assignment 2v2 template (1).xlsx out Formulas Data View Help Review Accounting A A A Z== 0 323 ab Wrap Text Merge & Center - $ - % Alignment Numb =IF(B6>=B$3,B6+(B6*B$1/12),(B6+B$2)+(B6*B$1/12)) 5.90% $50.00 000.00 7.90% $250.00 40,000.00 1.90% $160.00 50,000.00 N/A $1,1 $ $ IRA 529 Savings Roth 50.00 100.25 150.74 201.48 252.47 303.71 $250.00 $ $501.65 $ $754.95 $ $1,009.92 s $1,266.57 $ $1,524,91 s 160.00 $ 320.25 $ 480.76 $ 641.52 $ 802.54 $ 963.81 in en is nisises 1,31 2,64 3,97 5,318 6,670 8,032 25 ' 2. Wrap Text Currency Insert Delete- A E s Merge & Center - $ -% % -99 Conditional Format as Cell Formatting Table Styles Format Alignment 55 Number Styles Cells =IF(B7>=B$3,1F(C6>=C$3,C6 (C6*C$1/12),(B$2+C6+C$2)+C6*C$1/12)),IF(C6>=C$3,C6+(C6*C$1/12),(C6+$2)+(C6*C$1/12))) E F C 0% 0.00 00 257.90% $250.00 40,000.00 D 1.90% $160.00 50,000.00 N/A 8.20% $1,156.00 $ $ Roth 401k Total Investment Savings RAE $ R 529 $ $250 $ $501.65 $ $754.95 $ $1,009.92 $ $1,266.57 $ $1,524.91 $ $1,784.94 $ $2,046.70 $ $2,310.17 $ $2,575.38 $ $2,842.33 $ $3,111.04 S $3,381.53 $3,653.79 2017042 160.00 $ 320.25 480.76 $ 641.52 $ 802.54 963.81 1,125.33 1,287.12 $ 1,316.00 2,640.99 3,975.04 5,318.20 6,670.54 8,032.13 9,403.01 10,783.27 12,172.95 13,572.13 14,980.88 16,399.25 17,827.31 nnnnnn 1,611.45 $ 1,774,00 $ 1,936.81 $ 2,099.88 $ 19,265.13 he Layout Formulas Data Review View Help - 11 AA EEE P 2Wrap Text Accounting Insert Delete A A Merge & Center - $ -% 8-9 Conditional Format as Cell Formatting Table Styles Format - Alignment 5 Number Styles Cells =IF(C7>=C$3,1F(D6>=D$3,06+(D6*D$1/12).(C$2+06+D$2)+(D6*D$1/12)),1F(D6>D$3,06+(D6*D$1/12).(06+0$2)*(D6*D$1/12) D 5.90% $50.00 10,000.00 7.90% $250.00 40,000.00 1.90% $160.00 50,000.00 N/A 8.20% $1,156.00 $ $ IRA Savings Roth 401K Total Investment S 50.00 100.25 150.74 201.48 252.47 303.71 355.21 406.95 458.95 511.21 563.72 616.49 669.52 722.82 529 - $250.00 $ $501.65 $ $754.95 $ $1,009.92 $ $1,266.57 $ $1,524.91 $ $1,784.94 $ $2,046.70 $ $2,310.17 $ $2,575.38 S $2,842.33 S $3,111.04 S $3,381.53 $ $3,653.79 $ 160.00 $ 320.25 $ 480.76 $ 641.52 $ 802.54 $ 963.81 S 1,125.33 $ 1.287.12 $ 1,449.15S 1.611.45 S 1.774.00 $ 1.936.81 $ 2,099.88 $ 2.263.20 $ 1,316.00 2.640.99 3,975.04 5,318.20 6,670.54 8,032.13 9,403.01 10,783.27 12,172.95 13,572.13 14,980.88 16.39925 17,827.31 19.265.13