Question

In: Accounting

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 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 25 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) .

Solutions

Expert Solution

a)

End of Year Salary(Prev Value*103%) Tax(=(50000*15%)+(30000*20%)+((Salary-80000)*25%) Net Salary(Salary-Tax) Investment Installment (Net Salary*15%) Balance in Investment Acct@4%{=(Prev Balance*104%)+Current Installment}
1 80,000 13500 66,500 9975 9975
2 82400 14100 68,300 10245 20619
3 84872 14718 70,154 10523.1 31966.86
4 87418.16 15354.54 72,064 10809.543 44055.0774
5 90040.7048 16010.1762 74,031 11104.57929 56921.85979
6 92741.92594 16685.48149 76,056 11408.46667 70607.20085
7 95524.18372 17381.04593 78,143 11721.47067 85152.95955
8 98389.90923 18097.47731 80,292 12043.86479 100602.9427
9 101341.6065 18835.40163 82,506 12375.93073 117002.9912
10 104381.8547 19595.46368 84,786 12717.95865 134401.0695
11 107513.3103 20378.32759 87,135 13070.24741 152847.3597
12 110738.7097 21184.67741 89,554 13433.10484 172394.3589
13 114060.8709 22015.21774 92,046 13806.84798 193096.9812
14 117482.6971 22870.67427 94,612 14191.80342 215012.6639
15 121007.178 23751.7945 97,255 14588.30752 238201.478
16 124637.3933 24659.34833 99,978 14996.70675 262726.2438
17 128376.5151 25594.12878 102,782 15417.35795 288652.6515
18 132227.8106 26556.95265 105,671 15850.62869 316049.3863
19 136194.6449 27548.66122 108,646 16296.89755 344988.2593
20 140280.4842 28570.12106 111,710 16756.55448 375544.3441
21 144488.8988 29622.22469 114,867 17230.00111 407796.119
22 148823.5657 30705.89143 118,118 17717.65115 441825.6149
23 153288.2727 31822.06818 121,466 18219.93068 477718.5702
24 157886.9209 32971.73022 124,915 18737.2786 515564.5916
25 162623.5285 34155.88213 128,468 19270.14696 555457.3222

b)

Balance in Investment Acct@Random 5-9%{=(Prev Bal+ Prev Balance*Randbetween(5,9)%)+Current Installment} Balance in Investment Acct@Random 5-9%{=(Prev Bal+Prev Balance*Randbetween(5,9)%)+Current Installment} Balance in Investment Acct@Random 5-9%{=(Prev Bal+Prev Balance*Randbetween(5,9)%)+Current Installment} Balance in Investment Acct@Random 5-9%{=(Prev Bal+Prev Balance*Randbetween(5,9)%)+Current Installment} Balance in Investment Acct@Random 5-9%{=(Prev Bal+Prev Balance*Randbetween(5,9)%)+Current Installment} Balance in Investment Acct@Random 5-9%{=(Prev Bal+Prev Balance*Randbetween(5,9)%)+Current Installment} Balance in Investment Acct@Random 5-9%{=(Prev Bal+Prev Balance*Randbetween(5,9)%)+Current Installment} Balance in Investment Acct@Random 5-9%{=(Prev Bal+Prev Balance*Randbetween(5,9)%)+Current Installment} Balance in Investment Acct@Random 5-9%{=(Prev Bal+Prev Balance*Randbetween(5,9)%)+Current Installment} Balance in Investment Acct@Random 5-9%{=(Prev Bal+Prev Balance*Randbetween(5,9)%)+Current Installment}
9975 9975 9975 9975 9975 9975 9975 9975 9975 9975
21117.75 20718.75 20918.25 20818.5 20718.75 21018 20818.5 21018 20818.5 21018
33330.27 32484.98 32487.26 33215.27 32277.79 32592 32590.71 33432.72 33215.27 33222.54
46806.23 46218.17 44921.17 46349.88 44701.22 45357.06 46007.51 47251.21 46017.72 46689.89
61187.25 60558.02 59170.23 60698.95 59381.9 59636.64 61252.76 60718.35 59883.37 60128.96
75655.08 76205.54 73537.21 76356.34 74353.28 75219.67 75723.87 77591.47 74286 76949.03
94185.51 93261.4 91877.03 91895.63 91279.48 93710.91 91988.77 94744.34 90464.63 92517.96
111880.5 113698.8 111271.1 111291.1 109712.9 111377.4 109552 112472.9 110650.3 111038.1
129850.5 134033.6 130323.2 129231.6 129768.7 132663.6 131787.6 130472.4 129665.3 128965.9
149060.9 157474.3 153467.1 149703.5 148975.1 154668 155048.5 151018.7 151459.8 152001.1
175546.7 178418.3 180349.4 171755.9 173963.4 175471.6 180522.7 171639.9 175132.2 178751.5
197757.1 202556.5 208210.4 195494.4 196094.7 197678.3 202981.9 197087.8 204327.2 206484.7
223429.4 232567.8 236592 221030.9 223628.1 225322.6 230997.5 224690.8 232437 238875.2
253261.2 263039.4 267345.2 248484.6 257946.5 259793.5 265979.1 254611 262899.4 274565.8
290643.1 301301.2 305994.6 275497.1 288011.6 287371.4 296526.1 292114.3 290632.7 308373.7
325984.8 337389 342410.9 304268.7 328929.3 319610.4 329314.4 327559 323067.3 351124
357701.4 376423.6 388645.3 337942.2 367371.7 363792.7 364490.6 369181.1 354638 398142.5
391437.1 414859.6 423928.2 370689.9 401590.9 397833 398565.8 418258 388220.6 445844.6
442963.3 456048.1 465660.7 412935.1 437967.4 441978.2 446747.9 455467.8 431692.9 484433.7
499586.6 500167.6 515013.6 462726.4 476622.3 494093 499244.3 513216.4 478668 539944.9
551787.6 562412.6 578594.8 512347.3 527215.9 550850.5 561406.3 571503.8 529404.7 584172.2
602612.5 608250.9 625242.2 555682.3 592383 596110.6 618422.4 629226.7 573592.6 636940.2
669041.5 681213.4 687229 607243.2 652069.7 667980.5 679931.9 678907.9 626228.1 687007.1
734611.7 761259.9 767816.9 680632.3 709931.2 746836 746264.4 758746.9 695063.6 767575 Total Avg(Total/10)
819996.9 818593.1 848512.4 747546.8 793095.1 810916.3 817773.1 846304.3 749087 855926.9 8,107,752 810,775.2

If helpful, Thumbs UP please:)

ekkarill92 answered 2 years 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 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 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
ADVERTISEMENT
Latest Questions
ADVERTISEMENT