Reference no: EM13137065

Assume that you will be graduating from ESU in December of this year. You will start your new job in January of 2015 with an annual salary starting at $50,000.00. Your salary will increase every year by 5% until you retire. You will work a total of 50 years and then retire. 

Upon retirement you want to take your spouse on a 12-month trip around the world, which you expect would cost you currently approximately $120,000 (this amount is based on today's dollar, and it will increase every year due to inflation). The trip expenses have to be paid right at retirement when the journey begins. After you return from your trip, you expect you will need approximately $50,000 per year (this amount is based on today's dollar, and it will increase every year due to inflation), and that you will live for another 20 years. Note, you make contributions to your retirement at the end of a year, and you withdraw during retirement at the beginning of the year! 

During your work life you want to save a fixed amount every year for your retirement. You expect that your retirement account yields an annual return of 8% during your work life, and 4% during your retirement years. You budget for a 6% annual inflation (i.e., what costs $1,000 today would cost $1,000(1.06^10) = $1,790.85 in 10 years!). 

Your Task: 
Use Excel to find out the fixed annual amount you would have to save in order to afford your desired retirement lifestyle. You might want to focus on a 2-step process, where you first solve for the amount you need to have in your retirement account at the time of retirement, and then solve for the annual contribution that makes this possible. You select the setup of your spreadsheet, but keep it within the same workbook. Points will be given to the clarity and elegance of your setup, not just to the correct solution. A clear and clean setup will help you in the thinking process, and the ability of communicating your thoughts and solutions clearly in a simple setup will be a valuable skillset for your future professional life. 

You need to show all the steps involved to derive your final answer. If you come to the correct conclusion but do not show all/any necessary steps, you will not earn all/any points on this project. If you show your steps but make mistakes along the way, you might qualify for some partial credit. 

Based on your Excel spreadsheet, answer the following questions: 

a) What is the fixed annual year-end contribution amount that will allow you to achieve your retirement lifestyle? 
b) What are the approximate monthly contributions (if you have the annual number just simply divide it by 12 to get a monthly reference)? 
c) Do you believe that based on your provided income information you will be able to afford these annual contributions? When you answer this question provide a brief rationale for your position. 
d) Show an annual retirement account balance. The account balance should start and end with a balance of $0. 

Also answer the following additional questions: 

e) All else equal, what would be your annual contribution if you decide to start saving beginning in year 11, and alternatively, if you start saving beginning in year 21? 
f) All else equal, what would be your annual contribution if your retirement account yields an annual return of 12%, and alternatively, 4% during your work life (the return during retirement remains unchanged)? 
g) All else equal, what would be your annual contribution if annual inflation was 8%, and alternatively 12%?