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...
-
A South American country has had a high rate of inflation. Recently, its exchange rate was 15 cruzados per dollar; that is, one dollar will buy 15 cruzados in the foreign exchange market. It is...
-
A reactor is loaded with equal amounts of hydrogen, nitrogen, ammonia, and helium. If the reaction is 3H 2 + N 2 = 2NH 3 , do the following: a) Calculate the minimum and maximum values of . b)...
-
Keenlen Inc. is considering the purchase of new manufacturing equipment requiring an initial \(\$ 580,000\) investment and having an expected eight-year useful life. At the end of its life, the...
-
1. How successful was this project? 2. What best practices were evident in the case? How did they contribute to project objectives? Chad Cromwell, head of university housing, gazed up at the tower at...
-
The following financial information was taken from the books of Zone Health Club, a small spa and fitness club: Account Balances as of December 31, Year 2 Accounts receivable $ 26,900 Accounts...
-
Cherry Cotta makes custom ordered clay pots for residential gardens. Below is cost information regarding its latest job. a. Materials were purchased on account. $18,996 purchased b. A materials...
-
Switching to a two-stage pipeline means introducing an extra stage meaning halving the executing rate, but instead it is doubled in some cases. Explain why?
-
Question I: Introduction (30 min) a) What are Strategic decisions and Tactical decisions? Describe them in 2 sentences. Give some examples of Strategic and Tactical decisions in Operations Management...
-
You have two lenses at your disposal, one with a focal length f 1 = 3 8 . 5 cm , the other with a focal length f 2 = - 3 8 . 5 cm . If you want to produce an image of the bulb that is enlarged by a...
-
WACC: 9.76%NPV: 6,210,269IRR: 53.01%MIRR: 46.55%Disucuss the operation, forgein exchnage and political risk of opening a subsidiary in Nairobi,Kenya.
-
Why is it necessary for minutes of meetings to be stored either electronically or in hard copy? Provide reference
-
Zozan Corp. manufactures Hydrogen engines automobiles. The variable cost is $25,000 per unit, and the credit price is $43,000 each. Credit is extended for one period, and based on historical...
-
A) Use implicit differentiation to find for 3 y B) C) dy dr D) Use implicit differentiation to find E) y = -10y - 2x dy da Suppose f(-1) = 7, f'(-1) = 7. Approximate f(-0.99). f(-0.99) decimal...
-
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 consistency principle stipulates that when a company begins using a given inventory valuation method (FIFO, LIFO, or the weighted-average cost method), the company should continue to use that...
-
How does an individual know if all cash transactions have been properly accounted for on the statement of cash flows?
-
How are the cash flows originating from a company's financing activities identified?
Study smarter with the SolutionInn App