Question

In: Computer Science

9- Matthew has a new job as business analyst. He plans to invest 10 percent of...

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.

Solutions

Expert Solution

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

Related Solutions

Matthew has a new job as business analyst. He plans to invest 10 percent of his...
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...
Matthew has a new job as business analyst. He plans to invest 12 percent of his...
Matthew has a new job as business analyst. He plans to invest 12 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 is 5%, and his current after tax salary is 76k which grow 3% per year then: 1)Create a spreadsheet which shows Matthew the balance of retirement account for various levels of annual investments and returns. (EXCEL) 2)If Matthew aims to...
Matthew has a new job as business analyst. He plans to invest 15 percent of his...
Matthew has a new job as business analyst. He plans to invest 15 percent of his annual salary after the tax into a retirement account at the end of every year for the next 25 years. Suppose that annual return is 4, and his current salary before tax is 80k 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...
Matthew has a new job as business analyst. He plans to invest 15 percent of his...
Matthew has a new job as business analyst. He plans to invest 15 percent of his annual salary after the tax into a retirement account at the end of every year for the next 25 years. Suppose that annual return is changing 4% function in Excel), and his current salary before tax is 80k 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...
Matthew has a new job as business analyst. He plans to invest 15 percent of his...
Matthew has a new job as business analyst. He plans to invest 15 percent of his annual salary after the tax into a retirement account at the end of every year for the next 25 years. Suppose that annual return is 4, and his current salary before tax is 80k 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...
Adam plans to invest $1,500 today in a mutual fund. If he earns 12 percent interest...
Adam plans to invest $1,500 today in a mutual fund. If he earns 12 percent interest compounded monthly, the amount his investment will grow to in 20 years is closest to:
Richard plans to invest $100,000 for a 50 percent interest in a small business. His friend...
Richard plans to invest $100,000 for a 50 percent interest in a small business. His friend Jack will also invest $100,000 for the remaining 50 percent interest. On their investment, they expect to generate 10 percent before-tax return the first year. Richard’s marginal tax rate is 24 percent, and Jack’s marginal tax rate is 32 percent.Their tax rate for capital gains and dividend income is 15 percent They need to decide whether to establish the business as a partnership or...
New Business Ventures, Inc., has an outstanding perpetual bond with a coupon rate of 9 percent...
New Business Ventures, Inc., has an outstanding perpetual bond with a coupon rate of 9 percent that can be called in one year. The bond makes annual coupon payments and has a par value of $1,000. The call premium is set at $150 over par value. There is a 60 percent chance that the interest rate in one year will be 11 percent, and a 40 percent chance that the interest rate will be 7 percent. If the current interest...
A stock has returns of −6 percent, 12 percent, 5 percent, 10 percent, and −9 percent....
A stock has returns of −6 percent, 12 percent, 5 percent, 10 percent, and −9 percent. What are the arithmetic and geometric returns? (Do not round intermediate calculations. Enter your answers as a percent rounded to 2 decimal places.) Arithmetic return% ? Geometric return% ?
A friend proposes to him to invest in a business for which he has projected the...
A friend proposes to him to invest in a business for which he has projected the following costs and income: 1. Initial cost of $ 100 0002. Expenditure in year 1 of $ 50 0003. Annual expenses from year 2 to 10 of $ 10 000. Additionally he projects that from In year 4, these expenses will increase by $ 2,000 each year. 4. Annual income of $ 40,000 from year 1 to 10. Additionally, it projects that from year...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT