How do you calculate payments made into 401k for the problem below? I found a table where someone had calculated a timeline table with payments, but I cannot figure out how they got the numbers.

Attached please find a screenshot of the timeline with payments.

I know how the final years salary, 70% of salary, total money needed in 401k at retirement, and annual amount needed to save/contribute was calculated and I also came up with the same numbers for those questions.

I just don't understand where or how they got the numbers in the 4% column (32230, 67038) or the numbers in the post retirement column (213385, 0.952381, 203224)

Please see attached file. NON-EXCEL help on Growing Annuity Problem In this problem, we will assume all cash flows occur at the BEGINNING OF THE PERIOD (Annuity Due). Therefore, you need to set the calculator to BEGIN. You may want to review the Lecture Video on Growing Annuity for help in working the exam. Problem: Assume you are 32 years old and plan to retire in 35 years at age 67. You are currently earning $75,000/year and expect average annual salary increases of 4.0%/year over the next 35 years. You have $0 saved for retirement. You are trying to determine how much money to save (invest) each year in your 401(k) Plan to fund your retirement in order to pay yourself 70% of your final salary each year (that increases with inflation). [Remember this is an Annuity Due, so your first annual investment is made in Year 0.and your final payment is in Year 34.] You plan to maintain an investment as a percent of your salary, which simply means your payment into the 401(k) will also increase by 4.0% per year as your salary increases 4.0% each year. You believe that you can earn 8.0%/year over the next 35 years while saving for retirement. Once you retire, you have a life expectancy of 25 years. You plan to be more conservative in your investments and expect to earn only 5.0%/year on your investments over the 25 years while in retirement. You also want to maintain your purchasing power by increasing your annual retirement pay by the expected inflation rate of 3.0% each year. [Remember, your first withdrawal will be made in Year 0 of retirement (i.e., Year 35 on the timeline.] Assume that after you withdraw the 25th payment, you will have $0 left in the account. Instructions: - SET CALCULATOR TO BEGIN! - Set up and show your Timeline with payments. You MUST show your work in order to get partial credit. - For this Exam it would be better to handwork the problem and save as a pdf before submitting. - BOX in all your ANSWERS! - You may round all answers to the dollar throughout the problem. - Carry your adjusted interest rates out to 6 decimal places to reduce rounding errors. Questions: [NOTE: there are obviously many steps to get to these answersscoring is below.] 1. What is your final years salary? What is 70% of your salary? [Round to the dollar.] 2. How much will you need in your 401(k) at retirement in order to pay yourself 70% of your final years salary, AND have that payment to yourself increase at 3.0% each year to maintain purchasing power? 3. How much do you need to contribute each year (i.e., save, invest) over the next 35 years in order to fund your retirement. Remember, payment into the 401(k) is increasing by 4.0% each year. Scoring (Do not use Excel): 1. Readability of your Exam. Is it organized, do you show all work, are your answers BOXED in, etc. (10 points) 2. Correctly work problem as an Annuity Due (10 points) 3. Calculate the FV of salary and 70% of salary as first withdrawal from your 401(k) in retirement. (15 points) 4. Do you show a Timeline and is it labeled properly? (15 points) 5. Calculate adjusted interest rate for Retirement Annuity section. (10 points) 6. Calculate the PVA for the Retirement Annuity. (10 points) 7. Calculate the adjusted interest rate for the saving/investing section of problem. (10 points) 8. Calculate the adjusted FVA correctly that is used to determine amount to save each year. (10 points) 9. Calculate the amount you need to save each year; where payment increases as salary increases. (10 points) Expert Answer lalitha lalitha answered thisWas this answer helpful? 0 0 5,370 answers 1.Final years salary 75000*(1+0.04)^35= 295957 70% of salary= 295957*70%= 207170 2. Total Money needed in 401(k) at retirement in order to pay yourself 70% of your final years salary AND have that payment to yourself increase at 3.0% each year to maintain purchasing power is the PV of growing annuity due whose PV is to be found out Using PV of growing annuity-due formula, as below PV of G/A=(Pmt./(r-g))*(1-((1+g)/(1+r))^n)*(1+r) where, pmt.= annual payment into 401(k) r=reqd. return 5% g= growth/inflation rate n= no.of years Applying the values,in the formula, PV at Yr. 35 end/36 beginning=(207170/(5%-3%))*(1-(1.03/1.05)^25)*(1.05) 4151549 3.Amt. needed to be contributed each year (i.e., save, invest) over the next 35 years in order to fund your retirement. Remember, payment into the 401(k) is increasing by 4.0% each year. ie. Future Value of annuity due of 35 nos. year-beginning payments into 401(k) with required return r=8% & growth rate of annual savings , g =4% we have the future value of 35 -beginning -of- the -year savings as $ 4151549 & we need to find the annual savings, pmt.=? Using the Future Value of Growing annuity, which is as below: FV(GA)=Pmt.*(((1+r)^n-(1+g)^n)/(r-g))*(1+r) Applying the values,in the formula, 4151549=Pmt.*(((1+0.08)^35-(1+0.04)^35)/(0.08-0.04))*(1+0.08) Solving the above,we get the annual savings needed to be 14186

70% of final salary's To be saved 207170 Post retirement years Annual Earnings Years 4% PVF @5% 75000 900,82 207170 1.000000 207170 78000 32230 213385 0.952381 203224 81120 67038 219786 0.907029 199353 84365 104580 226380 0.863838 195555 87739 145017 233171 0.822702 191831 91249 188523 240166 0.783526 188177 94899 235276 247371 0.746215 184592 98695 285469 254793 0.710681 181076 102643 339300 262436 0.676839 177627 106748 396981 270309 0.644609 174244 111018 458733 278419 0.613913 170925 11 115459 524791 286771 0.584679 167669 12 120077 595399 295374 0.556837 164476 124881 670816 304236 0.530321 161343 129876 751314 313363 0.505068 158269 135071 837179 15 322764 0.481017 a 155255 140474 928711 o 16 332447 0.458112 152298 146093 1026225 17 342420 0.436297 149397 151936 1130055 18 352692 0.415521 146551 70% of final salary's To be saved 207170 Post retirement years Annual Earnings Years 4% PVF @5% 75000 900,82 207170 1.000000 207170 78000 32230 213385 0.952381 203224 81120 67038 219786 0.907029 199353 84365 104580 226380 0.863838 195555 87739 145017 233171 0.822702 191831 91249 188523 240166 0.783526 188177 94899 235276 247371 0.746215 184592 98695 285469 254793 0.710681 181076 102643 339300 262436 0.676839 177627 106748 396981 270309 0.644609 174244 111018 458733 278419 0.613913 170925 11 115459 524791 286771 0.584679 167669 12 120077 595399 295374 0.556837 164476 124881 670816 304236 0.530321 161343 129876 751314 313363 0.505068 158269 135071 837179 15 322764 0.481017 a 155255 140474 928711 o 16 332447 0.458112 152298 146093 1026225 17 342420 0.436297 149397 151936 1130055 18 352692 0.415521 146551