Question

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 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 the annual return rate is uncertain and it is between 5% to 9% in any given year (hint: use = =RANDBETWEEN(2,9)/100) what would be the expected value (average) of Matthew balance after 25 years (use the model 10 times then report the average) .

Answer 1

a) Annual Return is 4%
Particulars	Annual Salary Before tax	Tax	Annual Salary after tax	Annual Investment in fund (A)	Opening Balance of fund(B)	Return@4% on opening bal	Closing Balance of fund A+B+C
1	80,000	13,500	66,500	9,975	-	-	9,975
2	82,400	14,100	68,300	10,245	9,975	399	20,619
3	84,872	14,718	70,154	10,523	20,619	825	31,967
4	87,418	15,355	72,064	10,810	31,967	1,279	44,055
5	90,041	16,010	74,031	11,105	44,055	1,762	56,922
6	92,742	16,685	76,056	11,408	56,922	2,277	70,607
7	95,524	17,381	78,143	11,721	70,607	2,824	85,153
8	98,390	18,097	80,292	12,044	85,153	3,406	100,603
9	101,342	18,835	82,506	12,376	100,603	4,024	117,003
10	104,382	19,595	84,786	12,718	117,003	4,680	134,401
11	107,513	20,378	87,135	13,070	134,401	5,376	152,847
12	110,739	21,185	89,554	13,433	152,847	6,114	172,394
13	114,061	22,015	92,046	13,807	172,394	6,896	193,097
14	117,483	22,871	94,612	14,192	193,097	7,724	215,013
15	121,007	23,752	97,255	14,588	215,013	8,601	238,201
16	124,637	24,659	99,978	14,997	238,201	9,528	262,726
17	128,377	25,594	102,782	15,417	262,726	10,509	288,653
18	132,228	26,557	105,671	15,851	288,653	11,546	316,049
19	136,195	27,549	108,646	16,297	316,049	12,642	344,988
20	140,280	28,570	111,710	16,757	344,988	13,800	375,544
21	144,489	29,622	114,867	17,230	375,544	15,022	407,796
22	148,824	30,706	118,118	17,718	407,796	16,312	441,826
23	153,288	31,822	121,466	18,220	441,826	17,673	477,719
24	157,887	32,972	124,915	18,737	477,719	19,109	515,565
25	162,624	34,156	128,468	19,270	515,565	20,623	555,457

Using the model for 10 times, the expected value of balance after 25 years is shown below

Randbetween	Closing balance of Retirement fund
5.00%	628,194
9.00%	1,061,979
8.00%	ekkarill92 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 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 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... 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? 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: 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 $ ADVERTISEMENT Subjects Accounting Advanced Math Anatomy and Physiology Biology Chemistry Civil Engineering Computer Science Economics Electrical Engineering Finance History Math Mechanical Engineering Operations Management Physics Psychology Statistics and Probability Nursing Other ADVERTISEMENT Latest Questions An investor is considering purchasing a Treasury bond with a 20 year maturity, an 8% coupon... Write a program that copies the contents of one file to a destination file. This program... how to conduct a marketing mix for 3d crest white strips Is big data analytics used for service innovation at Amazon? If so, please write a brief... A shrink-fit process has been used to enhance the pressure carrying capacity of a cylinder. During... Ten-year zero coupon bonds issued by the U.S. Treasury have a face value of $1,000 and... The Blossom Department of Transportation has issued 25-year bonds that make semiannual coupon payments at a... ADVERTISEMENT About @ 2020 WizEdu