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...
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...
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...
Pierre Dupont just received a cash gift from his grandfather. He plans to invest in a...
Pierre Dupont just received a cash gift from his grandfather. He plans to invest in a five-year bond issued by Venice Corp. that pays an annual coupon of 4.65 percent. If the current market rate is 8.79 percent, what is the maximum amount Pierre should be willing to pay for this bond? (Round answer to 2 decimal places, e.g. 15.25.) Pierre should pay $
Joseph Ray just received an inheritance of $42,125 from his great aunt. He plans to invest...
Joseph Ray just received an inheritance of $42,125 from his great aunt. He plans to invest the funds for retirement. If Joseph can earn 5.25% per year with quarterly compounding for 30 years, how much will he have accumulated? (Round off to the nearest dollar.)
ADVERTISEMENT
Subjects
ADVERTISEMENT
Latest Questions
ADVERTISEMENT