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
-
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...
-
Identify each of the following reconciling items as: (a) An addition to the cash balance according to the bank statement, (b) A deduction from the cash balance according to the bank statement, (c) An...
-
Williams-Santana Inc. is a manufacturer of high-tech industrial parts that was started in 2012 by two talented engineers with little business training. In 2024, the company was acquired by one of its...
-
Consider a stock price \(S\) governed by the geometric Brownian motion process (a) Using \(\Delta t=1 / 12\) and \(S(0)=1\), simulate several (i.e., many) years of this process using either method,...
-
Sometimes, the historical data on returns and variances may be poor predictors of how investments will perform in the future. In this case, the scenario approach to portfolio optimization may be...
-
heridan Manufacturing has equipment that cost $ 7 5 2 4 0 0 and has accumulated depreciation of $ 3 4 2 0 0 0 . When the equipment has a fair value of $ 6 8 4 0 0 0 , it is exchanged for equipment...
-
The Conch Oil Company needs to transport 30 million barrels of crude oil from a port in Doha, Qatar in the Persian Gulf to three refineries throughout Europe. The refineries are in Rotterdam,...
-
An initial Problem Heat Cascade resulting from the Problem Table Algorithm is presented in the table below. It is known that ATmin= 20C for this process. T* (C) 540 480 400 270 120 80 H (MW) 0 -1.25...
-
What is the difference between an accounting entity and a legal entity?
-
What is a standard approach to estimate an equity beta?
-
Consider Figure 11.10, which shows the locus of feasible contracts for the population of the nation of Pcoria. The indifference curves are concave, which represents the fact that consumers want full...
-
What are the potential pitfalls of analyzing a transaction like this one based on its IRR or cash multiple?
-
How do we estimate a firms unlevered cost of capital using data from comparable publicly traded firms?
-
(a) A resistor is rated for 10k, 1W. What is its maximum voltage and current ratings. (b) An electric motor in a blender that is connected to a 220V voltage source takes a current of 8A. If the...
-
Create a data model for one of the processes in the end-of-chapter Exercises for Chapter 4. Explain how you would balance the data model and process model.
-
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...
-
For each of the following sets of numbers, calculate a \(95 \%\) confidence interval for the mean ( \(\sigma\) known); before going through the steps in calculating the confidence interval, the...
-
Consider a 3 -year \(10 \%\) coupon bond. The underlying short rate of interest follows a lattice with initial value of \(R=1.15\) and then has an factor of 1.02 , a down factor of .99 , and...
-
Using the density function of the stopping time probability for a fixed \(\lambda\), find the average time to the first event over the entire interval \([0, \infty)\).
Study smarter with the SolutionInn App