Reference no: EM13381901
Assume that you will be graduating from a university with your MBA in December of this year. You will start your new job in January of 2015 with an annual salary starting at $65,000.00. Your salary will typically increase every year by 7%. You will work a total of 45 years and then retire.
You plan to get married four years after you start your new job, and you budget $10,000 for wedding and honeymoon expenses. Once married, you plan to have two children, one who will be born in 2019, and the other one will be born in 2021. You want to save enough money to allow your children to attend your Alma Mater and earn a 4-year undergraduate degree (they have to fund their graduate degree themselves). Currently, the total one academic year cost for Kansas residents is $12,801. Your children will start college in January of 2037 and 2039, respectively. Assume an annual average increase in college expenses of 4.2%. Further assume that you need to pay for college expenses in January for the entire year.
Also shortly after getting married you want to buy a house. To meet the needs of your family you expect the house to cost about $100,000 when you buy it at the end of 2018. To obtain a mortgage you want to make a down payment of 5% of the purchase price.
Upon retirement you want to take your spouse on a 12-month trip around the world, which you expect would cost you currently approximately $100,000. 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 $40,000 per year, using today's cost of living. You expect to live for another 19 years after you return from your trip.
During your work life you expect to earn about 10% annual return on your investments. During retirement you switch your portfolio to more risk-averse assets, which will then earn you an annual return of 7%. You budget for a 6% annual inflation (i.e., what costs $100,000 today will cost more in the future!).
Your Task:
Use Excel to set up this problem in an easy to follow way and compute how much money you will have to save every year to allow for all the listed expenses during your lifetime - you are looking for one constant amount that does not change during your work life. Note, you make contribution at the end of a year, and you withdraw during retirement at the beginning of the year! Also note that your wedding expenses are withdrawn at the end of year 4, the down payment on your house is withdrawn at the end of year 5, and the annual college expenses are withdrawn at the end of years 2036 through 2041. Finally, your travel expenses are withdrawn at the end of year 45, and your final withdrawal will be made at the end of year 2078 (which is the same as the beginning of 2079).
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.
Answer specifically what the annual year-end contributions to your financial plan will have to be to allow for the appropriate withdrawals when the funds are needed, and what the approximate monthly contributions are (if you have the annual number just simply divide it by 12 to get a monthly reference). 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. Also show an annual account balance, starting in 2014 and ending at the end of 2079. The account balance should start and end with a balance of $0.