Question

In: Accounting

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 gain $1,500,000 at the end of the 30th year, what percentage of his salary he should put in the investment annually.

Solutions

Expert Solution

Amount = Principal (1+r)^t
Year 1 Investment in Retirement account(12% of Salary) (P) Years left(t) Amount
76000.00 9120.00 29 37539.16
78280.00 9393.60 28 36824.13
80628.40 9675.41 27 36122.71
83047.25 9965.67 26 35434.66
85538.67 10264.64 25 34759.72
88104.83 10572.58 24 34097.63
90747.97 10889.76 23 33448.15
93470.41 11216.45 22 32811.04
96274.53 11552.94 21 32186.07
99162.76 11899.53 20 31573.00
102137.64 12256.52 19 30971.61
105201.77 12624.21 18 30381.67
108357.83 13002.94 17 29802.98
111608.56 13393.03 16 29235.30
114956.82 13794.82 15 28678.44
118405.52 14208.66 14 28132.18
121957.69 14634.92 13 27596.33
125616.42 15073.97 12 27070.69
129384.91 15526.19 11 26555.05
133266.46 15991.98 10 26049.24
137264.45 16471.73 9 25553.07
141382.39 16965.89 8 25066.34
145623.86 17474.86 7 24588.89
149992.57 17999.11 6 24120.53
154492.35 18539.08 5 23661.09
159127.12 19095.25 4 23210.40
163900.94 19668.11 3 22768.30
168817.96 20258.16 2 22334.62
173882.50 20865.90 1 21909.20
179098.98 21491.88 0 21491.88
TOTAL 433887.79 863974.04
Principal 433887.79
Amount at end of 30 years 863974.04
Total Return (A-P) 430086.245
We calculate by using future value of growing annuity formula. FVGA = P*{[(1+r)^n - (1+g)^n]/(r-g)} FVGA = 1500000, r = 5%, g = 3%, n = 30, P = First Payment
P = 15833.84
Amount = Principal (1+r)^t
Year 1 Investment in Retirement account(20.834% of Salary) (P) Years left(t) Amount
76000.00 15833.84 29 65174.23
78280.00 16308.86 28 63932.82
80628.40 16798.12 27 62715.05
83047.25 17302.06 26 61520.48
85538.67 17821.13 25 60348.66
88104.83 18355.76 24 59199.16
90747.97 18906.43 23 58071.56
93470.41 19473.63 22 56965.43
96274.53 20057.83 21 55880.38
99162.76 20659.57 20 54815.99
102137.64 21279.36 19 53771.88
105201.77 21917.74 18 52747.65
108357.83 22575.27 17 51742.93
111608.56 23252.53 16 50757.35
114956.82 23950.10 15 49790.55
118405.52 24668.61 14 48842.15
121957.69 25408.67 13 47911.83
125616.42 26170.92 12 46999.22
129384.91 26956.05 11 46104.00
133266.46 27764.73 10 45225.83
137264.45 28597.68 9 44364.38
141382.39 29455.61 8 43519.35
145623.86 30339.27 7 42690.41
149992.57 31249.45 6 41877.26
154492.35 32186.94 5 41079.59
159127.12 33152.54 4 40297.13
163900.94 34147.12 3 39529.56
168817.96 35171.53 2 38776.62
173882.50 36226.68 1 38038.01
179098.98 37313.48 0 37313.48
TOTAL 753301.52 1500002.92
Principal 753301.52
Amount at end of 30 years 1500002.92
Total Return (A-P) 746701.4023

Therefore, 20.834 % percentage of his salary he should put in the investment annually to get an amount equal to 1500000 at the end of 30 years.

To get an interest equal to 1500000, then 41.852% should be invested.

Amount = Principal (1+r)^t
Year 1 Investment in Retirement account(41.852% of Salary) (P) Years left(t) Amount
76000.00 31807.52 29 130924.07
78280.00 32761.75 28 128430.27
80628.40 33744.60 27 125983.98
83047.25 34756.94 26 123584.29
85538.67 35799.64 25 121230.30
88104.83 36873.63 24 118921.15
90747.97 37979.84 23 116655.99
93470.41 39119.24 22 114433.97
96274.53 40292.81 21 112254.27
99162.76 41501.60 20 110116.10
102137.64 42746.65 19 108018.65
105201.77 44029.05 18 105961.15
108357.83 45349.92 17 103942.84
111608.56 46710.42 16 101962.98
114956.82 48111.73 15 100020.83
118405.52 49555.08 14 98115.67
121957.69 51041.73 13 96246.80
125616.42 52572.98 12 94413.53
129384.91 54150.17 11 92615.17
133266.46 55774.68 10 90851.07
137264.45 57447.92 9 89120.58
141382.39 59171.36 8 87423.04
145623.86 60946.50 7 85757.84
149992.57 62774.89 6 84124.36
154492.35 64658.14 5 82521.99
159127.12 66597.88 4 80950.14
163900.94 68595.82 3 79408.24
168817.96 70653.69 2 77895.70
173882.50 72773.31 1 76411.97
179098.98 74956.50 0 74956.50
TOTAL 1513255.99 3013253.45
Principal 1513255.99
Amount at end of 30 years 3013253.45
Total Return (A-P) 1499997.46

Gain of 1500000 can be taken in both ways i.e. Total Amount as 1500000 or just the interest component of it.

ekkarill92 answered 1 year ago
Next > < Previous

Related Solutions

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...
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...
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...
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:
STATE: Andrew plans to retire in 40 years. He plans to invest part of his retirement...
STATE: Andrew plans to retire in 40 years. He plans to invest part of his retirement funds in stocks, so he seeks out information on past returns. He learns that from 1966 to 2015, the annual returns on S&P 500 had mean 11.0% and standard deviation 17.0% . PLAN: The distribution of annual returns on common stocks is roughly symmetric, so the mean return over even a moderate number of years is close to Normal. We can use the Central...
1. Andrew plans to retire in 38 years. He plans to invest part of his retirement...
1. Andrew plans to retire in 38 years. He plans to invest part of his retirement funds in stocks, so he seeks out information on past returns. He learns that over the entire 20th century, the real (that is, adjusted for inflation) annual returns on U.S. common stocks had mean 8.7% and standard deviation 20.2%. The distribution of annual returns on common stocks is roughly symmetric, so the mean return over even a moderate number of years is close to...
Jason has decided to donate his kidney to his brother Matthew so that he can live....
Jason has decided to donate his kidney to his brother Matthew so that he can live. How would the psychoanalytic, social learning, and cognitive theorists explain his actions?
A company plans to invest in a new manufacturing project over the next 12 years. The...
A company plans to invest in a new manufacturing project over the next 12 years. The project will require an initial investment of $65,000, and an additional investment of $26,000 in Year 5. Starting in Year 2, the company will reduce their labour costs by $14,500 a year for the next 5 years, and $9000 a year for the next 4 years. At the end of the project, there will be a residual value of $24,000. If the company’s cost...
ADVERTISEMENT
Subjects
ADVERTISEMENT
Latest Questions
ADVERTISEMENT