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

retire_sp20_3.xlsx

retire_template_sp20.docx

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 yearsthat 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

documents 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 fundthat 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)

Stocksdomestic

Stocksforeign

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 todays 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 todays 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

spreadsheetSection 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 spreadsheetSection 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 spreadsheetSection 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 marketuse 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 spreadsheetSection

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.