Question
1. You have recently been hired as a consultant for a personal financial planning firm. One of your first projects is creating a retirement plan
1. You have recently been hired as a consultant for a personal financial planning firm. One of your first projects is creating a retirement plan for a couple, Xavier and Elaine Cooper. They have just celebrated their 45th birthdays and after paying for their childrens education, they have decided to get serious about saving for retirement. Xavier and Elaine hope to retire 20 years from now (on their 65th birthdays), and they expect to live until age 90. Their hope is to be able to withdraw $125,000 a year from their retirement account the first withdrawal will occur on their 65th birthdays, and the 25th and final withdrawal will occur on their 89th birthdays. On their 90th birthdays, the account is expected to have a zero value (i.e., they dont expect to have any remaining funds left for their childrens inheritance). Xavier and Elaine currently have $300,000 saved in a retirement account, which consists of a portfolio of mutual funds that is expected to produce an annual return of 7%. To accomplish their goals, they would like to deposit an equal annual amount into their account starting one year from today (on their 46th birthdays) and continue to make those deposits through age 65. (Again, the account has an expected annual return of 7%.) Thus, they will make 20 annual end-of-year deposits to this account. a. How much do Xavier and Elaine need to contribute to the account at the end of each of the next 20 years to accomplish their goals? (3 points) b. If they wanted to have $600,000 available for inheritance at age 90, how much would they need to contribute to the account at the end of each of the next 20 years? (Assume everything else stays the same.) (3 points) c. If they instead expected to earn only 5% a year from their mutual funds, how much would they need to contribute to the account at the end of each of the next 20 years? (Continue to assume that they want to have $600,000 available for inheritance at age 90.) (3 points) d. Xavier and Elaine realize that there are a lot of variables in their retirement plan. The two variables that they are particularly interested in are the expected return of their mutual funds and the amount they have available for inheritance at age 90. Create in Excel a two-input Data Table that tests the sensitivity of their annual deposit amount by varying the expected returns from 2% to 10% in 1% increments and varying the inheritance level from 0 to $2,400,000 in $300,000 increments. The data table should be constructed with the expected returns shown on the side of the table and the amount available for inheritance shown across the table. [Hint: An Excel file (in xlsx format) explaining how to create a Data Table is contained on the class web page. Go to Home and click on Start Here. In addition, there is a Data Table video explaining how to set up data tables on the class web page. Go to Home and click on Start Here.] Note that when you use the Excel Data Table feature, you can easily change assumptions about input values within your model and the Data Table values will simultaneously update. You may create your own table with Excel formulas to get the correct answersbut it is much easier to use the Excel Data Table feature. (3 points) e. Xavier and Elaine have one last concern. They recognize that the value of their $125,000 annual withdrawals during retirement will steadily decline because of expected inflation. Assume that they want to have the value of these withdrawals increase by 3% a year during retirement to account for expected inflation. In other words, they want to withdraw $125,000 at age 65, $128,750 at age 66, and $128,750 1.03 at age 67, etc. Going back to the other original assumptions (7% return and no expected inheritance), how much would they need to contribute to the account at the end of each of the next 20 years to meet this revised goal which protects them against rising inflation? Set up this problem using Excel
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started