Question

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.

Answer 1

Please see the spreadsheet for answer to (a)

Percentage of salary invested			10%
Year	Salary before tax	Tax	Salary after tax	Investment made	Balance at year end
1	$     90,000.00	$ 16,000.00	$        74,000.00	$    7,400.00	$ 7,400.00
2	$     92,700.00	$ 16,675.00	$        76,025.00	$    7,602.50	$ 15,446.50
3	$     95,481.00	$ 17,370.25	$        78,110.75	$    7,811.08	$ 24,184.37
4	$     98,345.43	$ 18,086.36	$        80,259.07	$    8,025.91	$ 33,661.33
5	$ 101,295.79	$ 18,823.95	$        82,471.84	$    8,247.18	$ 43,928.20
6	$ 104,334.67	$ 19,583.67	$        84,751.00	$    8,475.10	$ 55,038.99
7	$ 107,464.71	$ 20,366.18	$        87,098.53	$    8,709.85	$ 67,051.18
8	$ 110,688.65	$ 21,172.16	$        89,516.49	$    8,951.65	$ 80,025.90
9	$ 114,009.31	$ 22,002.33	$        92,006.98	$    9,200.70	$ 94,028.15
10	$ 117,429.59	$ 22,857.40	$        94,572.19	$    9,457.22	$   109,127.06
11	$ 120,952.47	$ 23,738.12	$        97,214.36	$    9,721.44	$   125,396.12
12	$ 124,581.05	$ 24,645.26	$        99,935.79	$    9,993.58	$   142,913.47
13	$ 1,28,318.48	$ 25,579.62	$ 102,738.86	$ 10,273.89	$   161,762.16
14	$ 132,168.03	$ 26,542.01	$ 105,626.03	$ 10,562.60	$   182,030.49
15	$ 136,133.08	$ 27,533.27	$ 108,599.81	$ 10,859.98	$   203,812.31
16	$ 140,217.07	$ 28,554.27	$ 111,662.80	$ 11,166.28	$   227,207.32
17	$ 144,423.58	$ 29,605.89	$ 114,817.68	$ 11,481.77	$   252,321.53
18	$ 148,756.29	$ 30,689.07	$ 118,067.22	$ 11,806.72	$   279,267.54
19	$ 153,218.98	$ 31,804.74	$ 121,414.23	$ 12,141.42	$   308,165.02
20	$ 157,815.54	$ 32,953.89	$ 124,861.66	$ 12,486.17	$   339,141.09
21	$ 162,550.01	$ 34,137.50	$ 128,412.51	$ 12,841.25	$   372,330.80
22	$ 167,426.51	$ 35,356.63	$ 132,069.88	$ 13,206.99	$   407,877.64
23	$ 172,449.31	$ 36,612.33	$ 135,836.98	$ 13,583.70	$   445,934.00
24	$ 177,622.79	$ 37,905.70	$ 139,717.09	$ 13,971.71	$   486,661.75
25	$ 182,951.47	$ 39,237.87	$ 143,713.60	$ 14,371.36	$   530,232.81
26	$ 188,440.01	$ 40,610.00	$ 147,830.01	$ 14,783.00	$   576,829.78
27	$ 194,093.21	$ 42,023.30	$ 152,069.91	$ 15,206.99	$   626,646.56
28	$ 199,916.01	$ 43,479.00	$ 156,437.01	$ 15,643.70	$   679,889.05
29	$ 205,913.49	$ 44,978.37	$ 160,935.12	$ 16,093.51	$   736,775.91
30	$ 212,090.90	$ 46,522.72	$ 165,568.17	$ 16,556.82	$ 797,539.28

For formulas, see the screenshot below for your ready reference:

(b) To find answer to (b), you can use Excel Goal Seek. For that, simply select cell F33 [i.e., the cell that has the balance of the investment account at the end of 30 years, and go to Data > What If Analysis > Goal Seek. Then in Set Cell, it will be F33, in To value, it should be 1000000, and in By changing cell, select cell D1 [that is the cell where we input 10%], or just type D1. Press OK. It will change the value of 10% to 12.53866...%, which we can round up as 12.54% (or use such other rounding systems). So, if he invests 12.54% (rounded) of his salary after tax every year, the account will accumulate to $1,000,000.

See the snap of the Goal Seek for your ready reference:

Hope this helps.
Feel free to ask for clarifications.
Please leave a thumbs up if this helps.
Thank you!