G22 needs a subtracting formula put into place so 64-21 and for g23 93-64

retire-sp19.xls - Compatibility Mo me Insert Page Layout Formulas Data Review View Arial Wrap Text Percentage ste Office Update To keep up-to-date with security updates, fixes, and improvements, choose Check for Updates Where a cell is highlighted in yellow, you must set up the relat values. For instance, you can use the PV function that is bullt into the spreadsheet to compute present value where it is required, or you can input the appropriate equation to completo the computation. Do not enter values (aumbers) in these cells. The computations should be based on information provided in other cells-that is, you should refer to the locations of the cells that contain the appropriate information when creating the computational rela Sonships. For example, to compute the-% Change" in salary for the period "6-10. in Section l, the relationship entered into cell C16 should be (C15-815yB15. Instructions as to what needs to be computed in the yelow-highilighted cells are given in the cells that are highlighted in blue. A cell that is highlighted in tan indicates thata number from another location (Le..nother ce the spreadsheet wir automatically be entered into that cel. ons hips-tha spreadsheet fnction. compute the required 1 Section I: Salary Expectations 552.500 $53.000 60.09 15 Salary: $43,000 $45.000 161 % change. Enter the salaries that you opect to earn duong each 5.ear Panog 2atappkable to your a er expectatons men oease se eatenine lequation) necesenry to compute the percentage change salany tom one pd lo theet f ees yar re 2e Section li Life Expectancy Current age Retrement age Years to rotirement Years of retirement 93 Enter threquested age ntommation the blank oels Your "Lhe "spectancy age. should b. based on the maurs a, me reexpectancy quires that 24 fook. Set up the apprepriate relatonships (equations) to compute the values requested in the cels highighted in yolbw 26 Saction IlIl: Risk Preferences You must take the risk tolerance quirzes isted in the general ins tructions for the Extra Creair Project Indlcate the proportion of your ze funds that you believe sthould be invested in each of the following types of nvestments Meey market 1-40 00% Bonds (long-serm) Stocks." on este-2002% Stocks foreign:50s "the total is not equal to 1000%, change the weghts unt" do. The weight, ahou"be ba- esuts of the rsk orance quirres that you completed PSheet + retire-sp19.xls-Compatibility M me Insert Page Layout Formulas Data Review View dbArial Wrap Text Currency . 'E -Merge & Center , $.96 , ffice Update To keep up-to-date with security updates, fixes, and improvements, choose Check for Updates ection IV: Retirement Goals he expected returns for the following types of investments: Rate Money market 00% Bonds (long-term)35 Stocks domestic Tota 100 Set up the relaionships mat are requined to compute the weighted average exspected rate of retum for the portfolo A. Retirement income Average annual income during retirement-stated in today's dolars Average annual inflation rate during your career Years to retirement Average annual Income during retiroment -stated in inflation-adjusted dolars up the reiationship required to detemmine he inflation-adjusted avernge annual noome you wish to receive duning your retirement years. You can use the FV function builtl into the spreadsheet for this computation The value in cel K55 wll be the same as the vabe shown in cel G22 in Section II. This value will automaticaly be antered by the spreadsheet when you set up me compuation for the cef G22. The values in cells K53-K55 must be used to compute the answer in col K58 B. Amount needed in your retirement fund when you retre Average annual income during retirement stated in inflation-adjusted dollars Weighted average expected rate of return on your retirement fund: Years of retirement (how long you expect to be retired) Total amount neeeded at retirement to meet your retrement goals Set up the relationship equired to detemine the total amount to which your retimement fund must grow during the years you espect to make contnbutions (when you are workingl You can use the PV function built into the spreadsheet for this computation The values in cels K61-K63 automaficaly be entered by the spreadsheet from the relationships you created eader These valves must be wsed to compute the answer in ce K64 C. Value of current "retirement" Investments at retirement (If you do not have an existing retirement fund, input ether $3,800 or $6,900 in col KB9 ad com ete rhe rest ofths section See the rtructons in the werd document to determine whother S3 shouid be entered) cr sesoo Sheet1 retire-sp19.xls - Compatibility Mode Insert Page Layout Formulas Data Review View Arial 10 AA Wrap Text Currency ..- .- .. Merge & Center. $ 96 , Update To keep up-to-date with security updates, fixes, and improvements, choose Check for Updates C. Value of current retrement" investments at retirement (If you do not have an existing retirement fund, input ether $3,800 or $8,900 in cel KB9 and complete the rest of this section. See the intructions in the Word document to determine whether $3,800 or $6,900 should be entered) Present (current) value of any retirement funds that you currently have: Expected rate of return on your retirement fund in an average market Years to retirement Total amount neeeded at retirement (in all retirement funds) Value of current (existing) retirement funds at the time you retire: Additional amount needed at retirement to meet your retirement needs Set up the relationship required to detemine how much any funds that you cumently have invested for metivement wil be when you retive r these funds are invested at the average rate compufed in ce H48. The values in cels KroK72 wr automacaly be enternd by me spreadsheet from the relationships that you created earer The values in cels K6s-472 must be used to compute ihe answer in celS and the value in cel K73 must be used to compute the answer in cel K74 D. Required annual contributions to your redirement fund Amount greater than existing funds needed at retirement to meet your needs: Use whatever value is given in cell K80 for the computations in the remainder of this section. The value given in K80 will differ from the value in K79 if the value in K79 is less than $600,000 Normal, or average, market condtions Expected rate of return on your retirement fund in an average market Annual contributions required to accumulate amount needed at retirement up the relationship required to detemine the amount that must be contrbuted to your retirement fund each year to a total amount tha ndo the spreadsheet for values in cels K79-J81 and K84 wil automaicaly be entered by the speadaheet. cell K85. For your computation, you must use the amount given in cel K8d, which These vakves must be used to compute the answer o Expected rate of return on your retirement fund in an above-average market Annual contrbutions required to accumulate amount needed at cef K89 that is 3.0 percent geater t than the average xpected ehon Folow the same procedue as above, except enter a rate of sehum in at me beginning of shis section. The values an used to cels K80-K81 and e9 must now Sheeti retire-sp19.xls-Compatibility Mode Insert Page Layout Formulas Data Review View Arial Wrap Text fice Update To keep up-to-date with security updates, fixes, and improvements, choose Check for Updates Below-average market conditions Expected rate of return on your retirement fund in a below-average market Annual contrbutions required to accumulate amount needed at retirement Follow the same procedure used to compute the contributions in abovemarket conditions, except enter a rate of relum in cell K98 that a 2.0 percent iess than the average expected refum computed at the beginning of this section. The values in cells Kao-k81 and K96 must now be used to compute the answer in cel K97 E. Re-compute the contributions that are needed to accumulate the amount of funds required af retirement (computed in Section B) assuming you wait to begin making contrbutions to your rotrement fund such thar the number of payments you make (years) is anly 50 percent of the years reperted in Section II. In other words, you expect that you will work for 40 years before retining, re-compute the required redirement contrbutions using 24 years-that s, assume you wal 16 years to start contributing to your retrement fund sothat you have only 24 y s remanng ure you rw. For the computation in this portion ofth0 preyot ek w the instructons gren Part D of this sector. Computo te annual contrb dons that must be mode to a retrernent tnd fr the troo dnorent markets described in Section IV, Part D n this section, you must perfom me same computation as you did in Pat D except the contabutions to the eihement fund will be for a drferent number of years Enter the "adjusted years', cee K107 Fokw th@ nstwtions gven i, Part D of this section so set up the ded relationship in cel K111 Use whatever value is given in cell K106 for the computations in the remainder of ti section. The value glven in K106 will differ from the value in K79 the value in K79 is kss than 3600,000 Adjusted" years to retirement (see descrpion above market conditions Expected rabe of return on your retirement fund in an average market Annual contrbutions required to aceumuate amount neesed at retrement Set up the eiationshp requred to delemine the amount mat must be contrbuted to your retirement und aach year to aooble ne total amount that is needed in the fund when you etie You can use the PMT unction built indo me spredsheet for thia compufaten. The value s cels K106 and K, 10 wg autoratcaly be entered by ine spreadshoet Th. eauesthese aa end he wasn you not ener K107 must be used to compute he answer cel K11f Above-average market condtions Expected rate of return on your retirement fund in an above-average market Aiual contrbutions required to accumdate amount needed at retrment Foow the same procedure as above, escept enter a rate of atum in cel K11S het is 3.0 pecent geater than the avernge axpected retire-sp19.xls - Compatibility Mode sert Page Layout Formulas Data Review View Wrap Text Percentage Arial 10A A date To keep up-to-date with security updates, fixes, and improvements, choose Check for Updates Normal, or average, market conditions: 000% Expected rate of return on your retirement fund in an average marketA Annual contrbutions required to accumulate amount needed at retirement Set up the relationship required to detemmine the amount that must be contnouted to your retiement fund each year to accumulate total amount that is needed in the fund when you retire. You can use th values in cels K108 and K110 wll automaticaly be entered by the spreadsheet. The K 107 must be used to compute the answer i, cor K1 1 1 e PMIT fubction buit into the spreadsheet for this computation. The values rese cels and the value you nputin cel Above-average market conditions Expected rate of return on your retirement fund in an above-average market Annual contributions required to accumulate amount needed at redirement cel K115 that is 3.0 percent greader than the average expected the same procedure as above, except enter a rate of retum in g of this section. The values in cols K108, K107, and K115 must now be used to compute the answer in section. retum computed at the beginning cel K116 Below-average market conditions Expected rate of return on your retirement fund in a below-average market Annual contributions required to accumulate amount needed at retirement cef K122 that a Follow the same procedure used to compute the contrbutions in above-market condtions, except enter a rate of efum in expected retum computed at the beginning of this section. The values in cels K106, K107, and K122 must now be used to compute the answer in cell K123 F. Re-compute Parts B- D in this section assuming that the amount you withdraw from your retirement funds each year during retrement is ( $10,000 higher and (0) $7,000 kower than the amount you established in Part A of this section. For this computation assume hat the number of years you contrbute to the redirement Kand is the same as you originaly determined n Secton i B-D of this section, except the withrawal from me in this section, you must perfom the same computations as you did in Parts fund wil be $10,000 greater and 00$7,000 lower than you detemined in Pat A of this section yoursef to compute (a) the amount the fund mutt egual at "tremont and the contributions to the retrement fund 0u mutar ser up spreadsheet nat exiat-that is, average macket, above-average mackat, and belowaverage market To set up the Parts A-D fomat, you can insent a new worksheet and copy the worksheet you used to pesfom the requred computationa for Sheet1 retire-sp19.xls - Compatibility Mo me Insert Page Layout Formulas Data Review View Arial Wrap Text Percentage ste Office Update To keep up-to-date with security updates, fixes, and improvements, choose Check for Updates Where a cell is highlighted in yellow, you must set up the relat values. For instance, you can use the PV function that is bullt into the spreadsheet to compute present value where it is required, or you can input the appropriate equation to completo the computation. Do not enter values (aumbers) in these cells. The computations should be based on information provided in other cells-that is, you should refer to the locations of the cells that contain the appropriate information when creating the computational rela Sonships. For example, to compute the-% Change" in salary for the period "6-10. in Section l, the relationship entered into cell C16 should be (C15-815yB15. Instructions as to what needs to be computed in the yelow-highilighted cells are given in the cells that are highlighted in blue. A cell that is highlighted in tan indicates thata number from another location (Le..nother ce the spreadsheet wir automatically be entered into that cel. ons hips-tha spreadsheet fnction. compute the required 1 Section I: Salary Expectations 552.500 $53.000 60.09 15 Salary: $43,000 $45.000 161 % change. Enter the salaries that you opect to earn duong each 5.ear Panog 2atappkable to your a er expectatons men oease se eatenine lequation) necesenry to compute the percentage change salany tom one pd lo theet f ees yar re 2e Section li Life Expectancy Current age Retrement age Years to rotirement Years of retirement 93 Enter threquested age ntommation the blank oels Your "Lhe "spectancy age. should b. based on the maurs a, me reexpectancy quires that 24 fook. Set up the apprepriate relatonships (equations) to compute the values requested in the cels highighted in yolbw 26 Saction IlIl: Risk Preferences You must take the risk tolerance quirzes isted in the general ins tructions for the Extra Creair Project Indlcate the proportion of your ze funds that you believe sthould be invested in each of the following types of nvestments Meey market 1-40 00% Bonds (long-serm) Stocks." on este-2002% Stocks foreign:50s "the total is not equal to 1000%, change the weghts unt" do. The weight, ahou"be ba- esuts of the rsk orance quirres that you completed PSheet + retire-sp19.xls-Compatibility M me Insert Page Layout Formulas Data Review View dbArial Wrap Text Currency . 'E -Merge & Center , $.96 , ffice Update To keep up-to-date with security updates, fixes, and improvements, choose Check for Updates ection IV: Retirement Goals he expected returns for the following types of investments: Rate Money market 00% Bonds (long-term)35 Stocks domestic Tota 100 Set up the relaionships mat are requined to compute the weighted average exspected rate of retum for the portfolo A. Retirement income Average annual income during retirement-stated in today's dolars Average annual inflation rate during your career Years to retirement Average annual Income during retiroment -stated in inflation-adjusted dolars up the reiationship required to detemmine he inflation-adjusted avernge annual noome you wish to receive duning your retirement years. You can use the FV function builtl into the spreadsheet for this computation The value in cel K55 wll be the same as the vabe shown in cel G22 in Section II. This value will automaticaly be antered by the spreadsheet when you set up me compuation for the cef G22. The values in cells K53-K55 must be used to compute the answer in col K58 B. Amount needed in your retirement fund when you retre Average annual income during retirement stated in inflation-adjusted dollars Weighted average expected rate of return on your retirement fund: Years of retirement (how long you expect to be retired) Total amount neeeded at retirement to meet your retrement goals Set up the relationship equired to detemine the total amount to which your retimement fund must grow during the years you espect to make contnbutions (when you are workingl You can use the PV function built into the spreadsheet for this computation The values in cels K61-K63 automaficaly be entered by the spreadsheet from the relationships you created eader These valves must be wsed to compute the answer in ce K64 C. Value of current "retirement" Investments at retirement (If you do not have an existing retirement fund, input ether $3,800 or $6,900 in col KB9 ad com ete rhe rest ofths section See the rtructons in the werd document to determine whother S3 shouid be entered) cr sesoo Sheet1 retire-sp19.xls - Compatibility Mode Insert Page Layout Formulas Data Review View Arial 10 AA Wrap Text Currency ..- .- .. Merge & Center. $ 96 , Update To keep up-to-date with security updates, fixes, and improvements, choose Check for Updates C. Value of current retrement" investments at retirement (If you do not have an existing retirement fund, input ether $3,800 or $8,900 in cel KB9 and complete the rest of this section. See the intructions in the Word document to determine whether $3,800 or $6,900 should be entered) Present (current) value of any retirement funds that you currently have: Expected rate of return on your retirement fund in an average market Years to retirement Total amount neeeded at retirement (in all retirement funds) Value of current (existing) retirement funds at the time you retire: Additional amount needed at retirement to meet your retirement needs Set up the relationship required to detemine how much any funds that you cumently have invested for metivement wil be when you retive r these funds are invested at the average rate compufed in ce H48. The values in cels KroK72 wr automacaly be enternd by me spreadsheet from the relationships that you created earer The values in cels K6s-472 must be used to compute ihe answer in celS and the value in cel K73 must be used to compute the answer in cel K74 D. Required annual contributions to your redirement fund Amount greater than existing funds needed at retirement to meet your needs: Use whatever value is given in cell K80 for the computations in the remainder of this section. The value given in K80 will differ from the value in K79 if the value in K79 is less than $600,000 Normal, or average, market condtions Expected rate of return on your retirement fund in an average market Annual contributions required to accumulate amount needed at retirement up the relationship required to detemine the amount that must be contrbuted to your retirement fund each year to a total amount tha ndo the spreadsheet for values in cels K79-J81 and K84 wil automaicaly be entered by the speadaheet. cell K85. For your computation, you must use the amount given in cel K8d, which These vakves must be used to compute the answer o Expected rate of return on your retirement fund in an above-average market Annual contrbutions required to accumulate amount needed at cef K89 that is 3.0 percent geater t than the average xpected ehon Folow the same procedue as above, except enter a rate of sehum in at me beginning of shis section. The values an used to cels K80-K81 and e9 must now Sheeti retire-sp19.xls-Compatibility Mode Insert Page Layout Formulas Data Review View Arial Wrap Text fice Update To keep up-to-date with security updates, fixes, and improvements, choose Check for Updates Below-average market conditions Expected rate of return on your retirement fund in a below-average market Annual contrbutions required to accumulate amount needed at retirement Follow the same procedure used to compute the contributions in abovemarket conditions, except enter a rate of relum in cell K98 that a 2.0 percent iess than the average expected refum computed at the beginning of this section. The values in cells Kao-k81 and K96 must now be used to compute the answer in cel K97 E. Re-compute the contributions that are needed to accumulate the amount of funds required af retirement (computed in Section B) assuming you wait to begin making contrbutions to your rotrement fund such thar the number of payments you make (years) is anly 50 percent of the years reperted in Section II. In other words, you expect that you will work for 40 years before retining, re-compute the required redirement contrbutions using 24 years-that s, assume you wal 16 years to start contributing to your retrement fund sothat you have only 24 y s remanng ure you rw. For the computation in this portion ofth0 preyot ek w the instructons gren Part D of this sector. Computo te annual contrb dons that must be mode to a retrernent tnd fr the troo dnorent markets described in Section IV, Part D n this section, you must perfom me same computation as you did in Pat D except the contabutions to the eihement fund will be for a drferent number of years Enter the "adjusted years', cee K107 Fokw th@ nstwtions gven i, Part D of this section so set up the ded relationship in cel K111 Use whatever value is given in cell K106 for the computations in the remainder of ti section. The value glven in K106 will differ from the value in K79 the value in K79 is kss than 3600,000 Adjusted" years to retirement (see descrpion above market conditions Expected rabe of return on your retirement fund in an average market Annual contrbutions required to aceumuate amount neesed at retrement Set up the eiationshp requred to delemine the amount mat must be contrbuted to your retirement und aach year to aooble ne total amount that is needed in the fund when you etie You can use the PMT unction built indo me spredsheet for thia compufaten. The value s cels K106 and K, 10 wg autoratcaly be entered by ine spreadshoet Th. eauesthese aa end he wasn you not ener K107 must be used to compute he answer cel K11f Above-average market condtions Expected rate of return on your retirement fund in an above-average market Aiual contrbutions required to accumdate amount needed at retrment Foow the same procedure as above, escept enter a rate of atum in cel K11S het is 3.0 pecent geater than the avernge axpected retire-sp19.xls - Compatibility Mode sert Page Layout Formulas Data Review View Wrap Text Percentage Arial 10A A date To keep up-to-date with security updates, fixes, and improvements, choose Check for Updates Normal, or average, market conditions: 000% Expected rate of return on your retirement fund in an average marketA Annual contrbutions required to accumulate amount needed at retirement Set up the relationship required to detemmine the amount that must be contnouted to your retiement fund each year to accumulate total amount that is needed in the fund when you retire. You can use th values in cels K108 and K110 wll automaticaly be entered by the spreadsheet. The K 107 must be used to compute the answer i, cor K1 1 1 e PMIT fubction buit into the spreadsheet for this computation. The values rese cels and the value you nputin cel Above-average market conditions Expected rate of return on your retirement fund in an above-average market Annual contributions required to accumulate amount needed at redirement cel K115 that is 3.0 percent greader than the average expected the same procedure as above, except enter a rate of retum in g of this section. The values in cols K108, K107, and K115 must now be used to compute the answer in section. retum computed at the beginning cel K116 Below-average market conditions Expected rate of return on your retirement fund in a below-average market Annual contributions required to accumulate amount needed at retirement cef K122 that a Follow the same procedure used to compute the contrbutions in above-market condtions, except enter a rate of efum in expected retum computed at the beginning of this section. The values in cels K106, K107, and K122 must now be used to compute the answer in cell K123 F. Re-compute Parts B- D in this section assuming that the amount you withdraw from your retirement funds each year during retrement is ( $10,000 higher and (0) $7,000 kower than the amount you established in Part A of this section. For this computation assume hat the number of years you contrbute to the redirement Kand is the same as you originaly determined n Secton i B-D of this section, except the withrawal from me in this section, you must perfom the same computations as you did in Parts fund wil be $10,000 greater and 00$7,000 lower than you detemined in Pat A of this section yoursef to compute (a) the amount the fund mutt egual at "tremont and the contributions to the retrement fund 0u mutar ser up spreadsheet nat exiat-that is, average macket, above-average mackat, and belowaverage market To set up the Parts A-D fomat, you can insent a new worksheet and copy the worksheet you used to pesfom the requred computationa for Sheet1