Statement of Facts Jennifer Wilson (age 47) has hired you to help her plan for her...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
Statement of Facts Jennifer Wilson (age 47) has hired you to help her plan for her future retirement She plans to retire in 20 years when she will be 67 and live to age 100 (33 years in retirement) Her yearly income is $35,000 Her current yearly spending is $30,000 and she plans to maintain this level in retirement Inflation is projected to be 3% per year She currently has saved $100,000 for her retirement Her investments are projected to grow at 8% per year Inflation-adjusted rate of return = [(1 + investment return rate + 1 + inflation rate) - 1] x 100 Her inflation-adjusted rate of return is projected to be 4.85% per year: [(1.08 ÷ 1.03) - 1] x 100 Using Excel Functions to Make Time Value of Money Calculations Step One - Calculating the Future Value of the Yearly Spending Needed: Determine the future value of Jennifer's annual spending needed for her first year of retirement and then solve for the future value of this amount 1. Layout the worksheet in a format similar to how the lecture slides illustrate to calculate the Future Value of a Single Amount 2. Present Value = current yearly spending, but the amount should be negative as it reflects cash spending / outflows 3. Rate of Interest = 3% (inflation rate) 4. Number of Years = years to retirement 5. Type = payment due at the end of the period (0) Step Two - Calculating the Present Value of the Series of Future Spending Payments: Solve for the present value of the amount Jennifer will need for retirement 1. Layout the worksheet in a format similar to how the lecture slides illustrate to calculate the Present Value of an Annuity Due 2. Annual Annuity Payment = calculated in Step One (link cell to the value from Step One), but the amount should be negative as it reflects cash spending / outflows 3. Rate of Interest = 4.85 % (inflation-adjusted rate of return) 4. Number of Years = years spent in retirement 5. Type = payment due at the beginning of the period (1) Step Three - Calculate the Amount Needed to be Saved Annually Solve for the annual payment needed for Jennifer to achieve the future value for her needed retirement fund 1. Layout the worksheet in a format similar to how the lecture slides illustrate to calculate the Annual Deposits Amount to Accumulate a Future Sum and add a row for Present Value 2. Present Value = amount already accumulated, but the amount should be negative as it reflects cash spending / outflows 3. Future Value = amount needed to support retirement spending (link cell to the value from Step Two) 4. Rate of Interest = 8% (investment growth rate) 5. Number of Years = years to retirement 6. Type = payment due at the end of the period (0) Statement of Facts Jennifer Wilson (age 47) has hired you to help her plan for her future retirement She plans to retire in 20 years when she will be 67 and live to age 100 (33 years in retirement) Her yearly income is $35,000 Her current yearly spending is $30,000 and she plans to maintain this level in retirement Inflation is projected to be 3% per year She currently has saved $100,000 for her retirement Her investments are projected to grow at 8% per year Inflation-adjusted rate of return = [(1 + investment return rate + 1 + inflation rate) - 1] x 100 Her inflation-adjusted rate of return is projected to be 4.85% per year: [(1.08 ÷ 1.03) - 1] x 100 Using Excel Functions to Make Time Value of Money Calculations Step One - Calculating the Future Value of the Yearly Spending Needed: Determine the future value of Jennifer's annual spending needed for her first year of retirement and then solve for the future value of this amount 1. Layout the worksheet in a format similar to how the lecture slides illustrate to calculate the Future Value of a Single Amount 2. Present Value = current yearly spending, but the amount should be negative as it reflects cash spending / outflows 3. Rate of Interest = 3% (inflation rate) 4. Number of Years = years to retirement 5. Type = payment due at the end of the period (0) Step Two - Calculating the Present Value of the Series of Future Spending Payments: Solve for the present value of the amount Jennifer will need for retirement 1. Layout the worksheet in a format similar to how the lecture slides illustrate to calculate the Present Value of an Annuity Due 2. Annual Annuity Payment = calculated in Step One (link cell to the value from Step One), but the amount should be negative as it reflects cash spending / outflows 3. Rate of Interest = 4.85 % (inflation-adjusted rate of return) 4. Number of Years = years spent in retirement 5. Type = payment due at the beginning of the period (1) Step Three - Calculate the Amount Needed to be Saved Annually Solve for the annual payment needed for Jennifer to achieve the future value for her needed retirement fund 1. Layout the worksheet in a format similar to how the lecture slides illustrate to calculate the Annual Deposits Amount to Accumulate a Future Sum and add a row for Present Value 2. Present Value = amount already accumulated, but the amount should be negative as it reflects cash spending / outflows 3. Future Value = amount needed to support retirement spending (link cell to the value from Step Two) 4. Rate of Interest = 8% (investment growth rate) 5. Number of Years = years to retirement 6. Type = payment due at the end of the period (0)
Expert Answer:
Answer rating: 100% (QA)
Time Value of Money Calculations for Jennifer Wilsons Retirement Step One Future Value of Yearly Spending Needed Layout Year Present Value Rate Number ... View the full answer
Related Book For
Posted Date:
Students also viewed these finance questions
-
1. Describe what a career in professional selling is and what it entails. i need more expleneshon
-
You have been approached by Ryan and Michelle Irving, to review their financial situation. The couple is concerned about their financial future as they are having difficulty making ends meet each...
-
This case draws on material from Chapters 1820. See MyLab Finance for the data in Tables 1, 2, 3, and 4 and the spreadsheet referenced in the case questions. Idexo Corporation is a privately held...
-
Consider a cube that has 5cm length sides. What is the surface area to volume ratio of this cube? Report your answer in cm/cm Report your answer to one decimal place. QUESTION 12 Consider a cube that...
-
Is a reversible expansion or compression process necessarily quasi-equilibrium? Is a quasi equilibrium expansion or compression process necessarily reversible? Explain.
-
What is the biggest deterrent to growth of the online insurance industry nationally?
-
Draw the complements of Graphs 8 and 9. Determine whether the graphs you drew are isomorphic to each other and explain how you know. Use this information to determine whether Graphs 8 and 9 are...
-
A domestic shoe company distributes running shoes and tennis shoes for $95 per pair. The marginal cost of producing a pair of running shoes is $60, and the marginal cost of producing a pair of tennis...
-
Mitchell Bicycle Shop has the following transactions related to its top - selling Mongoose mountain bike for the month of March. Mitchell Bicycle Shop uses a periodic inventory system. \ table [ [...
-
Suppose that it is one year after EBVs investment in Newco, and Tall-tree makes a Series B investment for 6M shares of Newco at $0.2 per share. Following the Series B investment, what percentage of...
-
Given f(z) = 2+3 and g(z), find the following functions and their domains. on the domain (from left to right) ( ? U( (A) fog(x)= (B) fof(z) = ? on the domain 7 (C) go f(z) = on the domain ? (D) go...
-
How to draft a pretrial memorandum?
-
How can reports on deficiencies be made more meaningful to management?
-
List five questions the preliminary survey can answer for internal auditors.
-
A deficient condition was described as follows in the draft on an audit report: In our test of automotive vehicles, we found that 35 automobiles had been serviced at intervals of 9 to 12 months. We...
-
Distinguish between the authorization for and the objectives of an activity.
-
Crane, Inc. uses a flexible budget for manufacturing overhead based on machine hours. Variable manufacturing overhead costs per machine hour are as follows: Indirect labor $5.00 Indirect materials...
-
What is a content filter? Where is it placed in the network to gain the best result for the organization?
-
Give three other names for minimum attractive rate of return.
-
What is meant by end-of-period convention?
-
Hydrochloric acid, which fumes at room temperatures, creates a very corrosive work environment, causing steel tools to rust and equipment to fail prematurely. A distillation system purchased 4 years...
-
The following are the accounts of Creative Advertising, Inc., an agency that develops marketing materials for print, radio, and television. The agencys first year of operations ended on January 31,...
-
Southwest Airlines Co. is one of the most successful airlines in the United States. Its annual report contains this statement: We are a company of People, not Planes. That is what distinguishes us...
-
Costco Wholesale Corporation is Americas largest membership retail company. According to its letter to stockholders: Our mission is to bring quality goods and services to our members at the lowest...
Study smarter with the SolutionInn App