Question
Part C. Answers using excel preferred.
4. Suppose you are 30. You expect to retire as soon as you reach age 65, and live to age 100. Your real annual labor income is $150,000 a year until age 65 Suppose the real discount rate is 2%. Suppose from age 40 you start paying social security tax of $12,000 and continue to do so each year until retirement Assume that all cash flows occur in annual frequencies and at year end, and the last social security tax is made when you just turn 65 What is your permanent income? How much do you need to save (including both personal and social security savings) each year during your working years in order to maintain a constant level of annual consumption that equals the permanent income? Verify that the PV of savings equal to the PV of consumption in retirement years. How much is your personal savings each year after you start paying social security tax? What is the annual benefit you get from social security after retirement? Does joining social security change your permanent income? a. b. c.
Explanation / Answer
Income Social Security Tax Disposable income Expenditure Saving Cumulative 150000 0 =C2-D2 =$L$6 =E2-F2 =G2 150000 0 =C3-D3 =$L$6 =E3-F3 =G3+(H2*1.02) 150000 0 =C4-D4 =$L$6 =E4-F4 =G4+(H3*1.02) 150000 0 =C5-D5 =$L$6 =E5-F5 =G5+(H4*1.02) 150000 0 =C6-D6 =$L$6 =E6-F6 =G6+(H5*1.02) =K38 150000 0 =C7-D7 =$L$6 =E7-F7 =G7+(H6*1.02) 150000 0 =C8-D8 =$L$6 =E8-F8 =G8+(H7*1.02) 150000 0 =C9-D9 =$L$6 =E9-F9 =G9+(H8*1.02) 150000 0 =C10-D10 =$L$6 =E10-F10 =G10+(H9*1.02) 150000 0 =C11-D11 =$L$6 =E11-F11 =G11+(H10*1.02) 150000 12000 =C12-D12 =$L$6 =E12-F12 =G12+(H11*1.02) 150000 12000 =C13-D13 =$L$6 =E13-F13 =G13+(H12*1.02) 150000 12000 =C14-D14 =$L$6 =E14-F14 =G14+(H13*1.02) 150000 12000 =C15-D15 =$L$6 =E15-F15 =G15+(H14*1.02) 150000 12000 =C16-D16 =$L$6 =E16-F16 =G16+(H15*1.02) 150000 12000 =C17-D17 =$L$6 =E17-F17 =G17+(H16*1.02) 150000 12000 =C18-D18 =$L$6 =E18-F18 =G18+(H17*1.02) 150000 12000 =C19-D19 =$L$6 =E19-F19 =G19+(H18*1.02) 150000 12000 =C20-D20 =$L$6 =E20-F20 =G20+(H19*1.02) 150000 12000 =C21-D21 =$L$6 =E21-F21 =G21+(H20*1.02) 150000 12000 =C22-D22 =$L$6 =E22-F22 =G22+(H21*1.02) 150000 12000 =C23-D23 =$L$6 =E23-F23 =G23+(H22*1.02) 150000 12000 =C24-D24 =$L$6 =E24-F24 =G24+(H23*1.02) 150000 12000 =C25-D25 =$L$6 =E25-F25 =G25+(H24*1.02) 150000 12000 =C26-D26 =$L$6 =E26-F26 =G26+(H25*1.02) 150000 12000 =C27-D27 =$L$6 =E27-F27 =G27+(H26*1.02) 150000 12000 =C28-D28 =$L$6 =E28-F28 =G28+(H27*1.02) 150000 12000 =C29-D29 =$L$6 =E29-F29 =G29+(H28*1.02) 150000 12000 =C30-D30 =$L$6 =E30-F30 =G30+(H29*1.02) 150000 12000 =C31-D31 =$L$6 =E31-F31 =G31+(H30*1.02) 150000 12000 =C32-D32 =$L$6 =E32-F32 =G32+(H31*1.02) 150000 12000 =C33-D33 =$L$6 =E33-F33 =G33+(H32*1.02) 150000 12000 =C34-D34 =$L$6 =E34-F34 =G34+(H33*1.02) 150000 12000 =C35-D35 =$L$6 =E35-F35 =G35+(H34*1.02) 150000 12000 =C36-D36 =$L$6 =E36-F36 =G36+(H35*1.02) 150000 12000 =C37-D37 =$L$6 =E37-F37 =G37+(H36*1.02) 0 0 =C38-D38 =$L$6 0 =NPV(0.02,F38:F72) =I38-H37 96049.0629564518 0 0 =C39-D39 =$L$6 0 0 0 =C40-D40 =$L$6 0 0 0 =C41-D41 =$L$6 0 0 0 =C42-D42 =$L$6 0 0 0 =C43-D43 =$L$6 0 0 0 =C44-D44 =$L$6 0 0 0 =C45-D45 =$L$6 0 0 0 =C46-D46 =$L$6 0 0 0 =C47-D47 =$L$6 0 0 0 =C48-D48 =$L$6 0 0 0 =C49-D49 =$L$6 0 0 0 =C50-D50 =$L$6 0 0 0 =C51-D51 =$L$6 0 0 0 =C52-D52 =$L$6 0 0 0 =C53-D53 =$L$6 0 0 0 =C54-D54 =$L$6 0 0 0 =C55-D55 =$L$6 0 0 0 =C56-D56 =$L$6 0 0 0 =C57-D57 =$L$6 0 0 0 =C58-D58 =$L$6 0 0 0 =C59-D59 =$L$6 0 0 0 =C60-D60 =$L$6 0 0 0 =C61-D61 =$L$6 0 0 0 =C62-D62 =$L$6 0 0 0 =C63-D63 =$L$6 0 0 0 =C64-D64 =$L$6 0 0 0 =C65-D65 =$L$6 0 0 0 =C66-D66 =$L$6 0 0 0 =C67-D67 =$L$6 0 0 0 =C68-D68 =$L$6 0 0 0 =C69-D69 =$L$6 0 0 0 =C70-D70 =$L$6 0 0 0 =C71-D71 =$L$6 0 0 0 =C72-D72 =$L$6 0
Income Social Security Tax Disposable income Expenditure Saving Cumulative 150000 0 150000 96049.063 53950.94 53950.93704 150000 0 150000 96049.063 53950.94 108980.8928 150000 0 150000 96049.063 53950.94 165111.4477 150000 0 150000 96049.063 53950.94 222364.6137 150000 0 150000 96049.063 53950.94 280762.843 96049.06 150000 0 150000 96049.063 53950.94 340329.0369 150000 0 150000 96049.063 53950.94 401086.5547 150000 0 150000 96049.063 53950.94 463059.2229 150000 0 150000 96049.063 53950.94 526271.3444 150000 0 150000 96049.063 53950.94 590747.7083 150000 12000 138000 96049.063 41950.94 644513.5995 150000 12000 138000 96049.063 41950.94 699354.8085 150000 12000 138000 96049.063 41950.94 755292.8418 150000 12000 138000 96049.063 41950.94 812349.6356 150000 12000 138000 96049.063 41950.94 870547.5654 150000 12000 138000 96049.063 41950.94 929909.4537 150000 12000 138000 96049.063 41950.94 990458.5799 150000 12000 138000 96049.063 41950.94 1052218.689 150000 12000 138000 96049.063 41950.94 1115213.999 150000 12000 138000 96049.063 41950.94 1179469.216 150000 12000 138000 96049.063 41950.94 1245009.538 150000 12000 138000 96049.063 41950.94 1311860.666 150000 12000 138000 96049.063 41950.94 1380048.816 150000 12000 138000 96049.063 41950.94 1449600.729 150000 12000 138000 96049.063 41950.94 1520543.681 150000 12000 138000 96049.063 41950.94 1592905.492 150000 12000 138000 96049.063 41950.94 1666714.538 150000 12000 138000 96049.063 41950.94 1741999.766 150000 12000 138000 96049.063 41950.94 1818790.699 150000 12000 138000 96049.063 41950.94 1897117.45 150000 12000 138000 96049.063 41950.94 1977010.736 150000 12000 138000 96049.063 41950.94 2058501.887 150000 12000 138000 96049.063 41950.94 2141622.862 150000 12000 138000 96049.063 41950.94 2226406.256 150000 12000 138000 96049.063 41950.94 2312885.319 150000 12000 138000 96049.063 41950.94 2401093.962 0 0 0 96049.063 0 24,01,093.96 0.00 96049.06 0 0 0 96049.063 0 0 0 0 96049.063 0 0 0 0 96049.063 0 0 0 0 96049.063 0 0 0 0 96049.063 0 0 0 0 96049.063 0 0 0 0 96049.063 0 0 0 0 96049.063 0 0 0 0 96049.063 0 0 0 0 96049.063 0 0 0 0 96049.063 0 0 0 0 96049.063 0 0 0 0 96049.063 0 0 0 0 96049.063 0 0 0 0 96049.063 0 0 0 0 96049.063 0 0 0 0 96049.063 0 0 0 0 96049.063 0 0 0 0 96049.063 0 0 0 0 96049.063 0 0 0 0 96049.063 0 0 0 0 96049.063 0 0 0 0 96049.063 0 0 0 0 96049.063 0 0 0 0 96049.063 0 0 0 0 96049.063 0 0 0 0 96049.063 0 0 0 0 96049.063 0 0 0 0 96049.063 0 0 0 0 96049.063 0 0 0 0 96049.063 0 0 0 0 96049.063 0 0 0 0 96049.063 0 0 0 0 96049.063 0
