Question

i need help with this problem ill post the whole question down below.

First photo is showing what needs to be answered

Second phote is what the correct answer is supposed to look like.

012 X for 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 $ $ $ 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

In the “Investment” worksheet, you will be calculating the monthly balance of a person’s 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 person’s 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

Answer 1

А с F G 50 Total Investment 50 B E 1 Estimated growth 5.90% 7.90% 1.90% 8.20% 2 Monthly contribution 250 160 1156 3 Max Balance 10000 40000 50000 NA 4 5 Date IRA 529 Savings Roth 401k 3/1/2020 0 0 4/1/2020 250 160 1156 5/1/2020 100.245833 501.645833 320.253333 2319.89933 6/1/2020 150.738709 754.948335 480.760401 3491.75198 7/1/2020 201.479841 1009.91841 641.521605 4671.61228 8/1/20201 252.47045| 1266.56704 802.537348 5859.53497 9/1/2020 303.711763 1524.90527 963.808032 7055.57512 10/1/2020 355.205012 1784.94423 1125.33406 8259.78822 11/1/2020 406.951437 2046.69512 1287.11584 9472.23011 12/1/2020 458.952282 2310.16919 1449.15377 10692.957 1/1/2021 511.208797 2575.37781 1611.44827 11922.0256 2/1/2021 563.72224 2842.33238 1773.99973 13159.4927 3/1/2021 616.493875 3111.0444 1936.80856 14405.4159 4/1/2021 669.52497 3381.52544 2099.87517 15659.8529 5/1/2021 722.816801 3653.78715 2263.19998 16922.8619 6/1/2021 776.37065 3927.84125 2426.78338 18194.5015 7/1/2021 830.187806 4203.69954 2590.62578 19474.8306 8/1/2021 884.269562 4481.37389 2754.72761 20763.9086 KTM Sheat1 Sheet2 Sheet3 / 1616 3242.044333 4878.199424 6524.532141 8181.109807 9848.000192 11525.27153 13212.9925 14911.23226 16620.06042 18339.54707 20069.76276 21810.77852 23562.66586 25325.49676 27099.34371 28884.27965

0.079 250 40000 0.019 160 50000 Saving ДА 1 Estimat 0.059 2 Monthl: 50 3 Max Bal 10000 4 5 Date IRA 6 43891 0 7 43922 FIF(B6>=B$3,B6+(B6*B$1/12),(B6+B$2)+(B6*B$1/12)) 8 43952 =IF(B7>=B$3,87+(B7*B$1/12),(B7+B$2)+(B7*B$1/12)) 9 43983 =IF(B8>=B$3,B8+(B8*B$1/12),(B8+B$2)+(B8*B$1/12)) 10 44013 EIF(B9>=B$3,69+(B9*B$1/12),(B9+B$2)+(89*B$1/12)) 11 44044 =IF(B10>=B$3,B10+(810*B$1/12),(B10+B$2)+(810*B$1/12)) 12 44075 =IF(B11>=B$3,B11+(B11*B$1/12),(B11+B$2)+(B11*B$1/12)) 13 44105 =IF(B12>=B$3,B12+(B12*B$1/12),(B12+B$2)+(B12*B$1/12)) 14 44136 =IF(B13>=B$3,B13+(B13*B$1/12),(B13+B$2)+(B13*B$1/12)) 15 44166 =IF(B14>=B$3,B14+(B14*B$1/12),(B14+B$2)+(B14*B$1/12)) 16 44197 =IF(B15>=B$3,B15+(B15*B$1/12),(B15+B$2)+(B15*B$1/12)) 17 44228 =IF(B16>=B$3,B16+(B16*B$1/12),(B16+B$2)+(B16*B$1/12)) 18 44256 =IF(B17>=B$3,B17+(B17*B$1/12),(B17+B$2)+(B17*B$1/12)) 19 44287 =IF(B18>=B$3,B18+(B18*B$1/12),(B18+B$2)+(B18*B$1/12)) 20 44317 =IF(819>=B$3,B19+(B19*B$1/12),(B19+B$2)+(B19*B$1/12)) 21 44348 =IF(B20>=B$3,B20+(B20*B$1/12),(B20+B$2)+(B20*B$1/12)) 22 44378 =IF(B21>=B$3,B21+(B21*B$1/12),(B21+B$2)+(B21*B$1/12)) 23 44409 =IF(B22>=B$3,B22+(B22*B$1/12),(B22+B$2)+(B22*B$1/12)) S heet1 Sheet2 Sheet3 529 lo =IF(B7>=B$3,IF(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+C$2)+(C6*C$1/12))) =IF(B8>=B$3,IF(C7>=C$3,C7+(C7*C$1/12),(B$2+C7+C$2)+(C7*C$1/12)),IF(C7>=C$3,C7+(C7*C$1/12),(C7+C$2)+(C7*C$1/12))) =IF(89>=B$3,IF(C8>=C$3,C8+(C8*C$1/12),(B$2+C8+$2)+(C8*C$1/12)),IF(C8>=C$3,C8+(C8*C$1/12),(C8+$2)+(C8*C$1/12))) =IF(B10>=B$3,IF(C9>=C$3,C9+(C9*C$1/12),(B$2+C9+$2)+(C9*C$1/12)),IF(C9>=C$3,C9+(C9"C$1/12),(C9+C$2)+(C9*C$1/12))) =IF(B11>=B$3,1F(C10>=C$3, C10+(C10*C$1/12),(B$2+C10+$2)+(C10*C$1/12)),IF(C10>=C$3,C10+(C10*C$1/12),(C10+C$2)+(C10*C$1/12))) =IF(B12>=B$3,IF(C11>=C$3,C11+(C11*C$1/12),(B$2+011+C$2)+(C11*C$1/12)),IF(C11>=C$3,C11+(C11*C$1/12),(C11+C$2)+(C11*C$1/12)) =IF(B13>=B$3,IF(C12>=C$3,012+(C12*C$1/12),(B$2+C12+C$2)+(C12*C$1/12)),IF(C12>=C$3,012+(C12*C$1/12),(C12+$2)+(C12*C$1/12))) =IF(B14>=B$3,IF(C13>=C$3,C13+C13*C$1/12),(B$2+C13+C$2)+(C13*C$1/12)),IF(C13>=C$3,C13+(C13*C$1/12),(C13+$2)+(C13*C$1/12))) =IF(B15>=B$3,IF(C14>=C$3,C14+C14*C$1/12),(B$2+C14+C$2)+(C14*C$1/12)),IF(C14>=C$3,C14+C14*C$1/12),(C14+C$2)+(C14*C$1/12)) =IF(B16>=B$3,IF(C15>=C$3,C15+C15*C$1/12),(B$2+C15+C$2)+(C15*C$1/12)),IF(C15>=C$3,C15+C15*C$1/12),(C15+C$2)+(C15*C$1/12))) =IF(B17>=B$3,1F(C16>=C$3,C16+(C16*C$1/12),(B$2+C16+C$2)+(C16*C$1/12)),IF(C16>=C$3,C16+(C16*C$1/12),(C16+$2)+(C16*C$1/12))) =IF(B18>=B$3,IF(C17>=C$3,C17+(C17*C$1/12),(B$2+C17+C$2)+(C17*C$1/12)),IF(C17>=C$3,C17+(C17*C$1/12),(C17+C$2)+(C17*C$1/12)) =IF(B19>=B$3,IF(C18>=C$3,C18+(C18*C$1/12),(B$2+C18+C$2)+(C18*C$1/12)),IF(C18>=C$3,C18+(C18*C$1/12),(C18+C$2)+(C18*C$1/12)) =IF(B20>=B$3,IF(C19>=C$3,C19+(C19*C$1/12),(B$2+C19+C$2)+(C19*C$1/12)),IF(C19>=C$3,019+(C19*C$1/12),(C19+$2)+(C19*C$1/12))) =IF(B21>=B$3,IF(C20>=C$3,C20+(C20*C$1/12),(B$2+C20+C$2)+(C20*C$1/12)),IF(C20>=C$3,C20+(C20*C$1/12),(C20+$2)+(C20*C$1/12))) =IF(B22>=B$3,1F(C21>=C$3,C21+(C21*C$1/12),(B$2+C21+$2)+(C21*C$1/12)),IF(C21>=C$3,C21+(C21*C$1/12),(C21+$2)+(C21*C$1/12))) =IF(B23>=B$3,1F(C22>=C$3,C22+(C22*C$1/12),(B$2+C22+C$2)+(C22*C$1/12)),IF(C22>=C$3,022+(C22*C$1/12), (C22+$2)+(C22*c$1/12))) =IF(C7 =IF(C8 =IF(C9 =IF(C1 =IF(C1 =IF(C1 =IF(C1 =IF(C1 =IF(C1 =IF(C1 =IF(C1 =IF(C1 =IF(C1 =IF(C2 =IF(C2 =IF(C2 =IF(C2 H

160 0.082 1156 NA Roth 401k 1 Estimated grc 0.059 0.079 0.019 2 Monthly contr 50 250 3 Max Balance 10000 40000 50000 4 5 Date IRA 529 Savings 6 43891 7 43922 =IF(B6>=B$3,B6+=IF(B7>=B$3,=IF(C7>=C$3,IF(D6>=D$3,D6+(D6*D$1/12),(C$2+D6+D$2)+(D6*D$1/12)),IF(D6>=D$3,D6+(D6*D$1/12),(D6+D$2)+(D6*D$1/12))) 8 43952 =IF(B7>=B$3,B7=IF(B8>=B$3,=IF(C8>=C$3,IF(D7>=D$3,D7+(D7*D$1/12),(C$2+D7+D$2)+(D7*D$1/12)),IF(D7>=D$3,D7+(D7*D$1/12),(D7+D$2)+(D7*D$1/12))) 9 43983 =IF(B8>=B$3,B8+=IF(B9>=B$3,=IF(C9>=C$3,IF(D8>=D$3,D8+(D8*D$1/12),(C$2+D8+D$2)+(D8*D$1/12)),IF(D8>=D$3,D8+(D8*D$1/12),(D8+D$2)+(D8*D$1/12))) 10 44013 =IF(B9>=B$3,69+=IF(B10>=B$ =IF(C10>=C$3,IF(D9>=D$3,D9+D9*D$1/12).(C$2+D9+D$2)+(D9*D$1/12)), IF(D9>=D$3,D9+D9*D$1/12).(D9+D$2)+(D9*D$1/12) 11 44044 =IF(B10>=B$3,B1 =IF(B11>=B5 =IF(C11>=C$3,IF(D10>=D$3,D10+(D10*D$1/12).(C$2+D10+D$2)+(D10*D$1/12), IF (D10>=D$3,D10+(D10*D$1/12).(D10+D$2)+(D10*D$1/12) 12 44075 =IF(B11>=B$3,B1 =IF(B12>=B$i =IF(C12>=C$3,IF(D11>=D$3,D11+(D11D$1/12),(C$2+011+D$2)+(D11D$1/12)),IF(D11>=D$3,D11+(D11*D$1/12),(D11+D$2)+(D11-D$1/12)}) 13 44105 =IF(B12>=B$3,B1 =IF(B13>=B$: =IF(C13>=C$3,IF(D12>=D$3,D12+(D12*D$1/12),(C$2+D12+D$2)+(D12*D$1/12)),IF(D12>=D$3,D12+(D12D$1/12),(D12-D$2)+(D12 D$1/12))) 14 44136 =IF(B13>=B$3,B1 =IF(B14>=B$: =IF(C14>=C$3,IF(D13>=D$3,D13+(D13*D$1/12),(C$2+D13+D$2)+(D13 D$1/12)),IF(D13>=D$3,D13+(D13D$1/12),(D13+D$2)+(D13 D$1/12))) 15 44166 =IF(B14>=B$3,B1 =IF(B15)=B$i =IF(C15>=C$3,IF(D14>=D$3,D14+(D14 D$1/12),(C$2+D14+D$2)+(D14 D$1/12)),IF(D14>=D$3,D14+(D14*D$1/12),(D14+D$2)+(D14*D$1/12)}) 16 44197 =IF(B15>=B$3,B1 =IF(B16>=B$: =IF(C16>=C$3,IF(D15>=D$3,D15+(D15*D$1/12),(C$2+D15+D$2)+(D15*D$1/12)),IF(D15>=D$3,D15+(D15*D$1/12),(D15+D$2)+(D15*D$1/12))) 17 44228 =IF(B16>=B$3,B1 =IF(B17>=B$i =IF(C17>=C$3,IF(D16>=D$3,D16+(D16*D$1/12),(C$2+D16+D$2)+(D16*D$1/12)),IF(D16>=D$3,D16+(D16*D$1/12),(D16+D$2)+(D16*D$1/12)}) 18 44256 =IF(B17>=B$3,B1 =IF(B18>=B$: =IF(C18>=C$3,IF(D17>=D$3,D17+(D17*D$1/12),(C$2+D17+D$2)+(D17*D$1/12)),IF(D17>=D$3,D17+(D17*D$1/12),(D17+D$2)+(D17*D$1/12))) 19 44287 =IF(B18>=B$3,B1 =IF(B19>=B$: =IF(C19>=C$3,IF(D18>=D$3,D18+(D18*D$1/12),(C$2+D18+D$2)+(D18*D$1/12)),IF(D18>=D$3,D18+(D18*D$1/12),(D18+D$2)+(D18*D$1/12))) 20 44317 =IF(B19>=B$3,B1 =IF(B20>=B$: =IF(C20>=C$3,IF(D19>=D$3,D19+(D19 D$1/12),(C$2+D19+D$2)+(D19*D$1/12)),IF(D19>=D$3,D19+(D19D$1/12),(D19+D$2)+(D19 D$1/12))) 21 44348 =IF(B20>=B$3,B2 =IF(B21>=B$: =IF(C21>=C$3,IF(D20>=D$3,D20+(D20*D$1/12),(C$2+D20+D$2)+(D20*D$1/12)),IF(D20>=D$3,D20+(D206D$1/12),(D20+D$2)+(D20*D$1/12))) 22 44378 =IF(B21>=B$3,B2 =IF(B22>=B$i =IF(C22>=C$3,IF(D21>=D$3,D21+(D21-D$1/12),(C$2+D21+D$2)+(D21-D$1/12)),IF(D21>=D$3,D21+(D21*D$1/12),(D21+D$2)+(D21*D$1/12))) 23 44409 =IF(B22>=B$3,B2 =IF(B23>=B$i =IF(C23>=C$3,IF(D22>=D$3,D22(D22D$1/12),(C$2+D22+D$2)+(D22D$1/12)),IF(D22>=D$3,D22+(D22*D$1/12),(D22+D$2)+(D22*D$1/12))) 24 44440 =IF(B23>=B$3,B2 =IF(B24>=B$i =IF(C24>=C$3,IF(D23>=D$3,D23+(D23*D$1/12),(C$2+D23+D$2)+(D23D$1/12)),IF(D23>=D$3,D23+(D23*D$1/12),(D23+D$2)+(D23*D$1/12))) 25 44470 =IF(B24>=B$3,B2 =IF(B25)=B$i =IF(C25>=C$3,IF(D24>=D$3,D24+(D24.D$1/12),(C$2+D24+D$2)+(D24*D$1/12)),IF(D24>=D$3,D24+(D24.D$1/12),(D24+D$2)+(D24.D$1/12))) 26 44501 =IF(B25)=B$3,B2 =IF(B26>=B$i =IF(C26>=C$3,IF(D25>=D$3,D25+(D25*D$1/12),(C$2+D25+D$2)+(D25*D$1/12)),IF(D25>=D$3,D25+(D25*D$1/12),(D25+D$2)+(D25*D$1/12))) 27 44531 =IF(B26>=B$3,B2 =IF(B27>=B$i =IF(C27>=C$3,IF(D26>=D$3,D26+(D26*D$1/12),(C$2+D26+D$2)+(D26*D$1/12)),IF(D26>=D$3,D26+(D26*D$1/12),(D26+D$2)+(D26*D$1/12))) 28 44562 =IF(B27>=B$3,B2 =IF(B28>=B$i =IF(C28>=C$3,IF(D27>=D$3,027+(D27*D$1/12),(C$2+D27+D$2)+(D27*D$1/12)),IF(D27>=D$3,D27+(D27*D$1/12),(D27+D$2)+(D27*D$1/12))) 29 44593 =IF(B28>=B$3,B2 =IF(B29>=B$i =IF(C29>=C$3,IF(D28>=D$3,D28+(D28D$1/12),(C$2+D28+D$2)+(D28*D$1/12)),IF(D28>=D$3,D28+(D28*D$1/12),(D28+D$2)+(D28*D$1/12))) =IF(D7>=D$3,(E6+D$2+E$2)+(EC =IF(D8>=D$3,(E7+D$2+$2)+(E7 =IF(D9>=D$3,(E8+D$2+$2)+(ES =IF(D10>=D$3,(E9+D$2+$2)+ =IF(D11>=D$3,(E10+D$2 E$2) =IF(D12>=D$3,(E11+D$2 E$2) =IF(D13>=D$3,(E12+D$2 E$2) =IF(D14>=D$3,(E13+D$2 E$2) =IF(D15>=D$3,(E14+D$2 E$2) =IF(D16>=D$3,(E15+D$2 E$2) =IF(D17>=D$3,(E16+D$2 E$2) =IF (D18>=D$3,(E17+D$2 E$2) =IF (D19>=D$3,(E18+D$2 E$2) =IF(D20>=D$3,(E19+D$2+E$2)+ =IF(D21>=D$3,(E20+D$2+E$2)+ =IF(D22>=D$3,(E21+D$2 E$2)+ =IF(D23>=D$3,(E22+D$2 E$2)+ =IF(D24>=D$3,(E23+D$2 E$2)+ =IF (D25>=D$3,(E24+D$2 E$2)+ =IF(D26>=D$3,(E25+D$2 E$2)+ =IF(D27>=D$3,(E26+D$2+$2)+ =IF(D28>=D$3,(E27+D$2 E$2)+ =IF(D29>=D$3,(E28+D$2 E$2)+