In: Computer Science
9- Matthew has a new job as business analyst. He plans to invest 10 percent of his annual salary after the tax into a retirement account at the end of every year for the next 30 years. Suppose that annual return of the investment is 6%, and his current salary before tax is 90k which grow 3% per year. The tax will apply as 15% on the salary up to 50k and it is 20% for the salary interval of 50k and 80k and the tax rate will be 25% for the remaining salary more than 80k (for example if his salary will be 105k, he is paying 15% tax on his first 50k and 20% in the next 30 k and 25% on his next 25k of his salary). then: a) Create a spreadsheet which shows Matthew the balance of retirement account for various levels of annual investments and returns. b) If Matthew aims to gain $1,000,000 at the end of the 30th year, what percentage of his salary he should put in the investment annually.
a) balance sheet for Mathew
Mathews Balance Sheet Year Wise along with Retirrement Inestment | |||||||||
Year | Gross slary | Tax Slab -1 Upto 50K | Tax Slab - 2 From 50K to 80K | Tax Slab - 3 Above 80K | Total Tax Deducted | Net Salary | Retirement Investment | Return on Investment | Retirement investment total after addition of Return |
1 | 90000 | 7500 | 6000 | 2500 | 16000 | 74000 | 20720 | 1243.2 | 21963.2 |
2 | 92700 | 7500 | 6000 | 3175 | 16675 | 76025 | 21287 | 1277.22 | 22564 |
3 | 95481 | 7500 | 6000 | 3870.25 | 17370.25 | 78110.75 | 21871 | 1312.26 | 23183 |
4 | 98345.43 | 7500 | 6000 | 4586.36 | 18086.36 | 80259.07 | 22473 | 1348.35 | 23821 |
5 | 101295.79 | 7500 | 6000 | 5323.95 | 18823.95 | 82471.84 | 23092 | 1385.53 | 24478 |
6 | 104334.67 | 7500 | 6000 | 6083.67 | 19583.67 | 84751.00 | 23730 | 1423.82 | 25154 |
7 | 107464.71 | 7500 | 6000 | 6866.18 | 20366.18 | 87098.53 | 24388 | 1463.26 | 25851 |
8 | 110688.65 | 7500 | 6000 | 7672.16 | 21172.16 | 89516.49 | 25065 | 1503.88 | 26568 |
9 | 114009.31 | 7500 | 6000 | 8502.33 | 22002.33 | 92006.98 | 25762 | 1545.72 | 27308 |
10 | 117429.59 | 7500 | 6000 | 9357.40 | 22857.40 | 94572.19 | 26480 | 1588.81 | 28069 |
11 | 120952.47 | 7500 | 6000 | 10238.12 | 23738.12 | 97214.36 | 27220 | 1633.20 | 28853 |
12 | 124581.05 | 7500 | 6000 | 11145.26 | 24645.26 | 99935.79 | 27982 | 1678.92 | 29661 |
13 | 128318.48 | 7500 | 6000 | 12079.62 | 25579.62 | 102738.86 | 28767 | 1726.01 | 30493 |
14 | 132168.03 | 7500 | 6000 | 13042.01 | 26542.01 | 105626.03 | 29575 | 1774.52 | 31350 |
15 | 136133.08 | 7500 | 6000 | 14033.27 | 27533.27 | 108599.81 | 30408 | 1824.48 | 32232 |
16 | 140217.07 | 7500 | 6000 | 15054.27 | 28554.27 | 111662.80 | 31266 | 1875.94 | 33142 |
17 | 144423.58 | 7500 | 6000 | 16105.89 | 29605.89 | 114817.68 | 32149 | 1928.94 | 34078 |
18 | 148756.29 | 7500 | 6000 | 17189.07 | 30689.07 | 118067.22 | 33059 | 1983.53 | 35042 |
19 | 153218.98 | 7500 | 6000 | 18304.74 | 31804.74 | 121414.23 | 33996 | 2039.76 | 36036 |
20 | 157815.54 | 7500 | 6000 | 19453.89 | 32953.89 | 124861.66 | 34961 | 2097.68 | 37059 |
21 | 162550.01 | 7500 | 6000 | 20637.50 | 34137.50 | 128412.51 | 35956 | 2157.33 | 38113 |
22 | 167426.51 | 7500 | 6000 | 21856.63 | 35356.63 | 132069.88 | 36980 | 2218.77 | 39198 |
23 | 172449.31 | 7500 | 6000 | 23112.33 | 36612.33 | 135836.98 | 38034 | 2282.06 | 40316 |
24 | 177622.79 | 7500 | 6000 | 24405.70 | 37905.70 | 139717.09 | 39121 | 2347.25 | 41468 |
25 | 182951.47 | 7500 | 6000 | 25737.87 | 39237.87 | 143713.60 | 40240 | 2414.39 | 42654 |
26 | 188440.01 | 7500 | 6000 | 27110.00 | 40610.00 | 147830.01 | 41392 | 2483.54 | 43876 |
27 | 194093.21 | 7500 | 6000 | 28523.30 | 42023.30 | 152069.91 | 42580 | 2554.77 | 45134 |
28 | 199916.01 | 7500 | 6000 | 29979.00 | 43479.00 | 156437.01 | 43802 | 2628.14 | 46431 |
29 | 205913.49 | 7500 | 6000 | 31478.37 | 44978.37 | 160935.12 | 45062 | 2703.71 | 47766 |
30 | 212090.90 | 7500 | 6000 | 33022.72 | 46522.72 | 165568.17 | 46359 | 2781.55 | 49141 |
Total Retirement Investment along with return After 30 Years |
1011002 | ||||||||
b) To Achieve the Aim he needs to invest 28% towards his retitrmrnt investment | |||||||||