Follow the instructions required in the attached file that would guide in completing the assignment
retire_sp20_3.xlsx

retire_template_sp20.docx

Never use plagiarized sources. Get Your Original Essay on
This a principle of finance subject
Hire Professionals Just from $11/Page
Order Now Click here

retire_sp20.pdf

Unformatted Attachment Preview

Principles of Finance
Extra Credit Project
Spring 2020
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
6-10
Period (Years) After Starting Your Career
11-15
16-20
21-25
26-30
31-35
36-40
41-45
Salary:
% Change:
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:
Years to retirement:
Years of retirement:
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:
Percent
(Weight)
Money market:
Bonds (long-term):
Stocks–domestic:
Stocks–foreign:
Total:
If the total is not equal to 100.0%, change the weights until it does. The weights should be based on
0,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%
0,00%
0,00%
0,00%
0,00%
Expected
Rate
2,5%
6,2%
9,7%
11,5%
Portfolio
Rate
Portfolio Return
Set up the relationships that are required to compute the weighted average expected rate of return for the portfolio.
A. Retirement income
Annual retirement expenses stated in today’s dollars–that is, the amounts you would need if you were retired today. Use
reliable Internet sources to estimate your expenses/needs.
Household expenses
Healthcare
Food
Clothing
Transportation
Entertainment
Other expenses/items
Total
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:
$0
0
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 K63 will be the same as value in cell
K61 and the value in cell K65 will be the same as the value shown in cell G22 in Section II. These values will automatically be entered
by the spreadsheet after you set up the computation for the cells G22 and K61. The values in cells K63-K65 must be used to compute
the answer in cell K66.
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 needed at retirement to meet your retirement goals:
$0
0,00%
0
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 K71 – K73 will automatically be entered by the spreadsheet from the relationships you created earlier. These values must be used
to compute the answer in cell K74.
C. Value of current “retirement” investments at retirement (If you do not have an existing retirement fund, input either $5,800
or $8,200 in cell K79 and complete the rest of this section. See the instructions in the Word document to determine whether
$5,800 or $8,200 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 needed 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:
0,00%
0
$0
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 K80 – K82 will automatically be entered
by the spreadsheet from the relationships that you created earlier. The values in cells K79 – K82 must be used to compute the answer
in cell K83, and the value in cell K83 must be used to compute the answer in cell K84.
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 K90 for the computations in the remainder of
this section. The value given in K90 will differ from the value in K89 if the value in
K89 is less than $500,000.:
Years to retirement:
Normal, or average, market conditions:
Expected rate of return on your retirement fund in an average market :
Annual contributions required to accumulate amount needed at retirement:
$0
$650.000
0
0,00%
Set up the relationship required to determine the amount that must be contributed to your retirement fund each year to accumulate the
total amount that is needed in the fund when you retire. You can use the PMT function built into the spreadsheet for this computation.
The values in cells K90, K91, and K94 will automatically be entered by the spreadsheet. These values must be used to compute the
answer in cell K95. For your computation, you must use the amount given in cell K90.
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 retirement:
Follow the same procedure as above, except enter a rate of return in cell K99 that is 2.6 percent greater than the average expected
return computed at the beginning of this section. The values in cells K90, K91, and K99 must now be used to compute the answer in
cell K100.
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:
Follow the same procedure used to compute the contributions in above-market conditions, except enter a rate of return in cell K106 that
is 1.4 percent less than the average expected return computed at the beginning of this section. The values in cells K90, K91, and K106
must now be used to compute the answer in cell K107.
E. Recompute 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 50 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 20 years—that is, assume you wait 20 years to start
contributing to your retirement fund so that you have only 20 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.
In this section, you must perform the same computation as you did in Part D, except the contributions to the retirement fund will be for a
different number of years. Enter the “adjusted years” in cell K117. Follow the instructions given in Part D of this section to set up the
needed relationship in cell K121.
Use whatever value is given in cell K116 for the computations in the remainder
of this section. The value given in K116 will differ from the value in K89 if the value
in K89 is less than $400,000.:
“Adjusted” years to retirement (see description above):
Normal, or average, market conditions:
Expected rate of return on your retirement fund in an average market :
Annual contributions required to accumulate amount needed at retirement:
$500.000
0,00%
Set up the relationship required to determine the amount that must be contributed to your retirement fund each year to accumulate the
total amount that is needed in the fund when you retire. You can use the PMT function built into the spreadsheet for this computation.
The values in cells K116 and K120 will automatically be entered by the spreadsheet; the values in these cells and the value you input in
cell K117 must be used to compute the answer in cell K121.
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 retirement:
Follow the same procedure as above, except enter a rate of return in cell K125 that is 2.6 percent greater than the average expected
return computed at the beginning of this section. The values in cells K116, K117, and K125 must now be used to compute the answer
in cell K126.
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:
Follow the same procedure used to compute the contributions in above-market conditions, except enter a rate of return in cell K132 that
is 1.4 percent less than the average expected return computed at the beginning of this section. The values in cells K116, K117, and
K132 must now be used to compute the answer in cell K133.
Follow the same procedure used to compute the contributions in above-market conditions, except enter a rate of return in cell K132 that
is 1.4 percent less than the average expected return computed at the beginning of this section. The values in cells K116, K117, and
K132 must now be used to compute the answer in cell K133.
F. Recompute Parts B – D in this section assuming that the amount you withdraw from your retirement funds each year during
retirement is (i) $11,500 higher and (ii) $7,500 lower than the amount you established in Part A of 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.
In this section, you must perform the same computations as you did in Parts B – D of this section, except the withdrawal from the
retirement fund will be (i) $11,500 greater and (ii) $7,500 lower than you determined in Part A of this section. You must set up the
spreadsheet format yourself to compute (a) the amount the fund must equal at retirement and (b) the contributions to the retirement
fund that are required when each of the three market conditions exist–that is, average market, above-average market, and belowaverage market. To set up the format, you can insert a new worksheet and copy the worksheet you used to perform the required
computations for Parts A – D .
REQUIREMENTS AND FORMAT FOR THE FIN 3403 EXTRA CREDIT PROJECT
The section labels given in this document correspond to the sections in the Word document that provides
the discussions of (instructions for) the project, as well as the sections contained in the spreadsheet
template (http://sbesley.myweb.usf.edu/FIN3403/retire-sp20.xlsx). For more detailed descriptions of the
requirements for each section, refer to the discussions contained in the Word document that describes the
project (http://sbesley.myweb.usf.edu/FIN3403/retire-sp20.pdf). You must use the format given in this
document for the extra credit project; simply fill in the requested information on the form. Do not change
the form in any way other than to extend the number of lines needed for your discussions. In other words,
keep the questions in the document you submit, do not reorder the questions, and do not change the
document’s general characteristics. If you use any other format, your project will not be graded (that is,
you will receive a score of zero for the entire project). Discussions must be typed; you will lose three (3)
points if your discussions are not typed. You can use more space than is provided; the spacing provided
in the template is for reference only. Use properly constructed sentences for all your discussions. You
will lose points if you use poor grammar or your sentences are not properly constructed.
Name:
Student Number: U
I.
Occupation/Career Expectations
A. Description of the career you expect to pursue:
B. Expected salary at various stages of your career: Include this information in the following
table and on your spreadsheet by entering the information requested in Section I on the
spreadsheet. You must compute the “% Change” in the spreadsheet.
1-5
6-10
Period (Years) After Starting Your Career
11-15
16-20
21-25
26-30
31-35
36-40
41-45
Salary:
% Change:
C. Sources of salary information (give specific and complete citations that indicate where you
obtained your information):
II.
Investment Horizon and Life Expectancy
A. According to the “Life expectancy quizzes” you completed, you expect to live until age:
(This age should be the “Life expectancy age” you enter in Section II of the spreadsheet.)
Discussion of the results of the “life expectancy quizzes.” Explain why you are expected to
live to the age indicated.
.
B. (1)
(2)
Age at retirement:
II of the spreadsheet.)
. (This should be the “Retirement age” you enter in Section
Number of years you will be able to contribute to your retirement fund—that is, the
number of years you expect to work:
. (This value should be computed in
Section II of the spreadsheet in the “Years to retirement” (Cell G22). Enter the result of
the computation here.)
Brief discussion as to why you chose the retirement age given in Part B(1):
C. Number of years you expect to live after retiring
. (This value should be computed
in Section II of the spreadsheet in the “Years of retirement” (Cell G23). Enter the result of the
computation here.)
Print and save copies of the results of the life expectancy quizzes you completed.
D. Based on the life expectancy quizzes, what changes in your lifestyle do you plan to make in
the future to increase the chances that you will live longer? Explain/Discuss.
III. Risk Preferences
A. Discuss the level of your risk tolerance; include the results of the risk tolerance quizzes you
completed. Do you consider yourself to be more conservative or less conservative than an
average investor with respect to taking risks? Explain/Discuss.
Discuss how you think your risk preferences might change as you get older. That is, how will
your risk preferences (tolerance) change as you near your retirement date?
B. Based on the results of the risk tolerance quizzes, indicate what proportion of your
retirement fund should be invested in each of the following types of investments:
Percent
Money market (cash & short-term debt)
Bonds (long-term debt)
Stocks—domestic
Stocks—foreign
Total
100.0%
Enter the proportions (weights) from this table in Section III on the spreadsheet template.
Explain why you think the above percentages are appropriate for your level of risk tolerance. If
the above percentages differ from those indicated in the results of your risk tolerance quizzes,
explain why you changed the weight for each type of investment area:
IV. Retirement Goals
A. Retirement Income
Annual retirement expenses stated in today’s dollars:
Household expenses
Healthcare
Food
Clothing
Transportation
Entertainment
Other expenses/items
Total amount
The “Total amount” should be the average income (amount) you estimate you will need each
year during retirement stated in today’s dollars. (Computed in cell K61 in the spreadsheet
template)
Enter the above amounts in the appropriate spaces in Section IV-A in the spreadsheet
template.
Discuss/Explain how you determined the dollar amounts given above (i.e., give the logic
behind your decisions):
Sources of information you used to determine the retirement expenses (give specific and
complete citations):
The expected average inflation rate per year from now until you retire is:
this amount in the appropriate cell in Section IV-A in the spreadsheet template.)
. (Enter
Discuss/Explain how you determined the expected average inflation rate:
Sources of information you used to determine the expected inflation rate (give specific and
complete citations):
The average annual dollar amount (income) needed during the years you are retired restated
in inflation-adjusted dollars is: $
. (This amount must be computed in the
spreadsheet—Section IV-A; cell K66. Enter the result of the computation here.)
B. The total dollar amount that must be in your retirement fund when you retire so you can
withdraw the annual funds you determined in Part A of this section is: $
.
(This amount must be computed using the spreadsheet—Section IV-B; cell K74. Enter the
result of the computation here.)
C. The amount of funds you currently (today) have invested for retirement purposes is:
$
. If you currently have no funds invested for your retirement and you are
25 years old or under, assume that you actually have $5,800 already in a retirement fund; if
you are older than 25, assume that you have $8,200 invested in a retirement fund. (This
amount should be entered into the appropriate cell in Section IV-C, cell K79 of the
spreadsheet.)
How much will the funds you currently (today) have invested for retirement purposes be
worth when you retire? $
. (This amount should be computed in Section IVC, cell K83 of the spreadsheet. Enter the result here.)
The additional amount needed at retirement to meet your retirement needs is: $
.
(This should be the total amount of funds you need when you retire to meet your retirement goals
minus the amount your existing retirement fund will grow to at retirement without making
additional contributions. This amount must be computed using the spreadsheet—Section IV-C,
cell K84. Enter the result of the computation here.)
D. The contributions (amounts invested) that must be made each year during your career to
ensure that your retirement fund accumulates the total amount needed at retirement are:
Rate of
Return (%)
Average market—use the expected portfolio rate
given at the top of Section IV in the spreadsheet
Above-average market (+2.6 percent)
Below-average market (–1.4 percent)
%
%
%
Dollar Amount of
the Contribution
$
$
$
(The “Dollar Amount of the Contribution” must be computed in the spreadsheet—Section
IV-D. Enter the results of the computations in the appropriate spaces above …
Purchase answer to see full
attachment

Need a custom written plagiarism free solution? Click here to order now.

Open chat
Lets chat on via WhatsApp
Hello, Welcome to our WhatsApp support. Reply to this message to start a chat.