Please use Excel financial functions or algebraic time value of money equations

to answer these questions in your spreadsheet. Please type the names of

everyone in your group along with section number (10, 11 or 12) at the top.

Part 1: Future value

Construct a table and a graph showing the relationship between interest rates, time, and

future value by showing how $10,000 would grow each successive year over a 20 year period at

different interest rates. Use $10,000 for your present value and calculate the future value of

this $10,000 each year over the 20 year period at 0%, 2%, 5%, 8%, 11%, 14%, 17%, and 20%

compounded annually. Future Value should be the y-axis for your graph and years (or time)

should be your x-axis and you should end up with a line for each interest rate on your graph.

Please insert your graph (chart) under your table of future values. Part 2: Present value

Construct a table and a graph showing the relationship between interest rates, time, and

present value by showing how $10,000 pushed a year further into the future over a 20 year

period would be discounted at different interest rates. Use $10,000 for your future value and

calculate the present value of this $10,000 each year over the 20 year period at 0%, 2%, 5%, 8%,

11%, 14%, 17%, and 20% compounded annually. Present Value should be the y-axis for your

graph and years (or time) should be your x-axis and you should end up with a line for each

interest rate on your graph. Please insert your graph (chart) under your table of present values. Part 3: Annuity

Prof. Washington has a self-managed retirement plan through his University and would like to

retire in 10 years and wonders if his current and future planned savings will provide adequate

future retirement income. Here’s his information and goals.

▪ Prof. Washington wants a 25-year retirement annuity that begins 10 years from today

with an equal annual payment equal to $70,000 today inflated at 3% annually over 10 years. His first retirement annuity payment would occur 10 years from today. He realizes his

purchasing power will decrease over time during retirement.

▪ Prof. Washington currently has $280,000 in his University retirement account. He expects

these savings and any future deposits into his University and any other retirement account

will earn 7.5% compounded annually. Also, he expects to earn this same 7.5% annual

return after he retires.

Answer the following questions to help Prof. Washington finalize his retirement planning.

1. What is Prof. Business’ desired annual retirement income in the first year, i.e., the

retirement income he wants 10 years from today? 2. Assuming that inflation is zero from year 10, how much will Prof. Washington need 10

years from today to fund his desired retirement annuity? 3. Assume that inflation is zero from year 10. In addition to the $280,000 balance today,

Prof. Washington will fund his future retirement goal from question 2 by making 10

annual equal deposits at 7.5% compounded annually into his retirement accounts

starting a year from today (the last deposit will be made when Prof. Washington

retires). How large does this annual deposit need to be in addition to the initial

$280,000 invested in Prof. Business’ retirement fund? 4. Assume that inflation is 3% during the entire period, even after retirement. –I got rid of

some unnecessary sentences here- Prof. Washington is worried about his purchasing

power eroding during retirement. He would like his first retirement withdrawal to be

equal to the amount you found in #1, and then he increase each successive retirement

withdrawal by 3% annually over the remaining 24 withdrawals. How much will Prof.

Washington need now at retirement given Prof. Washington's 7.5% expected return? 5. In addition to the $280,000 balance today, Prof. Washington will fund his adjusted

future retirement goal from question 4 by making 10 annual equal deposits at 7.5%

compounded annually into his retirement accounts starting a year from today (the last

deposit will be made when Prof. Washington retires). How large does this annual

deposit need to be in addition to the initial $280,000 invested in Prof. Washington’s

retirement fund? Part 4. NPV

Robert, the sophomore 20-year-old star quarterback of the university soccer team, is

approached about skipping his last two years of college and entering the professional soccer

draft. Robert expects that his soccer career will be over by the time he is 32 years old. Talent scouts estimate that Robert could receive a signing bonus of $15 million today, along with a

four-year contract for $2 million per year (payable at the end of each year). They further

estimate that he could negotiate a contract for $4 million per year for the remaining eight years

of his career. The scouts believe, however, that Robert will be a much higher draft pick if he

improves by playing two more years of college soccer. If he stays at the university, he is

expected to receive a $25 million signing bonus in two years, along with a five-year contract for

$3 million per year. After that, the scouts expect Robert to obtain a five-year contract for $5

million per year to take him into retirement. Assume that Robert can earn a 8% return over this

time.

1. What is the present value today (when Robert is 20) of the QB’s future expected NFL

earnings if he enters the NFL now?

Please use Excel financial functions or algebraic time value of money equations

to answer these questions in your spreadsheet. Please type the names of

everyone in your group along with section number (10, 11 or 12) at the top.

Part 1: Future value

Construct a table and a graph showing the relationship between interest rates, time, and

future value by showing how $10,000 would grow each successive year over a 20 year period at

different interest rates. Use $10,000 for your present value and calculate the future value of

this $10,000 each year over the 20 year period at 0%, 2%, 5%, 8%, 11%, 14%, 17%, and 20%

compounded annually. Future Value should be the y-axis for your graph and years (or time)

should be your x-axis and you should end up with a line for each interest rate on your graph.

Please insert your graph (chart) under your table of future values. Part 2: Present value

Construct a table and a graph showing the relationship between interest rates, time, and

present value by showing how $10,000 pushed a year further into the future over a 20 year

period would be discounted at different interest rates. Use $10,000 for your future value and

calculate the present value of this $10,000 each year over the 20 year period at 0%, 2%, 5%, 8%,

11%, 14%, 17%, and 20% compounded annually. Present Value should be the y-axis for your

graph and years (or time) should be your x-axis and you should end up with a line for each

interest rate on your graph. Please insert your graph (chart) under your table of present values. Part 3: Annuity

Prof. Washington has a self-managed retirement plan through his University and would like to

retire in 10 years and wonders if his current and future planned savings will provide adequate

future retirement income. Here’s his information and goals.

▪ Prof. Washington wants a 25-year retirement annuity that begins 10 years from today

with an equal annual payment equal to $70,000 today inflated at 3% annually over 10 years. His first retirement annuity payment would occur 10 years from today. He realizes his

purchasing power will decrease over time during retirement.

▪ Prof. Washington currently has $280,000 in his University retirement account. He expects

these savings and any future deposits into his University and any other retirement account

will earn 7.5% compounded annually. Also, he expects to earn this same 7.5% annual

return after he retires.

Answer the following questions to help Prof. Washington finalize his retirement planning.

1. What is Prof. Business’ desired annual retirement income in the first year, i.e., the

retirement income he wants 10 years from today? 2. Assuming that inflation is zero from year 10, how much will Prof. Washington need 10

years from today to fund his desired retirement annuity? 3. Assume that inflation is zero from year 10. In addition to the $280,000 balance today,

Prof. Washington will fund his future retirement goal from question 2 by making 10

annual equal deposits at 7.5% compounded annually into his retirement accounts

starting a year from today (the last deposit will be made when Prof. Washington

retires). How large does this annual deposit need to be in addition to the initial

$280,000 invested in Prof. Business’ retirement fund? 4. Assume that inflation is 3% during the entire period, even after retirement. –I got rid of

some unnecessary sentences here- Prof. Washington is worried about his purchasing

power eroding during retirement. He would like his first retirement withdrawal to be

equal to the amount you found in #1, and then he increase each successive retirement

withdrawal by 3% annually over the remaining 24 withdrawals. How much will Prof.

Washington need now at retirement given Prof. Washington's 7.5% expected return? 5. In addition to the $280,000 balance today, Prof. Washington will fund his adjusted

future retirement goal from question 4 by making 10 annual equal deposits at 7.5%

compounded annually into his retirement accounts starting a year from today (the last

deposit will be made when Prof. Washington retires). How large does this annual

deposit need to be in addition to the initial $280,000 invested in Prof. Washington’s

retirement fund? Part 4. NPV

Robert, the sophomore 20-year-old star quarterback of the university soccer team, is

approached about skipping his last two years of college and entering the professional soccer

draft. Robert expects that his soccer career will be over by the time he is 32 years old. Talent scouts estimate that Robert could receive a signing bonus of $15 million today, along with a

four-year contract for $2 million per year (payable at the end of each year). They further

estimate that he could negotiate a contract for $4 million per year for the remaining eight years

of his career. The scouts believe, however, that Robert will be a much higher draft pick if he

improves by playing two more years of college soccer. If he stays at the university, he is

expected to receive a $25 million signing bonus in two years, along with a five-year contract for

$3 million per year. After that, the scouts expect Robert to obtain a five-year contract for $5

million per year to take him into retirement. Assume that Robert can earn a 8% return over this

time.

1. What is the present value today (when Robert is 20) of the QB’s future expected NFL

earnings if he enters the NFL now?

Please use Excel financial functions or algebraic time value of money equations

to answer these questions in your spreadsheet. Please type the names of

everyone in your group along with section number (10, 11 or 12) at the top.

Part 1: Future value

Construct a table and a graph showing the relationship between interest rates, time, and

future value by showing how $10,000 would grow each successive year over a 20 year period at

different interest rates. Use $10,000 for your present value and calculate the future value of

this $10,000 each year over the 20 year period at 0%, 2%, 5%, 8%, 11%, 14%, 17%, and 20%

compounded annually. Future Value should be the y-axis for your graph and years (or time)

should be your x-axis and you should end up with a line for each interest rate on your graph.

Please insert your graph (chart) under your table of future values. Part 2: Present value

Construct a table and a graph showing the relationship between interest rates, time, and

present value by showing how $10,000 pushed a year further into the future over a 20 year

period would be discounted at different interest rates. Use $10,000 for your future value and

calculate the present value of this $10,000 each year over the 20 year period at 0%, 2%, 5%, 8%,

11%, 14%, 17%, and 20% compounded annually. Present Value should be the y-axis for your

graph and years (or time) should be your x-axis and you should end up with a line for each

interest rate on your graph. Please insert your graph (chart) under your table of present values. Part 3: Annuity

Prof. Washington has a self-managed retirement plan through his University and would like to

retire in 10 years and wonders if his current and future planned savings will provide adequate

future retirement income. Here’s his information and goals.

▪ Prof. Washington wants a 25-year retirement annuity that begins 10 years from today

with an equal annual payment equal to $70,000 today inflated at 3% annually over 10 years. His first retirement annuity payment would occur 10 years from today. He realizes his

purchasing power will decrease over time during retirement.

▪ Prof. Washington currently has $280,000 in his University retirement account. He expects

these savings and any future deposits into his University and any other retirement account

will earn 7.5% compounded annually. Also, he expects to earn this same 7.5% annual

return after he retires.

Answer the following questions to help Prof. Washington finalize his retirement planning.

1. What is Prof. Business’ desired annual retirement income in the first year, i.e., the

retirement income he wants 10 years from today? 2. Assuming that inflation is zero from year 10, how much will Prof. Washington need 10

years from today to fund his desired retirement annuity? 3. Assume that inflation is zero from year 10. In addition to the $280,000 balance today,

Prof. Washington will fund his future retirement goal from question 2 by making 10

annual equal deposits at 7.5% compounded annually into his retirement accounts

starting a year from today (the last deposit will be made when Prof. Washington

retires). How large does this annual deposit need to be in addition to the initial

$280,000 invested in Prof. Business’ retirement fund? 4. Assume that inflation is 3% during the entire period, even after retirement. –I got rid of

some unnecessary sentences here- Prof. Washington is worried about his purchasing

power eroding during retirement. He would like his first retirement withdrawal to be

equal to the amount you found in #1, and then he increase each successive retirement

withdrawal by 3% annually over the remaining 24 withdrawals. How much will Prof.

Washington need now at retirement given Prof. Washington's 7.5% expected return? 5. In addition to the $280,000 balance today, Prof. Washington will fund his adjusted

future retirement goal from question 4 by making 10 annual equal deposits at 7.5%

compounded annually into his retirement accounts starting a year from today (the last

deposit will be made when Prof. Washington retires). How large does this annual

deposit need to be in addition to the initial $280,000 invested in Prof. Washington’s

retirement fund? Part 4. NPV

Robert, the sophomore 20-year-old star quarterback of the university soccer team, is

approached about skipping his last two years of college and entering the professional soccer

draft. Robert expects that his soccer career will be over by the time he is 32 years old. Talent scouts estimate that Robert could receive a signing bonus of $15 million today, along with a

four-year contract for $2 million per year (payable at the end of each year). They further

estimate that he could negotiate a contract for $4 million per year for the remaining eight years

of his career. The scouts believe, however, that Robert will be a much higher draft pick if he

improves by playing two more years of college soccer. If he stays at the university, he is

expected to receive a $25 million signing bonus in two years, along with a five-year contract for

$3 million per year. After that, the scouts expect Robert to obtain a five-year contract for $5

million per year to take him into retirement. Assume that Robert can earn a 8% return over this

time.

1. What is the present value today (when Robert is 20) of the QB’s future expected NFL

earnings if he enters the NFL now?

to answer these questions in your spreadsheet. Please type the names of

everyone in your group along with section number (10, 11 or 12) at the top.

Part 1: Future value

Construct a table and a graph showing the relationship between interest rates, time, and

future value by showing how $10,000 would grow each successive year over a 20 year period at

different interest rates. Use $10,000 for your present value and calculate the future value of

this $10,000 each year over the 20 year period at 0%, 2%, 5%, 8%, 11%, 14%, 17%, and 20%

compounded annually. Future Value should be the y-axis for your graph and years (or time)

should be your x-axis and you should end up with a line for each interest rate on your graph.

Please insert your graph (chart) under your table of future values. Part 2: Present value

Construct a table and a graph showing the relationship between interest rates, time, and

present value by showing how $10,000 pushed a year further into the future over a 20 year

period would be discounted at different interest rates. Use $10,000 for your future value and

calculate the present value of this $10,000 each year over the 20 year period at 0%, 2%, 5%, 8%,

11%, 14%, 17%, and 20% compounded annually. Present Value should be the y-axis for your

graph and years (or time) should be your x-axis and you should end up with a line for each

interest rate on your graph. Please insert your graph (chart) under your table of present values. Part 3: Annuity

Prof. Washington has a self-managed retirement plan through his University and would like to

retire in 10 years and wonders if his current and future planned savings will provide adequate

future retirement income. Here’s his information and goals.

▪ Prof. Washington wants a 25-year retirement annuity that begins 10 years from today

with an equal annual payment equal to $70,000 today inflated at 3% annually over 10 years. His first retirement annuity payment would occur 10 years from today. He realizes his

purchasing power will decrease over time during retirement.

▪ Prof. Washington currently has $280,000 in his University retirement account. He expects

these savings and any future deposits into his University and any other retirement account

will earn 7.5% compounded annually. Also, he expects to earn this same 7.5% annual

return after he retires.

Answer the following questions to help Prof. Washington finalize his retirement planning.

1. What is Prof. Business’ desired annual retirement income in the first year, i.e., the

retirement income he wants 10 years from today? 2. Assuming that inflation is zero from year 10, how much will Prof. Washington need 10

years from today to fund his desired retirement annuity? 3. Assume that inflation is zero from year 10. In addition to the $280,000 balance today,

Prof. Washington will fund his future retirement goal from question 2 by making 10

annual equal deposits at 7.5% compounded annually into his retirement accounts

starting a year from today (the last deposit will be made when Prof. Washington

retires). How large does this annual deposit need to be in addition to the initial

$280,000 invested in Prof. Business’ retirement fund? 4. Assume that inflation is 3% during the entire period, even after retirement. –I got rid of

some unnecessary sentences here- Prof. Washington is worried about his purchasing

power eroding during retirement. He would like his first retirement withdrawal to be

equal to the amount you found in #1, and then he increase each successive retirement

withdrawal by 3% annually over the remaining 24 withdrawals. How much will Prof.

Washington need now at retirement given Prof. Washington's 7.5% expected return? 5. In addition to the $280,000 balance today, Prof. Washington will fund his adjusted

future retirement goal from question 4 by making 10 annual equal deposits at 7.5%

compounded annually into his retirement accounts starting a year from today (the last

deposit will be made when Prof. Washington retires). How large does this annual

deposit need to be in addition to the initial $280,000 invested in Prof. Washington’s

retirement fund? Part 4. NPV

Robert, the sophomore 20-year-old star quarterback of the university soccer team, is

approached about skipping his last two years of college and entering the professional soccer

draft. Robert expects that his soccer career will be over by the time he is 32 years old. Talent scouts estimate that Robert could receive a signing bonus of $15 million today, along with a

four-year contract for $2 million per year (payable at the end of each year). They further

estimate that he could negotiate a contract for $4 million per year for the remaining eight years

of his career. The scouts believe, however, that Robert will be a much higher draft pick if he

improves by playing two more years of college soccer. If he stays at the university, he is

expected to receive a $25 million signing bonus in two years, along with a five-year contract for

$3 million per year. After that, the scouts expect Robert to obtain a five-year contract for $5

million per year to take him into retirement. Assume that Robert can earn a 8% return over this

time.

1. What is the present value today (when Robert is 20) of the QB’s future expected NFL

earnings if he enters the NFL now?

to answer these questions in your spreadsheet. Please type the names of

everyone in your group along with section number (10, 11 or 12) at the top.

Part 1: Future value

Construct a table and a graph showing the relationship between interest rates, time, and

future value by showing how $10,000 would grow each successive year over a 20 year period at

different interest rates. Use $10,000 for your present value and calculate the future value of

this $10,000 each year over the 20 year period at 0%, 2%, 5%, 8%, 11%, 14%, 17%, and 20%

compounded annually. Future Value should be the y-axis for your graph and years (or time)

should be your x-axis and you should end up with a line for each interest rate on your graph.

Please insert your graph (chart) under your table of future values. Part 2: Present value

Construct a table and a graph showing the relationship between interest rates, time, and

present value by showing how $10,000 pushed a year further into the future over a 20 year

period would be discounted at different interest rates. Use $10,000 for your future value and

calculate the present value of this $10,000 each year over the 20 year period at 0%, 2%, 5%, 8%,

11%, 14%, 17%, and 20% compounded annually. Present Value should be the y-axis for your

graph and years (or time) should be your x-axis and you should end up with a line for each

interest rate on your graph. Please insert your graph (chart) under your table of present values. Part 3: Annuity

Prof. Washington has a self-managed retirement plan through his University and would like to

retire in 10 years and wonders if his current and future planned savings will provide adequate

future retirement income. Here’s his information and goals.

▪ Prof. Washington wants a 25-year retirement annuity that begins 10 years from today

with an equal annual payment equal to $70,000 today inflated at 3% annually over 10 years. His first retirement annuity payment would occur 10 years from today. He realizes his

purchasing power will decrease over time during retirement.

▪ Prof. Washington currently has $280,000 in his University retirement account. He expects

these savings and any future deposits into his University and any other retirement account

will earn 7.5% compounded annually. Also, he expects to earn this same 7.5% annual

return after he retires.

Answer the following questions to help Prof. Washington finalize his retirement planning.

1. What is Prof. Business’ desired annual retirement income in the first year, i.e., the

retirement income he wants 10 years from today? 2. Assuming that inflation is zero from year 10, how much will Prof. Washington need 10

years from today to fund his desired retirement annuity? 3. Assume that inflation is zero from year 10. In addition to the $280,000 balance today,

Prof. Washington will fund his future retirement goal from question 2 by making 10

annual equal deposits at 7.5% compounded annually into his retirement accounts

starting a year from today (the last deposit will be made when Prof. Washington

retires). How large does this annual deposit need to be in addition to the initial

$280,000 invested in Prof. Business’ retirement fund? 4. Assume that inflation is 3% during the entire period, even after retirement. –I got rid of

some unnecessary sentences here- Prof. Washington is worried about his purchasing

power eroding during retirement. He would like his first retirement withdrawal to be

equal to the amount you found in #1, and then he increase each successive retirement

withdrawal by 3% annually over the remaining 24 withdrawals. How much will Prof.

Washington need now at retirement given Prof. Washington's 7.5% expected return? 5. In addition to the $280,000 balance today, Prof. Washington will fund his adjusted

future retirement goal from question 4 by making 10 annual equal deposits at 7.5%

compounded annually into his retirement accounts starting a year from today (the last

deposit will be made when Prof. Washington retires). How large does this annual

deposit need to be in addition to the initial $280,000 invested in Prof. Washington’s

retirement fund? Part 4. NPV

Robert, the sophomore 20-year-old star quarterback of the university soccer team, is

approached about skipping his last two years of college and entering the professional soccer

draft. Robert expects that his soccer career will be over by the time he is 32 years old. Talent scouts estimate that Robert could receive a signing bonus of $15 million today, along with a

four-year contract for $2 million per year (payable at the end of each year). They further

estimate that he could negotiate a contract for $4 million per year for the remaining eight years

of his career. The scouts believe, however, that Robert will be a much higher draft pick if he

improves by playing two more years of college soccer. If he stays at the university, he is

expected to receive a $25 million signing bonus in two years, along with a five-year contract for

$3 million per year. After that, the scouts expect Robert to obtain a five-year contract for $5

million per year to take him into retirement. Assume that Robert can earn a 8% return over this

time.

1. What is the present value today (when Robert is 20) of the QB’s future expected NFL

earnings if he enters the NFL now?