Office Update To keep up-to-date with security updates, fixes, and improvements, choose CH C48 4Xfx In cell D40, by using cell references, calculate the return that yo G H 2 Problem 4-25 You are trying to decide how much to save for retirement. Assume you plan to save 55,000 per year with the fint investment made one year from now. You think you can earn 10% per year on your investments and you plan to retire in 43 years, immediately after making your last $5,000 investment Complete the steps below asing dll references to give data ar previous calculationer. In some cases, a simple el reference is all you need to copy paste a formula across a row or down a coman absolute all rerence or a mixed cell reference may be preferred apelfie Excel function is to be used the directions will specify the we of that function. Do nor pre in merical data into a cell or function Instead, make a reference to the call in which the data is found. Make your computations only in the Nue cells highlighted below. In all cases, less otherwise directed use the earliest appearance of the data in your formulas wally the Guen Data section How much will you have in your retirement account on the day you retire? b. instead of investing 55,000 per year, you wanted to make one fump-sum investment today for your retirement that will result in the same retirement saving, how much would that lump sum need to be? C If you hope to live for 20 years in retirement, how much cari you withdraw every you in retirement (starting one you after retirement) so that you will just exhaust your savings with the 20th withdrawal (assume your savings will continue to cam 10% in retirement d. If instead, you decide to withdraw 5300,000 per year in retirement again with the fint withdrwal one your after retiring), how many years will it take until you exhaust your savings? Assoming the most you can afford to save $1,000 per year, but you want to retire with $1 million in your investment account, how high of a retum do you need to cum on your investments? 11 12 s Annual saving Interest rate Years to retirement 5.000 10% 43 14 25 How much will you have in your retirement account on the day you retire! 17 Future value b. 10 instead of investing 55,000 per year, you wanted to make one luopumisesta today for your retirement that will result in the same time saving how much would that lumpsum nood to be LY 20 Lumpun lavestment 21 If you hope to live for 20 years in time, how today withdraw every yow in retirement anting on your timet) so that you will just exhaust your savings with the 20th with assume your savings will continuato a 10 in retirement? 4-25 + Office Update To keep up-to-date with security updates, fixes, and improvements, choose Check C48 X w fx In cell D40, by using cell references, calculate the return that you n H A 8 24 25 Years of withdrawal 20 26 22 Annual withdrawal 28 d. It instead, you decide to withdraw $300,000 per year in retirement (again with the first withdrawal one year after retinng), how many years will it take until you exhaust your savings? 19 30 Annual withdrawal $ 31 32 300,000 33 Number of periods 34 35 Assuming the most you can afford to save is $1,000 per year, but you want to retire with Si million in your investment account, how high of a return do you need to cam on your investments? 36 37 35 Annual saving Future value $ $ 1,000 1,000,000 40 41 Rate of retum AH Requirements 1 In cell D17, by using cell references, calculate the future value of the yerely savings on the day you retire (I pt.) Note: The output of the expression or function you typed in this cell is expected as positive number 2 3 In cell 121, by using cell references, calculate the lamp som that you need to invest today in order to save the same amount as with the yearly savings (pt.). Note: The output of the expression or function you typed in this cell is expected a positive number. In cell D27, by using cell references, calculate the amount the you can withdrawn from your retirement account every year (l pt.) in cell D33, by using cell references, calculate the number of years that it will take to deplete your retirement savings if you with a tot el pt.) In cell D40, by using cell references, calculate the setum that you need to cam on your yearly savings for a certain payment to reach your goal retirement Note: For this particular case, only use the first four parameters of the RATE function. Assume Anual savings as a cash outfiow and the Future values an cash inflow. (1 pt). 4 5 RE