i need help with formula for E

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

its supposwd to look like the first photo i had it already answered however for E it was cut off

forget this question i asked it again with better phrased.

need formula for 401k

1 Estimated growth 2 Monthly contribution 3 Max Balance 1156 350 SODO NA 5 Date Total investment 132 529 San Roth OER 3/1/2020 4/1/2020 W1/2020 100 3 501 102 6/1/2020 150.738709 753 750391.75198 2/12920201491009 50505 0671 61223 //2020 252.47045 1256 56704 302 53734 S85953497 9/1/2020 3211534627 2 0555512 30/1/2020 355. 2021754 1235 33406 3959.322 11/1/202006951437 205.2 011 2011 65351261 1/1/2021 S120 2573 372 1 27 1122 2/1/201563.72234 250 235177395731359.4323 1/2021636.493675 311 1936 BORSE AS.419 4/1/2016657155291517 1565529 5/1/2021 722 616013653.7875 226319998 16922 8529 6/1/2021726370651927.4106 1 115015 2/1/2021 830.107306 4203.094 209578 194748306 8/1/2021 2095213739 275472751 207538 1155275 13212935 1412220 165200002 16339.54707 2006 2010 77852 2626586 25125.49676 2709 288427965 Fuled Growth Monthly Contributi Max Race 7.90 $50.00 40.000. 00 $50.00 1000000 1.90% $16000 .000 0 $1.156.00 S $ 0 N/A Date Savings Roth 401 Total Investment 1/1/2020 S 4/1/2020 $ 5/1/2020 S 6/1/2020 S 2/1/2020 S 1/1/2020 $ 9/1/2020 S 10/1/2020 S 11/1/2020 S 12/1/2020 $ 1/1/2021 S 2/1/20215 1/1/2021 S 4/1/2021 5/1/2021 S 6/1/20215 7/1/20215 1/1/2021 9/1/2021 S 10/1/2021 S 11/1/2021 12/1/20215 50.00 $ 100.25 $ 150.74 $ 2014 252.47 $ 301.71 $ 255.21 406.95 $ 458.95 $ $11.21 567.72 $ 616.49 $ 2 $ 722.82 $ 7737 810 19 $ 634.27 $ 938.625 93.215 1 012 250.00 $ 501.65 $ 754.95 $ 1,009.) 1,266.57 $ 1.524.91 $ 1,714.94 $ 2,046.20 2,310.175 2.595.385 2,1423) 1,111.04 $ 1,181.535 1.651.70 S 1,027.345 4,201.05 4,481.375 4,780.8 042225 5,125.41 5,610.47 % 16000$ 32055 480.76 S 64155 309 545 96381 S 1,1251) S 1,267.12 S 1.440.15 S 1,611 455 1,77400S 1,9181 2,099 S 2.263.205 2,436.78 2.590.6) 2.754.7) 2010. 05 3,083715 3.240 995 3.41 $ 1196.00 2.31999 2.401.75 4.671.61 5.559 53 7.055 50 259.79 9472 23 10.699.95 11,92203 11.199 49 14 405 42 15.65985 16.922 85 11 19.474.81 20,75191 22.0519 21.168.55 24.63 26.000 1 616.00 2.242.04 4878,20 6.524 52 101.11 9648,00 11.525, 27 12.212.99 14 911.23 16 620.06 16.339.55 20069,76 21 810.78 23562.67 25.325.50 27.1.4 28 04.28 1000 32.487.71 ML106.36 16 116.39 spent situation not maxed out and no extra panyes situation maxed out and no extra paymenino situation not maxed out and extra paymer yes situation maxed out and extra payment no yes Estimated Growth Monthly Contributi Max Balance 5.90% $50.00 10,000.00 7.90% $250.00 40,000.00 1.906 $160.00 50.000,00 N/A 8.20% $1.156.00 $ $ $ Date IRA 529 Savings Roth 401k Total Investment $ 1.616.00 3,242.04 4,878.20 652053 3/1/2020 $ 4/1/2020 $ 5/1/2020 $ 6/1/2020 $ 7/1/2020 $ 8/1/2020 $ 9/1/2020 $ 10/1/2020 s 11/1/2020 S 12/1/2020 S 1/1/2021 S 2/1/2021 $ 3/1/2021 $ 4/1/2021 $ 5/1/2021 $ 6/1/2021 $ 7/1/2021 S 8/1/2021 9/1/2021 S 10/1/2021 $ 11/1/2021 $ 12/1/2021 $ 50.00 $ 100.25 $ 150.74 $ 201.48 $ 252.47 $ 303.71 $ 355.21 $ 406.95 $ 45.95 $ $11.21 $ 563.72 $ 616.49 $ 6 .52 $ 722.82 770.375 810.19 $ 84.27 $ 938.62 $ 993.23 $ 1,048.12 $ 1.103.27 $ 250.00 $ 501.65 $ 754.95 S 1,009.92 $ 1,266.57 $ 1,524.91 $ 1,784.94 $ 2.046,20 S 2.310.17 S 2,575.38 2,842,33 $ 111.04 $ 3.181.53 $ 3,653.79 $ 3,927.84 S 4,203.20 $ 4,481.32 $ 4,700.88 $ 5,042.22 $ 5,325.41 S 5,610.47 $ 160.00 $ 320.25 $ 490.76 $ 641.52 S 802.54 $ 963.81 $ 1,125.33 S 1.287.12 S 1.449.15S 1,611.45 $ 1,774.00 $ 1.936.81 S 2,099.88 $ 2.263.20 $ 2,426.78 2.590.63 $ 2,754,73 $ 2,919.09 3,083.715 3,248.59 $ 3,413.74 S 1.156,00 2,319.90 3.4917 4,671.61 5,859.53 7,055.58 8,259.79 9472 23 10.092.96 11.922.03 13.159.49 14.405.42 15,659.85 16.922.86 18.11.50 194.83 20,263.91 22,061.80 23.368.55 24,61 26,008 913 8,181.11 9,848.00 11.525.27 13,212.99 14.911.23 16.620.00 10.339.55 20.069,76 21,810,78 23.562.67 25, 125.50 27,099.34 28.84.28 30.680.38 32,487,71 34,300.36 36, 136.39 pmtxpmt situation not maxed out and no extra payr yes no situation maxed out and no extra paymenino no situation not maxed out and extra paymer yes yes situation maxed out and extra payment no yes 012 X 8.20% 1 Estimated Growth 2 Monthly Contributi 3 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 $1,156,00 St $ $ $ 5 Date Savings Roth 401k Total Investment 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 Estimated Growth Monthly Contributi Max Balance 5.90% $50.00 7.90% $250.00 40,000.00 0. 1.90% $160.00 50,000.00 N/A 8.20% $1,156.00 $ 10,000.00 $ $ Date IRA 529 Savings Roth 401k Total Investme 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 S 2/1/2021 S 3/1/2021 $ 4/1/2021 5/1/2021 $ uc 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 $250.00 $ $501.65 $ $754.95 $ $1,009.92 $ $1,266.57 $ $1,524.91 S $1,784.94 $ $2,046.70 S $2,310.17 $ $2,575.38 S $2,842.33 $ $3,111.04 $ $3,381.53 S $3,653.79 $ 42027.04 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 Garano 1 Estimated growth 2 Monthly contribution 3 Max Balance 1156 350 SODO NA 5 Date Total investment 132 529 San Roth OER 3/1/2020 4/1/2020 W1/2020 100 3 501 102 6/1/2020 150.738709 753 750391.75198 2/12920201491009 50505 0671 61223 //2020 252.47045 1256 56704 302 53734 S85953497 9/1/2020 3211534627 2 0555512 30/1/2020 355. 2021754 1235 33406 3959.322 11/1/202006951437 205.2 011 2011 65351261 1/1/2021 S120 2573 372 1 27 1122 2/1/201563.72234 250 235177395731359.4323 1/2021636.493675 311 1936 BORSE AS.419 4/1/2016657155291517 1565529 5/1/2021 722 616013653.7875 226319998 16922 8529 6/1/2021726370651927.4106 1 115015 2/1/2021 830.107306 4203.094 209578 194748306 8/1/2021 2095213739 275472751 207538 1155275 13212935 1412220 165200002 16339.54707 2006 2010 77852 2626586 25125.49676 2709 288427965 Fuled Growth Monthly Contributi Max Race 7.90 $50.00 40.000. 00 $50.00 1000000 1.90% $16000 .000 0 $1.156.00 S $ 0 N/A Date Savings Roth 401 Total Investment 1/1/2020 S 4/1/2020 $ 5/1/2020 S 6/1/2020 S 2/1/2020 S 1/1/2020 $ 9/1/2020 S 10/1/2020 S 11/1/2020 S 12/1/2020 $ 1/1/2021 S 2/1/20215 1/1/2021 S 4/1/2021 5/1/2021 S 6/1/20215 7/1/20215 1/1/2021 9/1/2021 S 10/1/2021 S 11/1/2021 12/1/20215 50.00 $ 100.25 $ 150.74 $ 2014 252.47 $ 301.71 $ 255.21 406.95 $ 458.95 $ $11.21 567.72 $ 616.49 $ 2 $ 722.82 $ 7737 810 19 $ 634.27 $ 938.625 93.215 1 012 250.00 $ 501.65 $ 754.95 $ 1,009.) 1,266.57 $ 1.524.91 $ 1,714.94 $ 2,046.20 2,310.175 2.595.385 2,1423) 1,111.04 $ 1,181.535 1.651.70 S 1,027.345 4,201.05 4,481.375 4,780.8 042225 5,125.41 5,610.47 % 16000$ 32055 480.76 S 64155 309 545 96381 S 1,1251) S 1,267.12 S 1.440.15 S 1,611 455 1,77400S 1,9181 2,099 S 2.263.205 2,436.78 2.590.6) 2.754.7) 2010. 05 3,083715 3.240 995 3.41 $ 1196.00 2.31999 2.401.75 4.671.61 5.559 53 7.055 50 259.79 9472 23 10.699.95 11,92203 11.199 49 14 405 42 15.65985 16.922 85 11 19.474.81 20,75191 22.0519 21.168.55 24.63 26.000 1 616.00 2.242.04 4878,20 6.524 52 101.11 9648,00 11.525, 27 12.212.99 14 911.23 16 620.06 16.339.55 20069,76 21 810.78 23562.67 25.325.50 27.1.4 28 04.28 1000 32.487.71 ML106.36 16 116.39 spent situation not maxed out and no extra panyes situation maxed out and no extra paymenino situation not maxed out and extra paymer yes situation maxed out and extra payment no yes Estimated Growth Monthly Contributi Max Balance 5.90% $50.00 10,000.00 7.90% $250.00 40,000.00 1.906 $160.00 50.000,00 N/A 8.20% $1.156.00 $ $ $ Date IRA 529 Savings Roth 401k Total Investment $ 1.616.00 3,242.04 4,878.20 652053 3/1/2020 $ 4/1/2020 $ 5/1/2020 $ 6/1/2020 $ 7/1/2020 $ 8/1/2020 $ 9/1/2020 $ 10/1/2020 s 11/1/2020 S 12/1/2020 S 1/1/2021 S 2/1/2021 $ 3/1/2021 $ 4/1/2021 $ 5/1/2021 $ 6/1/2021 $ 7/1/2021 S 8/1/2021 9/1/2021 S 10/1/2021 $ 11/1/2021 $ 12/1/2021 $ 50.00 $ 100.25 $ 150.74 $ 201.48 $ 252.47 $ 303.71 $ 355.21 $ 406.95 $ 45.95 $ $11.21 $ 563.72 $ 616.49 $ 6 .52 $ 722.82 770.375 810.19 $ 84.27 $ 938.62 $ 993.23 $ 1,048.12 $ 1.103.27 $ 250.00 $ 501.65 $ 754.95 S 1,009.92 $ 1,266.57 $ 1,524.91 $ 1,784.94 $ 2.046,20 S 2.310.17 S 2,575.38 2,842,33 $ 111.04 $ 3.181.53 $ 3,653.79 $ 3,927.84 S 4,203.20 $ 4,481.32 $ 4,700.88 $ 5,042.22 $ 5,325.41 S 5,610.47 $ 160.00 $ 320.25 $ 490.76 $ 641.52 S 802.54 $ 963.81 $ 1,125.33 S 1.287.12 S 1.449.15S 1,611.45 $ 1,774.00 $ 1.936.81 S 2,099.88 $ 2.263.20 $ 2,426.78 2.590.63 $ 2,754,73 $ 2,919.09 3,083.715 3,248.59 $ 3,413.74 S 1.156,00 2,319.90 3.4917 4,671.61 5,859.53 7,055.58 8,259.79 9472 23 10.092.96 11.922.03 13.159.49 14.405.42 15,659.85 16.922.86 18.11.50 194.83 20,263.91 22,061.80 23.368.55 24,61 26,008 913 8,181.11 9,848.00 11.525.27 13,212.99 14.911.23 16.620.00 10.339.55 20.069,76 21,810,78 23.562.67 25, 125.50 27,099.34 28.84.28 30.680.38 32,487,71 34,300.36 36, 136.39 pmtxpmt situation not maxed out and no extra payr yes no situation maxed out and no extra paymenino no situation not maxed out and extra paymer yes yes situation maxed out and extra payment no yes 012 X 8.20% 1 Estimated Growth 2 Monthly Contributi 3 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 $1,156,00 St $ $ $ 5 Date Savings Roth 401k Total Investment 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 Estimated Growth Monthly Contributi Max Balance 5.90% $50.00 7.90% $250.00 40,000.00 0. 1.90% $160.00 50,000.00 N/A 8.20% $1,156.00 $ 10,000.00 $ $ Date IRA 529 Savings Roth 401k Total Investme 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 S 2/1/2021 S 3/1/2021 $ 4/1/2021 5/1/2021 $ uc 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 $250.00 $ $501.65 $ $754.95 $ $1,009.92 $ $1,266.57 $ $1,524.91 S $1,784.94 $ $2,046.70 S $2,310.17 $ $2,575.38 S $2,842.33 $ $3,111.04 $ $3,381.53 S $3,653.79 $ 42027.04 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 Garano