FIN3403 University of South Florida Time Value of Money Assignment

Anonymous
timer Asked: Apr 2nd, 2019
account_balance_wallet $50

Question Description

Required files are attached.

Purpose

The purpose of this project is to apply some of the concepts covered in the course. Specifically, the project requires you to develop a simple retirement plan. The approach you should take when completing this project is that the only funds you receive at retirement are those you contribute to specific retirement funds—that is, you should assume Social Security will not be available when you retire. In other words, the only funds you will have available at retirement will include amounts you have already accumulated in investments that are specifically designated for retirement and any amounts you accumulate in the future.

Extra Credit Points

Your extra credit project must follow the format contained in the Word template that you must use for your discussions/explanations and to record the results of your computations (http://sbesley.myweb.usf.edu/FIN3403/retire template-sp19.docx); if it does not, your project will not be graded, which means you will receive a grade of zero (0) for the work you submit. In addition, you should closely follow the instructions included in this document to ensure that your project is graded and that you do not lose points for not following instructions.

Prior to starting the extra credit project, it is recommended that you work Time Value of Money—Assignment #1 that is posted on the course website at http://sbesley.myweb.usf.edu/FIN3403/tvm-1.doc. If you understand the two problems given in this assignment, you should be able to complete the extra credit project without much difficulty. You must use a spreadsheet to complete the computations required for the project. The Comprehensive Notes for Chapter 4, which are posted on the course webpage, show how to use the time value of money functions that are available in Excel. The link for the Chapter 4 notes is http://sbesley.myweb.usf.edu/FIN3403/notes/tvm.pdf.

Requirements of the Project

You should already be planning for your retirement. For this reason, the extra credit project is designed to make you think about retirement now by requiring you to develop a simple, yet viable plan for achieving your retirement goals. Following are the requirements that must be included in the project. These requirements must be included in (1) the Word template that is available at http://sbesley.myweb.usf.edu/FIN3403/retire template-sp19.docx and (2) the spreadsheet template that must be used to complete the required computations, which is available at http://sbesley.myweb.usf.edu/FIN3403/retire-sp19.xls.

I.Occupation/Career Expectations. Provide a brief description of what you expect (hope) your occupation(s) will be from the time you graduate from college until you retire. Include in the description the salary range you expect to earn at the various stages of your career. Salary estimates can be obtained from a variety of sources, including USF Career Services and the library, but most of the needed information can be found on the Internet. To search the Internet, use a key phrase that identifies the area in which you expect to work. For example, if you expect to work in the finance area, the key phrase for your search should be either “finance salaries” or “salaries in finance.” Estimate your salary in five-year increments rather than annually—that is, estimate the average annual salary for the first five years of your career, the average annual salary for the second five years of your career, and so forth. When estimating the salaries you expect to earn during your career, you must consider expected inflation rates, percentage raises, and the effects of promotions.

II.Investment Horizon and Life Expectancy. Determine how long you expect to be able to contribute to a retirement fund to meet your retirement goals—that is, the number of years you expect to earn a salary in your chosen career. For example, if you intend to retire at age 65, use the number of years from when you first start your career (upon graduation from USF) until the age of 65. This will constitute your investment horizon, which is the length of time you expect to be able to invest funds to build your retirement “nest egg.” You must also determine the number of years you expect to live after retirement. To estimate this time period, you must complete the following life expectancy quizzes:

The results will appear at the bottom of the page. Below the results you will be provided information about how your lifestyle affects your life expectancy.

The results appear in the upper right portion of the second page of questions.

After providing basic information about yourself (gender and age), click “Submit Values” to go to the next page. The next page will give your life expectancy based on actuarial (insurance) data; you have not finished the life-expectancy quiz; you must provide the “Personal Risk-factors” in the tables that follow on the same page. Provide the information requested in the tables that follow. When finished, click “Submit Values” at the bottom of the page, and your life expectancy based on your personal risk factors will be computed and shown on the next page. After the results are revealed, click “Proceed to Phase I Life-Extension” to see your risk factors and how changes in lifestyle will extend your life. Click “Proceed to Phase IIa Life-Extension”, then click Proceed to Phase IIb Life-Extension,” and finally click “Proceed to Phase IIc Regeneration.” The various screens will indicate how you might be able to extend your actual life expectancy up to 30 years beyond your actuarial life expectancy.

You can access the quizzes by clicking the links given above. If clicking a link does not work, simply copy the link and paste it into the address line of your web browser. The life expectancy quizzes will provide a good estimate as to how long you should expect to live given your current lifestyle. To determine your life expectancy, average the results of the quizzes. You must turn in with your extra credit project a copy of the results for each quiz; so make sure you print the results of each quiz when you complete it. You can use your computer’s print screen function (PrtScn key) to print the results that appear on the last page of each quiz.

III.Risk Preferences. Think about the degree of risk you are willing to take when investing to meet your retirement goals, and then describe your risk preferences. Include in your description a discussion of how you think your risk preferences might change as you progress through your career and get closer to retirement. Part of this process is determining what risks you are willing to take as you build up your retirement fund.

To assess your risk preferences, you must complete the following risk tolerance surveys/quizzes. The results will help you determine your risk tolerance level, as well as the composition of a portfolio of investments that is appropriate for your risk tolerance at this time of your life.

At the end of the questionnaire, you will see your “risk score.” Below the risk score will be a pie chart that shows the recommended allocation of your funds between stocks and bonds.

This test takes about 20 minutes to complete. The results will give you a good idea of how you approach risk in general. Print the final page that contains the results.

You can access the quizzes by clicking the links given above. If clicking a link does not work, simply copy the link and paste it into the address line on your web browser. These risk tolerance quizzes will provide a good estimate of your tolerance for taking risk when investing. Be sure to print the results of each quiz you complete, because you must turn in a copy of the results for each quiz with your extra credit project. You can use your computer’s print screen function (PrtScn key) to print the results of the quizzes.

IV.Retirement Goals. This is the most important part of the project. In this section, you must describe your retirement goals and determine how these goals can be achieved. You must (1) determine the amount of funds you will need at retirement to achieve the retirement goals you establish and (2) how much you should invest each year from graduation until retirement to ensure sufficient funds are available at retirement to meet your goals. In this part of the project you must develop the numerical details of your retirement plan. All computations must be completed using the spreadsheet template that is provided with the extra credit project (link: http://sbesley.myweb.usf.edu/FIN3403/retire-sp19.xls). If you do not use the spreadsheet for the computations, your project will not be graded (that is, you will receive a score of zero for the entire project). For this portion of the project, you must complete the following:

A.Determine the average annual dollar amount that you expect to need during the years you are retired—that is, determine the average amount you want your retirement fund to “pay” you each year during retirement so you can live the lifestyle you desire. You must explain how you determined the annual retirement income (amount) you selected is appropriate for your retirement plan. To estimate your financial needs/wants at retirement, first determine the average dollar amount you would need each year if you were retired today. To estimate the needed retirement income in terms of current dollars, you can talk with persons who are currently retired—that is, ask what their living expenses (housing, health, travel, and so forth) are and what amount of income they need to meet such expenses. After you estimate your retirement needs in terms of current dollars, you must translate the current dollars into dollars for the years (in the future) when you expect to be retired. To do so, you must consider the impact of inflation.

You can estimate future inflation by examining historical inflation rates. You can find information about the Consumer Price Index (CPI) and inflation rates at the following websites:

Once you estimate future inflation, translate your “current retirement dollars” into future dollars (in the year you expect to retire) using the average inflation rate. In other words, when determining your income needs (withdrawals from your retirement fund) during your retirement years, you must consider the impact of inflation.

HINT: The second problem in the Time Value of Money—Assignment #1 that is posted on the course webpage shows how to adjust current values for expected inflation. The link to the problem is: http://sbesley.myweb.usf.edu/FIN3403/tvm-1.doc.

B.Compute the total dollar amount your retirement fund must equal at the time you retire so that you can withdraw the amount you want to collect from your retirement fund each year as determined in Part A of this section. You should assume that the first withdrawal from your retirement account will be on the day you retire. For your computations, use an average rate of return (opportunity cost) for all the years you expect to withdraw funds from your retirement fund. Compute the average rate of return using the information provided at the beginning of Section IV of the spreadsheet template.

C.Any funds you currently have invested for your retirement will decrease the future contributions that must be made to your retirement fund during your career to meet your retirement goals. As a result, if you currently have funds invested for retirement purposes, you must compute how much these funds will be worth (grow to) at the time you expect to retire so that you can determine the additional amount you must contribute to your retirement fund in the future to achieve your goals. For the computations in this section, assume the rate of return these funds earn is the average rate that is determined at the beginning of Section IV in the spreadsheet. If you currently have no funds invested for your retirement and you are 27 years old or under, for this assignment assume you actually have $3,800 already in a retirement fund; if you are older than 27 years, assume you have $6,900 invested in a retirement fund.

D.Compute the annual contributions you must make to a retirement fund during your professional career to ensure your retirement fund accumulates the total amount needed at retirement. In this section, compute the annual contributions you must make during your career so that your retirement fund has the amount you specified in Part B of this section. You must consider the amount you currently have (or are assumed to have) invested in a retirement fund (Computed in Part C), because this amount will decrease the future contributions you must make to accomplish your retirement goals. For your computations, use an average rate of return for all of the years you expect to contribute to your retirement fund as the base-case scenario (see the beginning of Section IV of the spreadsheet template that is required for the project). Compute the contributions that are necessary based on estimates for the average return in three different markets—a normal, or average, market, which is based on the expected returns given in Section IV of the spreadsheet, a lower-than-normal, or below-average, market, and a higher-than-normal, or above-average, market. The return you use for the above-average market should be 3 percent greater than the portfolio return given at the beginning of Section IV in the spreadsheet; the return you use for the below-average market should be 2 percent less than the portfolio return given in Section IV of the spreadsheet.

E.Re-compute the contributions that are needed to accumulate the amount of funds required at retirement (computed in Section B) assuming you wait to begin making contributions to your retirement fund such that the number of payments you make (years) is only 60 percent of the years reported in Section II. In other words, if you expect that you will work for 40 years before retiring, re-compute the required retirement contributions using 24 years—that is, assume you wait 16 years to start contributing to your retirement fund so that you have only 24 years remaining until you retire. For the computation in this portion of the project, follow the instructions given in Part D of this section. Compute the annual contributions that must be made to a retirement fund for the three different markets described in Part D.

F.Re-compute Parts B – D in this section assuming that the amount you withdraw from your retirement fund each year during retirement is (i) $10,000 higher and (ii) $7,000 lower than the amount you established in Part A. For example, if you determined the amount that should be withdrawn from your retirement account each year after you retire is $50,000, (i) increase that amount to $60,000 and (ii) decrease that amount to $43,000 for the computations in this section. For this computation, assume that the number of years you contribute to the retirement fund is the same as you originally determined in Section II. Compute the contributions that would be needed in the three market conditions given in Part D of this section—that is, compute the contributions that would be required in a normal, or average, market, a below-average market, and an above-average market.

G.Discuss the feasibility of your retirement plan (Parts A-E of this section). Do you think the plan is attainable? You should consider the fact that you need some of the income you earn each year during your career to support your existing lifestyle and you must pay taxes. If your plan requires you to invest 80 percent of the annual income you estimated earlier (in Part I) in a retirement fund, then it clearly is not realistic.

H.Find at least two published articles that address the issue of financial literacy in the United States. The articles must have appeared in print or online January 1, 2017 or later, and they should have been published by reputable sources, such as the New York Times, Money, CBS News, Fox News, and so forth. Briefly summarize the articles you choose and discuss actions that you think can be implemented to improve the financial literacy of Americans. Your discussion should be a minimum of 500 words in length (typed and double spaced) and a maximum of 800 words. You must turn in copies of the articles with the project. Make sure you reference (cite) any ideas you use in your discussions that are not your own thoughts.

IMPORTANT! READ CAREFULLY

1.It is a good idea to review the Time Value of Money—Assignment #1 that is posted on the course website at http://sbesley.myweb.usf.edu/FIN3403/tvm-1.doc. If you understand the two problems given in this assignment, you should be able to complete the extra credit project without much difficulty.

2.You should submit only three documents:

a.The Word template. You must use the Word template to provide your answers and discussions/explanations. Simply fill in the requested information on the form. Your project will not be graded (i.e., you will receive a score of zero for the entire project) if any other format is used. The pages that contain the required layout for the project are formatted so that you can easily fill in the spaces with the necessary information/discussions. You can use more space than is provided on the form for your discussions. The Word template is available at http://sbesley.myweb.usf.edu/FIN3403/retire%20template-sp19.docx.

b.The Excel spreadsheet template. You must use the spreadsheet template that has been created for the project to complete your computations. If you do not use the spreadsheet template or follow the instructions given, your project will not be graded (i.e., you will receive a score of zero for the entire project). You must use spreadsheet functions or set up equations in the appropriate cells to solve the computations required in the spreadsheet. Do not plug in any numbers where computations are required or where you should reference a number that is located in another cell in the spreadsheet; otherwise, you will lose valuable points (one point will be deducted for each instance where you enter numbers where either computations are required or you should reference the results from another cell in the spreadsheet). In other words, the appropriate computations should be completed in the spreadsheet, not on your calculator. If you input values (numbers) for all of your answers in the spreadsheet rather than using the appropriate functions or setting up equations to complete the computations, your grade on the project will be zero. The Excel template is available at http://sbesley.myweb.usf.edu/FIN3403/retire-sp19.xls.

c.A Word document or a PDF file that contains (i) the results of the life expectancy quizzes and the risk tolerance quizzes that you completed and (ii) copies of the articles you used to complete Part IV(H) of this project. You can use the print screen (PrtScn) function on your computer to capture an image of the results of the quizzes. You should place all of the results of the quizzes and copies of the articles in one document. If you do not attach copies of these quizzes, you will lose 1/2 point for each quiz that is missing.

Unformatted Attachment Preview

Purpose The purpose of this project is to apply some of the concepts covered in the course. Specifically, the project requires you to develop a simple retirement plan. The approach you should take when completing this project is that the only funds you receive at retirement are those you contribute to specific retirement funds—that is, you should assume Social Security will not be available when you retire. In other words, the only funds you will have available at retirement will include amounts you have already accumulated in investments that are specifically designated for retirement and any amounts you accumulate in the future. Extra Credit Points Your extra credit project must follow the format contained in the Word template that you must use for your discussions/explanations and to record the results of your computations (http://sbesley.myweb.usf.edu/FIN3403/retire template-sp19.docx); if it does not, your project will not be graded, which means you will receive a grade of zero (0) for the work you submit. In addition, you should closely follow the instructions included in this document to ensure that your project is graded and that you do not lose points for not following instructions. Prior to starting the extra credit project, it is recommended that you work Time Value of Money—Assignment #1 that is posted on the course website at http://sbesley.myweb.usf.edu/FIN3403/tvm-1.doc. If you understand the two problems given in this assignment, you should be able to complete the extra credit project without much difficulty. You must use a spreadsheet to complete the computations required for the project. The Comprehensive Notes for Chapter 4, which are posted on the course webpage, show how to use the time value of money functions that are available in Excel. The link for the Chapter 4 notes is http://sbesley.myweb.usf.edu/FIN3403/notes/tvm.pdf. Requirements of the Project You should already be planning for your retirement. For this reason, the extra credit project is designed to make you think about retirement now by requiring you to develop a simple, yet viable plan for achieving your retirement goals. Following are the requirements that must be included in the project. These requirements must be included in (1) the Word template that is available at http://sbesley.myweb.usf.edu/FIN3403/retire template-sp19.docx and (2) the spreadsheet template that must be used to complete the required computations, which is available at http://sbesley.myweb.usf.edu/FIN3403/retire-sp19.xls. I. Occupation/Career Expectations. Provide a brief description of what you expect (hope) your occupation(s) will be from the time you graduate from college until you retire. Include in the description the salary range you expect to earn at the various stages of your career. Salary estimates can be obtained from a variety of sources, including USF Career Services and the library, but most of the needed information can be found on the Internet. To search the Internet, use a key phrase that identifies the area in which you expect to work. For example, if you expect to work in the finance area, the key phrase for your search should be either “finance salaries” or “salaries in finance.” Estimate your salary in five-year increments rather than annually—that is, estimate the average annual salary for the first five years of your career, the average annual salary for the second five years of your career, and so forth. When estimating the salaries you expect to earn during your career, you must consider expected inflation rates, percentage raises, and the effects of promotions. 1 II. Investment Horizon and Life Expectancy. Determine how long you expect to be able to contribute to a retirement fund to meet your retirement goals—that is, the number of years you expect to earn a salary in your chosen career. For example, if you intend to retire at age 65, use the number of years from when you first start your career (upon graduation from USF) until the age of 65. This will constitute your investment horizon, which is the length of time you expect to be able to invest funds to build your retirement “nest egg.” You must also determine the number of years you expect to live after retirement. To estimate this time period, you must complete the following life expectancy quizzes: • University of Connecticut Healthy Life Expectancy Calculator: https://apps.goldensoncenter.uconn.edu/HLEC/ The results will appear at the bottom of the page. Below the results you will be provided information about how your lifestyle affects your life expectancy. • John Hancock Life Expectancy Calculator: https://www.johnhancockinsurance.com/lifeexpectancy-calculator.html# The results appear in the upper right portion of the second page of questions. • A Life-Expectancy Calculation—Foundation for Infinite Survival, Inc.: http://www.fis.org/LE-Calc/index.html After providing basic information about yourself (gender and age), click “Submit Values” to go to the next page. The next page will give your life expectancy based on actuarial (insurance) data; you have not finished the life-expectancy quiz; you must provide the “Personal Risk-factors” in the tables that follow on the same page. Provide the information requested in the tables that follow. When finished, click “Submit Values” at the bottom of the page, and your life expectancy based on your personal risk factors will be computed and shown on the next page. After the results are revealed, click “Proceed to Phase I LifeExtension” to see your risk factors and how changes in lifestyle will extend your life. Click “Proceed to Phase IIa Life-Extension”, then click Proceed to Phase IIb Life-Extension,” and finally click “Proceed to Phase IIc Regeneration.” The various screens will indicate how you might be able to extend your actual life expectancy up to 30 years beyond your actuarial life expectancy. • Poodwaddle Life Clock: http://www.poodwaddle.com/life/. When you finish completing the quiz, print the Summary Report. If you click the tab labeled Full Report, you will be taken to a page that provides information about how to increase your life expectancy. You can access the quizzes by clicking the links given above. If clicking a link does not work, simply copy the link and paste it into the address line of your web browser. The life expectancy quizzes will provide a good estimate as to how long you should expect to live given your current lifestyle. To determine your life expectancy, average the results of the quizzes. You must turn in with your extra credit project a copy of the results for each quiz; so make sure you print the results of each quiz when you complete it. You can use your computer’s print screen function (PrtScn key) to print the results that appear on the last page of each quiz. 2 III. Risk Preferences. Think about the degree of risk you are willing to take when investing to meet your retirement goals, and then describe your risk preferences. Include in your description a discussion of how you think your risk preferences might change as you progress through your career and get closer to retirement. Part of this process is determining what risks you are willing to take as you build up your retirement fund. To assess your risk preferences, you must complete the following risk tolerance surveys/quizzes. The results will help you determine your risk tolerance level, as well as the composition of a portfolio of investments that is appropriate for your risk tolerance at this time of your life. • Hansard International/Fidelity International: https://www.hansard.com/international/fidelity/take-the-questionnaire At the end of the questionnaire, you will see your “risk score.” Below the risk score will be a pie chart that shows the recommended allocation of your funds between stocks and bonds. • Calcxml.com: (1) take the risk tolerance quiz, named “What is my risk tolerance,” which can be accessed at https://www.calcxml.com/do/inv08. Print the page that gives you the results of the quiz; and (2) take the quiz named “How should I allocate my assets,” which can be accessed at http://www.calcxml.com/calculators/inv01 (this link is also given in the area that reports the results for the risk tolerance quiz). Print the page that shows the pie chart with the recommended allocation of investments. • PsychTests: http://testyourself.psychtests.com/testid/2122 This test takes about 20 minutes to complete. The results will give you a good idea of how you approach risk in general. Print the final page that contains the results. • Money-Zine: http://www.money-zine.com/calculators/investment-calculators/asset-allocationcalculator/. The results give an indication of the recommended proportions that should be invested in each type of financial asset. You can access the quizzes by clicking the links given above. If clicking a link does not work, simply copy the link and paste it into the address line on your web browser. These risk tolerance quizzes will provide a good estimate of your tolerance for taking risk when investing. Be sure to print the results of each quiz you complete, because you must turn in a copy of the results for each quiz with your extra credit project. You can use your computer’s print screen function (PrtScn key) to print the results of the quizzes. IV. Retirement Goals. This is the most important part of the project. In this section, you must describe your retirement goals and determine how these goals can be achieved. You must (1) determine the amount of funds you will need at retirement to achieve the retirement goals you establish and (2) how much you should invest each year from graduation until retirement to ensure sufficient funds are available at retirement to meet your goals. In this part of the project you must develop the numerical details of your retirement plan. All computations must be completed using the spreadsheet template that is provided with the extra credit project (link: http://sbesley.myweb.usf.edu/FIN3403/retire-sp19.xls). If you do not use the spreadsheet for the computations, your project will not be graded (that is, you will receive a score of zero for the entire project). For this portion of the project, you must complete the following: 3 A. Determine the average annual dollar amount that you expect to need during the years you are retired—that is, determine the average amount you want your retirement fund to “pay” you each year during retirement so you can live the lifestyle you desire. You must explain how you determined the annual retirement income (amount) you selected is appropriate for your retirement plan. To estimate your financial needs/wants at retirement, first determine the average dollar amount you would need each year if you were retired today. To estimate the needed retirement income in terms of current dollars, you can talk with persons who are currently retired—that is, ask what their living expenses (housing, health, travel, and so forth) are and what amount of income they need to meet such expenses. After you estimate your retirement needs in terms of current dollars, you must translate the current dollars into dollars for the years (in the future) when you expect to be retired. To do so, you must consider the impact of inflation. You can estimate future inflation by examining historical inflation rates. You can find information about the Consumer Price Index (CPI) and inflation rates at the following websites: • http://inflationdata.com/Inflation/Consumer_Price_Index/HistoricalCPI.aspx?reloaded =true • https://data.bls.gov/search/query/results?cx=013738036195919377644%3A6ih0hfrgl5 0&q=consumer+price+index • http://neatideas.com/cpi.htm • http://www.pan.ci.seattle.wa.us/financedepartment/cpi/forecast.htm (Click “Excel File” under Annual U.S. CPI-U to get CPI estimates in spreadsheet form.) Once you estimate future inflation, translate your “current retirement dollars” into future dollars (in the year you expect to retire) using the average inflation rate. In other words, when determining your income needs (withdrawals from your retirement fund) during your retirement years, you must consider the impact of inflation. HINT: The second problem in the Time Value of Money—Assignment #1 that is posted on the course webpage shows how to adjust current values for expected inflation. The link to the problem is: http://sbesley.myweb.usf.edu/FIN3403/tvm-1.doc. B. Compute the total dollar amount your retirement fund must equal at the time you retire so that you can withdraw the amount you want to collect from your retirement fund each year as determined in Part A of this section. You should assume that the first withdrawal from your retirement account will be on the day you retire. For your computations, use an average rate of return (opportunity cost) for all the years you expect to withdraw funds from your retirement fund. Compute the average rate of return using the information provided at the beginning of Section IV of the spreadsheet template. C. Any funds you currently have invested for your retirement will decrease the future contributions that must be made to your retirement fund during your career to meet your retirement goals. As a result, if you currently have funds invested for retirement purposes, you must compute how much these funds will be worth (grow to) at the time you expect to retire so that you can determine the additional amount you must contribute to your retirement fund in the future to achieve your goals. For the computations in this section, 4 assume the rate of return these funds earn is the average rate that is determined at the beginning of Section IV in the spreadsheet. If you currently have no funds invested for your retirement and you are 27 years old or under, for this assignment assume you actually have $3,800 already in a retirement fund; if you are older than 27 years, assume you have $6,900 invested in a retirement fund. D. Compute the annual contributions you must make to a retirement fund during your professional career to ensure your retirement fund accumulates the total amount needed at retirement. In this section, compute the annual contributions you must make during your career so that your retirement fund has the amount you specified in Part B of this section. You must consider the amount you currently have (or are assumed to have) invested in a retirement fund (Computed in Part C), because this amount will decrease the future contributions you must make to accomplish your retirement goals. For your computations, use an average rate of return for all of the years you expect to contribute to your retirement fund as the base-case scenario (see the beginning of Section IV of the spreadsheet template that is required for the project). Compute the contributions that are necessary based on estimates for the average return in three different markets—a normal, or average, market, which is based on the expected returns given in Section IV of the spreadsheet, a lowerthan-normal, or below-average, market, and a higher-than-normal, or above-average, market. The return you use for the above-average market should be 3 percent greater than the portfolio return given at the beginning of Section IV in the spreadsheet; the return you use for the below-average market should be 2 percent less than the portfolio return given in Section IV of the spreadsheet. E. Re-compute the contributions that are needed to accumulate the amount of funds required at retirement (computed in Section B) assuming you wait to begin making contributions to your retirement fund such that the number of payments you make (years) is only 60 percent of the years reported in Section II. In other words, if you expect that you will work for 40 years before retiring, re-compute the required retirement contributions using 24 years—that is, assume you wait 16 years to start contributing to your retirement fund so that you have only 24 years remaining until you retire. For the computation in this portion of the project, follow the instructions given in Part D of this section. Compute the annual contributions that must be made to a retirement fund for the three different markets described in Part D. F. Re-compute Parts B – D in this section assuming that the amount you withdraw from your retirement fund each year during retirement is (i) $10,000 higher and (ii) $7,000 lower than the amount you established in Part A. For example, if you determined the amount that should be withdrawn from your retirement account each year after you retire is $50,000, (i) increase that amount to $60,000 and (ii) decrease that amount to $43,000 for the computations in this section. For this computation, assume that the number of years you contribute to the retirement fund is the same as you originally determined in Section II. Compute the contributions that would be needed in the three market conditions given in Part D of this section—that is, compute the contributions that would be required in a normal, or average, market, a below-average market, and an above-average market. G. Discuss the feasibility of your retirement plan (Parts A-E of this section). Do you think the plan is attainable? You should consider the fact that you need some of the income you earn each year during your career to support your existing lifestyle and you must pay taxes. If 5 your plan requires you to invest 80 percent of the annual income you estimated earlier (in Part I) in a retirement fund, then it clearly is not realistic. H. Find at least two published articles that address the issue of financial literacy in the United States. The articles must have appeared in print or online January 1, 2017 or later, and they should have been published by reputable sources, such as the New York Times, Money, CBS News, Fox News, and so forth. Briefly summarize the articles you choose and discuss actions that you think can be implemented to improve the financial literacy of Americans. Your discussion should be a minimum of 500 words in length (typed and double spaced) and a maximum of 800 words. You must turn in copies of the articles with the project. Make sure you reference (cite) any ideas you use in your discussions that are not your own thoughts. . 6 IMPORTANT! READ CAREFULLY 1. It is a good idea to review the Time Value of Money—Assignment #1 that is posted on the course website at http://sbesley.myweb.usf.edu/FIN3403/tvm-1.doc. If you understand the two problems given in this assignment, you should be able to complete the extra credit project without much difficulty. 2. You should submit only three documents: a. The Word template. You must use the Word template to provide your answers and discussions/explanations. Simply fill in the requested information on the form. Your project will not be graded (i.e., you will receive a score of zero for the entire project) if any other format is used. The pages that contain the required layout for the project are formatted so that you can easily fill in the spaces with the necessary information/discussions. You can use more space than is provided on the form for your discussions. The Word template is available at http://sbesley.myweb.usf.edu/FIN3403/retire%20template-sp19.docx. b. The Excel spreadsheet template. You must use the spreadsheet template that has been created for the project to complete your computations. If you do not use the spreadsheet template or follow the instructions given, your project will not be graded (i.e., you will receive a score of zero for the entire project). You must use spreadsheet functions or set up equations in the appropriate cells to solve the computations required in the spreadsheet. Do not plug in any numbers where computations are required or where you should reference a number that is located in another cell in ...
Purchase answer to see full attachment

Tutor Answer

MurphyW
School: Carnegie Mellon University

Hello,Attached find the completed work together with the plagiarism report. Go through it and in case you need any corrections kindly let me know.All the best.

Principles of Finance
Extra Credit Project
Spring 2019

f18

Name:
Student ID: U
Section:
Where a cell is highlighted in yellow , you must set up the relationships--that is, equations or spreadsheet functions--to compute the
required values. For instance, you can use the PV function that is built into the spreadsheet to compute present value where it is required,
or you can input the appropriate equation to complete the computation. Do not enter values (numbers) 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 relationships. For example, to compute the "% Change" in salary for the period "6-10" in
Section I, the relationship entered into cell C16 should be (C15-B15)/B15. Instructions as to what needs to be computed in the yellowhighlighted cells are given in the cells that are highlighted in blue . A cell that is highlighted in tan indicates that a number from another
location (i.e., another cell) in the spreadsheet will automatically be entered into that cell.

Section I: Salary Expectations
1-5
Salary:
% Change:

6-10

Period (Years) After Starting Your Career
11-15
16-20
21-25
26-30
31-35

$85,000 $100,000 $112,000
17.65%
12.00%

$123,000
9.82%

36-40
$135,000 $180,000 $198,000 $223,000
9.76%
33.33%
10.00%
12.63%

41-45
$255,000
14.35%

Enter the salaries that you expect to earn during each 5-year period that is applicable to your career expectations. Then, create the relationship
(equation) necessary to compute the percentage change in salary from one period to the next for each 5-year period.

Section II: Life Expectancy
Current age:
Retirement age:
Life expectancy age:

21
65
93.8

Years to retirement:
Years of retirement:

44
28.8

Enter the requested age information in the blank cells. Your "Life expectancy age" should be based on the results of the life-expectancy quizzes that
you took. Set up the appropriate relationships (equations) to compute the values requested in the cells highlighted in yellow.

Section III: Risk Preferences
You must take the risk tolerance quizzes listed in the general instructions for the Extra Credit Project. Indicate the proportion of your
retirement funds that you believe should be invested in each of the following types of investments:

Money market:
Bonds (long-term):
Stocks--domestic:
Stocks--foreign:

Percent
(Weight)
0.00%
60.00%
28.00%
12.00%

If the total is not equal to 100.0%, change the weights until it does. The weights should be based on

Total: 100.00% the results of the risk tolerance quizzes that you completed.
Section IV: Retirement Goals
The expected returns for the following types of investments:

Money market:
Bonds (long-term):
Stocks--domestic:
Stocks--foreign
Total:

Percent
(Weight)
0.00%
60.00%
28.00%
12.00%
100.00%

Expected
Rate
2.2%
4.8%
11.5%
9.0%

Portfolio
Rate
2.00%
6.00%
11.80%
8.00%

Set up the relationships that are required to compute the weighted average expected rate of return for the portfolio.

A. Retirement income
Average annual income during retirement --stated in today's dollars:
Average annual inflation rate during your career :
Years to retirement:
Average annual income during retirement --stated in inflation-adjusted dollars:

$46,000
60.00%
44
$73,600

Set up the relationship required to determine the inflation-adjusted average annual income you wish to receive during your retirement
years. You can use the FV function built into the spreadsheet for this computation. The value in cell K55 will be the same as the value
shown in cell G22 in Section II. This value will automatically be entered by the spreadsheet when you set up the compuation for the cell
G22. The values in cells K53-K55 must be used to compute the answer in cell K56.

B. Amount needed in your retirement fund when you retire
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 retirement goals:

$73,600
12.00%
28.8
$2,119,680

Set up the relationship required to determine the total amount to which your retirement fund must grow during the years you expect to
make contributions (when you are working). You can use the PV function built into the spreadsheet for this computation. The values in
cells K61-K63 will automatically be entered by the spreadsheet from the relationships you created earlier. These values must be used
to compute the answer in cell K64.

C. Value of current "retirement" investments at retirement (If you do not have an existing retirement fund, input either $3,800
or $6,900 in cell K69 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):

$12,000
8.00%
44
$2,119,680

Value of current (existing) retirement funds at the time you retire:
Additional amount needed at retirement to meet your retirement needs:

$354,672
$1,765,008

Set up the relationship required to determine how much any funds that you currently have invested for retirement will be when you
retire if these funds are invested at the average rate computed in cell H48. The values in cells K70-K72 will automatically be entered by
the spreadsheet from the relationships that you created earlier. The values in cells K69-K72 must be used to compute the answer in
cell K73, and the value in cell K73 must be used to compute the answer in cell K74.

D. Required annual contributions to your retirement 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.:
Years to retirement:
Normal, or average, market conditions:
Expected rate of return on your retirement fund in an average market :
Annual contributions requ...

flag Report DMCA
Review

Anonymous
Excellent job

Similar Questions
Related Tags

Brown University





1271 Tutors

California Institute of Technology




2131 Tutors

Carnegie Mellon University




982 Tutors

Columbia University





1256 Tutors

Dartmouth University





2113 Tutors

Emory University





2279 Tutors

Harvard University





599 Tutors

Massachusetts Institute of Technology



2319 Tutors

New York University





1645 Tutors

Notre Dam University





1911 Tutors

Oklahoma University





2122 Tutors

Pennsylvania State University





932 Tutors

Princeton University





1211 Tutors

Stanford University





983 Tutors

University of California





1282 Tutors

Oxford University





123 Tutors

Yale University





2325 Tutors