Question

1 Approved Answer

Posted on Sep 07, 2024

Ms. Janet Yellen is a prospective client of your financial planning firm. She has reached out to you for help regarding her retirement savings. Based

Ms. Janet Yellen is a prospective client of your financial planning firm. She has reached out to you for help regarding her retirement savings. Based on the 3 tasks completed in your homework 1, write a memo to Ms. Yellen explaining the following:

- Key findings from your analysis of the S&P 500 historical performance. In comparing the average returns across the 3 different time periods, offer your insights on potential reasons of why the market performed differently across the 3 time periods.

-The basic concept behind your savings spreadsheet. Essentially, you want to effectively communicate to Ms. Yellen what you have built in the spreadsheet in plain English

-Findings from your sensitivity analysis. Since a picture speaks a thousand words, in your discussion, be sure to provide Ms. Yellen with graphs and charts to help her visualize and in turns, strengthen the effectiveness of your written communication.

I attach the number I did on the excel,

I need help how to write the memo for her.

I focus on 10 years , 20 years and 30 years and the company I did S&P 500 ( base on historical data)

5000 base on the question..

6000 because she is under 50 years old , so she can max out her contribution IRA to 6000 according to IRS

image text in transcribed

Hai Tran-hamork 1 FIN 402 (1) Protected ww - Excel Product Activation Failed) Tell me what you want to de File Hame In Page Layout Formule Resim Vir Por Prect Hai Tur 9 Share PROTECTED VIFW Perales from the Intemeran contain Virus. In you need to edit, Pecater to stay in Protected View Fahle Fiting HI 1 0 P R 5 w X Y G 1 M N 2 According IRS.GOV" the contributions you make each year to all of your traditional IRA and Roth IRAs can't be more than $6000 $7000 if you're age 50 or older, or if less, your table compensation for the year).' Assume that my customer is under 50 years old, so she can max out the contribution $6,000 to her IRA If my customer contribute $5,000 for the next 30 year with the intercts rate of 9.14% per year, she will earn an average annuliced return: 5829,353.76. 1 Year Amount contribute enth year begin Received Interest End Received 2 11 $ 6,000.00 9.14% $ 6.000.00 3 2 s 6,000.00 $ 6,000.00 S 540.34 S 12.548.34 4 35 6,000.00 S 12,548.34 $ 1,146.79 S 19,695.13 S 6,000.00 $ 19,695.13 $ 1,799.94 $ 27,495.07 5 $ 6,000.00 $ 27,495.07 $ 2.512.77 $ 36,007.85 7 6 $ 6,000.00 S 36,007.85 S 3,220.70 S 45.238.60 8 7 $ 6,000.00 $ 45,292.60 $ 4,139.84 $ 55,438.44 8 $ 6,000.00 $ 55,438.44 $ 5,056.52 $ 66.504.96 10 9 $ 6,000.00S 66,504.30 S 6,077.89 S 78,582.05 11 10 $ 6,000.00 $ 73,582.85$ 7,131.69 $ 91,754.54 $ 12 11 $ 6,000.00 $ 91,764.34 $ 8,396.36 $206,150.90 13 12 S 6,000.00 S 106,150.30 S 9.20.13 121,852.03 14 13 $ 6,000.00 $ 131,852.03 $11,136.06 $138.988.00 15 14 $ 6,000.00 $ 128,983.08 $ 12.702.12 $157,080.21 16 15$ $ 6,000.00 S 157,690.21 $ 19,411.31 $178,101.51 17 16 S 6,000.00 $ 178,101.51 $ 16,276.70 5200.378.21 18 17 S 6,000.00 $ 200,378.21 $18,312.56 $224.090.77 79 10 S 6,000.00 S 224,6%0.77 $ 20.534.49 $251.225.26 19 $ 6,000.00 $ 251.225.76 $ 22.959.48 5220.184.74 20 $ 6,000.00 $ 250,184.74 $ 25,000.08 $211.790.83 21 $ 6,000.00 S 311,783 $ 28,424.56 $146.285.39 23 22 $ 6,000.00 $ 146,25.19 $ 11,647.02 $183.992.41 23 $ 6,000.00 S 383,932.41 $35,087.58 $425,019.99 29 $ 6,000.00 S 425,019.99 | $30,842.589469,852.57 25 $ 6,000.00 $ 464,862-7$ 42,940.74 $518.803.31 20 $ 6,000.00 $ 518,802.11 $ 47,413.43 $372.226.75 28 27 S 6,000.00 S 572,215/5 $ 52.244.899630,511.63 28 $ 6,000.00 $ 630,511.63 $57,623.46 5694.134.09 30 29 $ 6,000.00 $ $ 694,134.09 $ 63,436.91 $763,571.01 31 30 $ 6,000.00 S 763.571.01 $69.782.75 $39.353.75 14 35 39 40 Task 1 Task 2 Task 3-1 Task 2 Task 3-3 + 100% S N A 10:04 PM 1/12 S2 Hiniran-homework' EN 421 Protected Virul - Face Product Activation Failed Tull me what you want to de.. File Hame Inert Page Layou Formule Data Roir Vin Power Pivot Hei Tre Share O PROTECTED VIFW Re rules from the Intemeran contain Virus. In you need to edit, Pecater to stay in Protected View Frahlefciting IN 14 1 K K 1 M N P 2 R S T 11 V w X Y $143.83747.93 F F G H H 1090 W/1/2018 2821.17 2916.5 276.36 2750.34 2900.52 2901.52 0235220000 3.00% 1001 9/1/2018 2896.96 M0.91 2854.12 2913.98 2913.98 62497080000 0.43% 1092 10/1/2018 2920.29 2929.00 2003.54 2711.74 271174 91327930000 0.94% 1093 11/1/2018 2717.55 2515.15 2531,0922017 2750,12 2760.17 80080110000 1094 13/1/2018 2793.5 2800.12 2346.58 2506.AS 2505.99 1523520000 -9.18% 1095 1/1/2019 2470.96 2700.99 2443.90 27041 2704.1 30401030000 7.87% 7095 2/1/2019 2702.32 2513.49 2254,49 2784.49 70183430000 1007 2/1/2019 7790.>> 2860.31 2732.27 2834.4 2034.4 78596280000 1.79% 1099 4/1/2019 2548.63 2949.52 2840.63 2943.83 2945.83 USG04940000 21.92% 1099 5/1/2019 2352.33 2050.13 2750.52 2752.06 2752.06 76360120000 -6.50% 11006/1/2019 2751.53 2964,15 2778.81 2941.76 2941.76 70881390000 5.99% 1101 7/1/2019 2971.41 3027.93 2952.22 2980.38 2980.38 70349470000 1315 1102 8/1/2019 2800.82 313.59 2822.12 2926.46 2926.46 295949000 -1.81% 1103 9/1/2019 2909.01 2001.99 2891.85 2978,74 2976.74 73997330000 1.72% 1104 10/1/2019 283.09 2050.1 2555.94 3037.56 3037.56 77564550000 2.04% 1105 11/1/2019 50.72 3154.25 -0.22 3140.90 3140.90 721 720000 3.40% 1105 13/1/2019 3143.RS 3247.93 3070.33 3230.71 3230.78 72054000000 2.86% 1107 1/1/2020 3244.07 3237.77 3214.64 3225.52 3223.32 77104420000 0.16% 1100 2/1/2020 3235.66 3393.52 2855.04 2954.22 2054.22 812922 000 -3.41% 1100 1/1/2020 2974.22 $136.72 2191.86 2191.86 2584.59 25B4.59 1.61801E+11 -12.51% 1110 4/1/2020 2498.00 2904.80 2447.49 2912.43 2912.43 1.23103E-11 12.68% 1111 5/1/2020 2809.09 2060.67 2766.64 3044.31 3044.31 1.06799E+11 4.53% 1112 6/1/2020 308.78 B23.13 2965.66 3100,29 3100.29 1.31044E+11 1.84% 1118 7/1/2020 3105.92 3279.99 3101.17 3271.12 3271.12 97197020000 3.31% 1114 8/1/2020 3280.25 3514.77 3284.53 3500.31 3500.31 84902300000 7.01% 1115 9/1/2020 3507.44 258.11 3204.45 3363 3363 92014120000 -3.925 1110 10/1/2020 B185.87 3549.85 323.94 3209.90 3269.96 59737000000 277% 1117 11/1/2020 32052 3545.99 3229.24362) 3621.03 3621.63 1.00700011 10% 1118 13/1/2020 3545.87 3760.2 3633.4 3756.07 3756.07 96055410000 3.71% 1119 1/1/20213704.01 2301.45 3002.71 3062.71 3841.47 3841.47 69133270000 2.27% 1120 Monthly Arithmetic Average 1121 30 years 0.76 1122 20 years 0.53% 1123 10 years 1124 1125 1126 1127 1120 1129 Task #1 Task 2 Task 3-1 Task 3-2 Task 3-3 Ready + 100% 102 PM 1/10/2001 N Hwan-homework' EN42: Protected Viru] - Focal Product Activation failed Tell me what you want to de File Hame Page Layout Formule Data Resim Vin Power Pivot Hei Tre Share PROTECTED VIFW Perales from the Intemeran contain Virus. In you need to edit, Pecater to stay in Protected View Frehlefciting H 1 1 1 M N 0 R 5 w 7 3 4 A 1 Year Amount contribute esth year legini Received Interest End Received 2 11 $ 6,000.00 11.90% $ 6.000.00 2 s 6,000.00 $ 6,000.00 $ 714.06 $ 12.714.00 35 6,000.00 $ 12,714.00 1.513.10 S 20.227.16 S 4 $ 6,000.00 $ 20,227.16 $ 2,407.23 $ 28,634.39 5 $ 6,000.00 $ 28,634.29 $ 3.407.78 $ 38,042.17 7 6 $ 6,000.00 S 38,042-174,527.00 48.569.57 7 $ 6,000.00 $ 48,569.57 $ 5,780.26 $ 60,349.84 9 8 $ 6,000.00 $ 60,349.84 $ 7,182.23 $ 73,522.07 10 6,000.00 S 79,532,07 S 8,751,05 S 38.283.12 10 $ 6,000.00 $ 29,283.12$ 10,506.57 $104.789.70 12 If my customer contribute $5,000 for the next 10 year with the interets rate of 11.90% per year, she will earn an average annusliced return $104,789.70. 11 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 20 20 30 31 33 14 35 37 39 40 Task 1 Task 2 Task 3-1 Task #3-2 Task 3-3 E + 100% 10:13 PM 1/10/2011 11 N Hai Tran-homework'FN 4221 Protected Virul - Feel Pacurt Activation Failed Tell me what you want to de File Hame Page Layout Formule Resim Vir Por Prect Hei Tre Share PROTECTED VIFW Perales from the Intemeran contain Virus. In you need to edit, Pecater to stay in Protected View Frahleciting H35 G H 1 1 N 0 R 5 w 7 If my customer contribute $6,000, for the next 20 year with the interets Isle of 6.33% per year, she willen an average annualized return $278.717.83. A F 1 Year Amount contribute enth year begin Received Interest End Received 2 11 $ 6,000.00 6.33% $ 6.000.00 3 2 s 6,000.00 S 0,000.00 $ 379.83 $ 12.379.83 4 35 6,000.00 S 12,379.89 S789.71 $ 19,169.54 S 45 6,000.00 $ 19,163.54 $ 1,213.15$ 26,376.68 5 $ 6,000.00 $ 26,376.68 $ 1.609.78 $ 34,046.46 7 6 $ 6,000.00 S 34,045.40 2.155.31 S 42.201.77 8 7 $ 6,000.00 $ 42,201.77 $ 2,671.58 $ 50.873.35 8 $ 6,000.00 $ 50,873.35 $ 3.220.54 $ 60,093.89 10 9 $ 6,000.00 S 60,093.89 $ 3,804.29 S 69,898.13 11 10 $ 6,000.00 $ 69,892.13 $ 4,424.90 $ 80,323.04 12 11 $ 6,000.00 $ 30,323.04 $ 5,054.85 $ 91,407.89 13 12 s 6,000.00 S 91,407.89 5.786.58 103.194.46 14 13 $ 6,000.00 $ 109,114.46 $ 6.537.73 $115.727.19 15 6,000.00 $ 115,727.19 S 2.120.11 $129,033.30 16 15$ 6,000.00 S 129,053 30 S 8.109.72 $143.223.02 17 16 $ 6,000.00 $ 143,223.02 $ 9,056.73 $158.249.75 18 17 $ 6,000.00 $ 158,289.73 $ 10,020.53 $274,310.28 19 10 $ 6,000.00 S 174,310 28 $ 11,034.71 $191,24.99 20 19 $ 6,000.00 $ 191,344.99 $ 12,113.09 $209,458.09 21 20 $ 6,000.00 $ 209,458.09 $ 13.259.74 $228.717.83 23 25 26 27 20 20 30 31 33 14 35 II 32 39 29 40 Task 1 Task 2 Task 3-1 Task 3-2 Task #3-3 + Ready E + 100% 10:13 PM 1/10/2011 11 NE x 1 Hai Tran-hamork 1 FIN 402 (1) Protected ww - Excel Product Activation Failed) Tell me what you want to de File Hame In Page Layout Formule Resim Vir Por Prect Hai Tur 9 Share PROTECTED VIFW Perales from the Intemeran contain Virus. In you need to edit, Pecater to stay in Protected View Fahle Fiting HI 1 0 P R 5 w X Y G 1 M N 2 According IRS.GOV" the contributions you make each year to all of your traditional IRA and Roth IRAs can't be more than $6000 $7000 if you're age 50 or older, or if less, your table compensation for the year).' Assume that my customer is under 50 years old, so she can max out the contribution $6,000 to her IRA If my customer contribute $5,000 for the next 30 year with the intercts rate of 9.14% per year, she will earn an average annuliced return: 5829,353.76. 1 Year Amount contribute enth year begin Received Interest End Received 2 11 $ 6,000.00 9.14% $ 6.000.00 3 2 s 6,000.00 $ 6,000.00 S 540.34 S 12.548.34 4 35 6,000.00 S 12,548.34 $ 1,146.79 S 19,695.13 S 6,000.00 $ 19,695.13 $ 1,799.94 $ 27,495.07 5 $ 6,000.00 $ 27,495.07 $ 2.512.77 $ 36,007.85 7 6 $ 6,000.00 S 36,007.85 S 3,220.70 S 45.238.60 8 7 $ 6,000.00 $ 45,292.60 $ 4,139.84 $ 55,438.44 8 $ 6,000.00 $ 55,438.44 $ 5,056.52 $ 66.504.96 10 9 $ 6,000.00S 66,504.30 S 6,077.89 S 78,582.05 11 10 $ 6,000.00 $ 73,582.85$ 7,131.69 $ 91,754.54 $ 12 11 $ 6,000.00 $ 91,764.34 $ 8,396.36 $206,150.90 13 12 S 6,000.00 S 106,150.30 S 9.20.13 121,852.03 14 13 $ 6,000.00 $ 131,852.03 $11,136.06 $138.988.00 15 14 $ 6,000.00 $ 128,983.08 $ 12.702.12 $157,080.21 16 15$ $ 6,000.00 S 157,690.21 $ 19,411.31 $178,101.51 17 16 S 6,000.00 $ 178,101.51 $ 16,276.70 5200.378.21 18 17 S 6,000.00 $ 200,378.21 $18,312.56 $224.090.77 79 10 S 6,000.00 S 224,6%0.77 $ 20.534.49 $251.225.26 19 $ 6,000.00 $ 251.225.76 $ 22.959.48 5220.184.74 20 $ 6,000.00 $ 250,184.74 $ 25,000.08 $211.790.83 21 $ 6,000.00 S 311,783 $ 28,424.56 $146.285.39 23 22 $ 6,000.00 $ 146,25.19 $ 11,647.02 $183.992.41 23 $ 6,000.00 S 383,932.41 $35,087.58 $425,019.99 29 $ 6,000.00 S 425,019.99 | $30,842.589469,852.57 25 $ 6,000.00 $ 464,862-7$ 42,940.74 $518.803.31 20 $ 6,000.00 $ 518,802.11 $ 47,413.43 $372.226.75 28 27 S 6,000.00 S 572,215/5 $ 52.244.899630,511.63 28 $ 6,000.00 $ 630,511.63 $57,623.46 5694.134.09 30 29 $ 6,000.00 $ $ 694,134.09 $ 63,436.91 $763,571.01 31 30 $ 6,000.00 S 763.571.01 $69.782.75 $39.353.75 14 35 39 40 Task 1 Task 2 Task 3-1 Task 2 Task 3-3 + 100% S N A 10:04 PM 1/12 S2 Hiniran-homework' EN 421 Protected Virul - Face Product Activation Failed Tull me what you want to de.. File Hame Inert Page Layou Formule Data Roir Vin Power Pivot Hei Tre Share O PROTECTED VIFW Re rules from the Intemeran contain Virus. In you need to edit, Pecater to stay in Protected View Frahlefciting IN 14 1 K K 1 M N P 2 R S T 11 V w X Y $143.83747.93 F F G H H 1090 W/1/2018 2821.17 2916.5 276.36 2750.34 2900.52 2901.52 0235220000 3.00% 1001 9/1/2018 2896.96 M0.91 2854.12 2913.98 2913.98 62497080000 0.43% 1092 10/1/2018 2920.29 2929.00 2003.54 2711.74 271174 91327930000 0.94% 1093 11/1/2018 2717.55 2515.15 2531,0922017 2750,12 2760.17 80080110000 1094 13/1/2018 2793.5 2800.12 2346.58 2506.AS 2505.99 1523520000 -9.18% 1095 1/1/2019 2470.96 2700.99 2443.90 27041 2704.1 30401030000 7.87% 7095 2/1/2019 2702.32 2513.49 2254,49 2784.49 70183430000 1007 2/1/2019 7790.>> 2860.31 2732.27 2834.4 2034.4 78596280000 1.79% 1099 4/1/2019 2548.63 2949.52 2840.63 2943.83 2945.83 USG04940000 21.92% 1099 5/1/2019 2352.33 2050.13 2750.52 2752.06 2752.06 76360120000 -6.50% 11006/1/2019 2751.53 2964,15 2778.81 2941.76 2941.76 70881390000 5.99% 1101 7/1/2019 2971.41 3027.93 2952.22 2980.38 2980.38 70349470000 1315 1102 8/1/2019 2800.82 313.59 2822.12 2926.46 2926.46 295949000 -1.81% 1103 9/1/2019 2909.01 2001.99 2891.85 2978,74 2976.74 73997330000 1.72% 1104 10/1/2019 283.09 2050.1 2555.94 3037.56 3037.56 77564550000 2.04% 1105 11/1/2019 50.72 3154.25 -0.22 3140.90 3140.90 721 720000 3.40% 1105 13/1/2019 3143.RS 3247.93 3070.33 3230.71 3230.78 72054000000 2.86% 1107 1/1/2020 3244.07 3237.77 3214.64 3225.52 3223.32 77104420000 0.16% 1100 2/1/2020 3235.66 3393.52 2855.04 2954.22 2054.22 812922 000 -3.41% 1100 1/1/2020 2974.22 $136.72 2191.86 2191.86 2584.59 25B4.59 1.61801E+11 -12.51% 1110 4/1/2020 2498.00 2904.80 2447.49 2912.43 2912.43 1.23103E-11 12.68% 1111 5/1/2020 2809.09 2060.67 2766.64 3044.31 3044.31 1.06799E+11 4.53% 1112 6/1/2020 308.78 B23.13 2965.66 3100,29 3100.29 1.31044E+11 1.84% 1118 7/1/2020 3105.92 3279.99 3101.17 3271.12 3271.12 97197020000 3.31% 1114 8/1/2020 3280.25 3514.77 3284.53 3500.31 3500.31 84902300000 7.01% 1115 9/1/2020 3507.44 258.11 3204.45 3363 3363 92014120000 -3.925 1110 10/1/2020 B185.87 3549.85 323.94 3209.90 3269.96 59737000000 277% 1117 11/1/2020 32052 3545.99 3229.24362) 3621.03 3621.63 1.00700011 10% 1118 13/1/2020 3545.87 3760.2 3633.4 3756.07 3756.07 96055410000 3.71% 1119 1/1/20213704.01 2301.45 3002.71 3062.71 3841.47 3841.47 69133270000 2.27% 1120 Monthly Arithmetic Average 1121 30 years 0.76 1122 20 years 0.53% 1123 10 years 1124 1125 1126 1127 1120 1129 Task #1 Task 2 Task 3-1 Task 3-2 Task 3-3 Ready + 100% 102 PM 1/10/2001 N Hwan-homework' EN42: Protected Viru] - Focal Product Activation failed Tell me what you want to de File Hame Page Layout Formule Data Resim Vin Power Pivot Hei Tre Share PROTECTED VIFW Perales from the Intemeran contain Virus. In you need to edit, Pecater to stay in Protected View Frehlefciting H 1 1 1 M N 0 R 5 w 7 3 4 A 1 Year Amount contribute esth year legini Received Interest End Received 2 11 $ 6,000.00 11.90% $ 6.000.00 2 s 6,000.00 $ 6,000.00 $ 714.06 $ 12.714.00 35 6,000.00 $ 12,714.00 1.513.10 S 20.227.16 S 4 $ 6,000.00 $ 20,227.16 $ 2,407.23 $ 28,634.39 5 $ 6,000.00 $ 28,634.29 $ 3.407.78 $ 38,042.17 7 6 $ 6,000.00 S 38,042-174,527.00 48.569.57 7 $ 6,000.00 $ 48,569.57 $ 5,780.26 $ 60,349.84 9 8 $ 6,000.00 $ 60,349.84 $ 7,182.23 $ 73,522.07 10 6,000.00 S 79,532,07 S 8,751,05 S 38.283.12 10 $ 6,000.00 $ 29,283.12$ 10,506.57 $104.789.70 12 If my customer contribute $5,000 for the next 10 year with the interets rate of 11.90% per year, she will earn an average annusliced return $104,789.70. 11 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 20 20 30 31 33 14 35 37 39 40 Task 1 Task 2 Task 3-1 Task #3-2 Task 3-3 E + 100% 10:13 PM 1/10/2011 11 N Hai Tran-homework'FN 4221 Protected Virul - Feel Pacurt Activation Failed Tell me what you want to de File Hame Page Layout Formule Resim Vir Por Prect Hei Tre Share PROTECTED VIFW Perales from the Intemeran contain Virus. In you need to edit, Pecater to stay in Protected View Frahleciting H35 G H 1 1 N 0 R 5 w 7 If my customer contribute $6,000, for the next 20 year with the interets Isle of 6.33% per year, she willen an average annualized return $278.717.83. A F 1 Year Amount contribute enth year begin Received Interest End Received 2 11 $ 6,000.00 6.33% $ 6.000.00 3 2 s 6,000.00 S 0,000.00 $ 379.83 $ 12.379.83 4 35 6,000.00 S 12,379.89 S789.71 $ 19,169.54 S 45 6,000.00 $ 19,163.54 $ 1,213.15$ 26,376.68 5 $ 6,000.00 $ 26,376.68 $ 1.609.78 $ 34,046.46 7 6 $ 6,000.00 S 34,045.40 2.155.31 S 42.201.77 8 7 $ 6,000.00 $ 42,201.77 $ 2,671.58 $ 50.873.35 8 $ 6,000.00 $ 50,873.35 $ 3.220.54 $ 60,093.89 10 9 $ 6,000.00 S 60,093.89 $ 3,804.29 S 69,898.13 11 10 $ 6,000.00 $ 69,892.13 $ 4,424.90 $ 80,323.04 12 11 $ 6,000.00 $ 30,323.04 $ 5,054.85 $ 91,407.89 13 12 s 6,000.00 S 91,407.89 5.786.58 103.194.46 14 13 $ 6,000.00 $ 109,114.46 $ 6.537.73 $115.727.19 15 6,000.00 $ 115,727.19 S 2.120.11 $129,033.30 16 15$ 6,000.00 S 129,053 30 S 8.109.72 $143.223.02 17 16 $ 6,000.00 $ 143,223.02 $ 9,056.73 $158.249.75 18 17 $ 6,000.00 $ 158,289.73 $ 10,020.53 $274,310.28 19 10 $ 6,000.00 S 174,310 28 $ 11,034.71 $191,24.99 20 19 $ 6,000.00 $ 191,344.99 $ 12,113.09 $209,458.09 21 20 $ 6,000.00 $ 209,458.09 $ 13.259.74 $228.717.83 23 25 26 27 20 20 30 31 33 14 35 II 32 39 29 40 Task 1 Task 2 Task 3-1 Task 3-2 Task #3-3 + Ready E + 100% 10:13 PM 1/10/2011 11 NE x 1