Question

In: Finance

Input Current age                      25 Retirement age               &

Input
Current age                      25
Retirement age                      60
Number of years until retirement                      35
Annual withdrawal ($)            120,000
Number of years to withdraw                      20
Interest rate (%) 6.00%
Employer contribution ($)                 5,000
Trust fund distribution ($)            150,000
Age at trust fund distribution                      45
Years until distribution                      20
Calculation & Output
Present value of withdrawals
Question 4
Annual deposit until retirement
Question 5
Present value in lump sum
Question 6
Value of employer's contribution at retirement:
Value of trust fund at retirement:
Amount required at retirement
Annual deposit until retirement

Solutions

Expert Solution

Input

Current age

                     25

Retirement age

                     60

Number of years until retirement

                     35

Annual withdrawal ($)

           120,000

Number of years to withdraw

                     20

Interest rate (%)

6.00%

Employer contribution ($)

                5,000

Trust fund distribution ($)

           150,000

Age at trust fund distribution

                     45

Years until distribution

                     20

Calculation & Output

Present value of withdrawals

Using PV function in MS excel

pv(rate,nper,pmt,fv,type)

PV(6%,20,120000,0,0)

($1,376,390.55)

Question 4

Annual deposit until retirement

Using PMT function in MS excel

pmt(rate,nper,pv,fv,type)

PMT(6%,35,0,1376390.55,0)

($12,351.53)

Question 5

Present value in lump sum

Using present value function in MS excel

pv(rate,nper,pmt,fv,type)

PV(6%,35,0,1376390.55,0)

($179,075.59)

Question 6

Value of employer's contribution at retirement:

Using Future value function in MS excel

fv(rate,nper,pmt,pv,type)

FV(6%,35,-5000,0,0)

$557,173.90

Value of trust fund at retirement:

Using Future value function in MS excel

fv(rate,nper,pmt,pv,type)

FV(6%,15,0,-150000,0)

$359,483.73

Amount required at retirement

1376390.55-557173.9-359483.73

459732.92

Annual deposit until retirement

Using PMT function in MS excel

pmt(rate,nper,pv,fv,type)

PMT(6%,35,0,459732.92,0)

($4,125.58)

jeff jeffy answered 2 years ago
Next > < Previous

Related Solutions

Comprehensive Given: current income $50,000 income needed in retirement $45,000 current age 35 age at retirement...
Comprehensive Given: current income $50,000 income needed in retirement $45,000 current age 35 age at retirement 70 age at death 90 investment return prior to retirement 7% investment return during retirement 4% a) Calculate the nest-egg needed at retirement. Assume the annual income amount will be taken at the beginning of each year during retirement. b) How much must this individual save each year in order to meet the retirement-savings goal? Assume the individual will save an equal amount annually.
For the initial scenario, assume your current age, zero current savings, a retirement age of 60,...
For the initial scenario, assume your current age, zero current savings, a retirement age of 60, a life expectancy of 95 years, an inflation rate of 3.5%, an investment portfolio return of 9% and current purchasing power of income of $75,000. Set up your worksheet to compute and show the user: (a) How much they will need to have the day they retire in order to fund their retirement with no additional contributions, (b) How much they will need to...
You begin saving for retirement at age 25, and you plan to retire at age 60....
You begin saving for retirement at age 25, and you plan to retire at age 60. You want to deposit a certain amount each month into an account that pays an APR of 3% compounded monthly. Make a table that shows the amount you must deposit each month in terms of the nest egg you desire to have when you retire. (Round your answers to the nearest cent.) Nest egg size Needed deposit $100,000 $ $200,000 $ $300,000 $ $400,000...
Consider a person who begins contributing to a retirement plan at age 25 and contributes for 40 years until retirement at age 65.
Consider a person who begins contributing to a retirement plan at age 25 and contributes for 40 years until retirement at age 65. For the first 10 years, she contributes $3,000 per year. She increases the contribution rate to $5,000 per year in years 11 through 20. This is followed by increases to $10,000 per year in years 21 through 30 and to $15,000 per year for the last ten years. This money earns a 9 percent return.First compute the...
If you start contributing to your retirement account at the age of 18 with $25 a...
If you start contributing to your retirement account at the age of 18 with $25 a month and you increase your contributions by $5 each year, how much will you be contributing a month at the age of 40?
Consider a person who begins contributing to a retirement plan at age 25 and contributes for...
Consider a person who begins contributing to a retirement plan at age 25 and contributes for 40 years until retirement at age 65. For the first ten years, she contributes $3,000 per year. She increases the contribution rate to $5,000 per year in years 11 through 20. This is followed by increases to $10,000 per year in years 21 through 30 and to $15,000 per year for the last ten years. This money earns a 9 percent return. Compute the...
Consider a person who begins contributing to a retirement plan at age 25 and contributes for...
Consider a person who begins contributing to a retirement plan at age 25 and contributes for 40 years until retirement at age 65. For the first ten years, she contributes $3,700 per year. She increases the contribution rate to $5,700 per year in years 11 through 20. This is followed by increases to $10,700 per year in years 21 through 30 and to $15,700 per year for the last ten years. This money earns a return of 10 percent. First...
Suppose you are just starting your retirement savings at age 25, and plan to retire at...
Suppose you are just starting your retirement savings at age 25, and plan to retire at age 70. Your goal is to save a million dollars in your IRA and you believe you can earn a 7% rate of return on your investment. How much must you contribute to your IRA each year to meet your goal? Select one: a. $22,222 b. $3,500 c. $619 d. $21,881
Your cousin Joe at age 25 wants to plan for his retirement and estimates to retire...
Your cousin Joe at age 25 wants to plan for his retirement and estimates to retire at the age of 65. He already has $5000 in his savings that he received as a gift from his mother. He plans to save some of his income each year during his working years and he plans to increase his savings at a constant 5% each year. He wants to be able to spend $100.000 for 20 years after his retirement and at...
You annually invest $2,000 in an individual retirement account (IRA) starting at the age of 25...
You annually invest $2,000 in an individual retirement account (IRA) starting at the age of 25 and make the contributions for 10 years. Your twin sister does the same starting at age 40 and makes the contributions for 25 years. Both of you earn 7 percent annually on your investment. What amounts will you and your sister have at age 65? Use Appendix A and Appendix C to answer the question. Round your answers to the nearest dollar. Amount on...
ADVERTISEMENT
Subjects
ADVERTISEMENT
Latest Questions
ADVERTISEMENT