View the step-by-step solution to:

Excel Project #2 Retirement Planning FINA 3724 Fall 2012 A. Gubin Among the goals of any introductory finance class should be an increased awareness...

I need someone to help answer this question: How much money will you need in your account at retirement?

Also attached is a PDF with instructions for an excel sheet that must be made. Thanks!

Excel Project #2 Retirement Planning FINA 3724 Fall 2012 A. Gubin Among the goals of any introductory finance class should be an increased awareness of how important it is to save wisely for retirement. Regardless of your career path, age, or area of expertise, knowing how much of your annual salary you should save to meet your retirement goals is important. Everyone needs to answer this question, but unfortunately many have not. The goal of this project is to start you thinking about how much you should save each year to meet your retirement needs. Done correctly, this project can serve you for years. On the next pages , you’ll find some simplifying assumptions and an example. You are free to modify any assumptions (e.g. inflation rate, annual rate of return, etc.) so long as they are reasonable and realistic. Questions: 1. How much money will you need in your account at retirement? 2. What annual savings rate did you need to generate enough savings to reach your retirement goal? 3. If you made any changes to the simplifying assumptions, what changes did you make and why? 4. Did you find this assignment interesting and useful to you? Deliverables (due December 3, 2011 at midnight): 1. Submit online or email a copy of your spreadsheet to me at [email protected] . Be sure your name is on your spreadsheet! 2. In the body of your submission or email, answer the 4 questions. A suggested game plan: Step 1: Figure out what you’ll make each year until you retire. Step 2: Figure out how much money you’ll need through retirement e ach year. Step 3: Discount the retirement spending to January 1 of the year you turn 68 and sum the values to find the value of money you’ll need in your account at retirement. Step 4: Estimate an annual savings rate and calculate how much you’re saving each year. Step 5: Compound forward your annual savings to January 1 of the year you turn 68 and sum the values to find the value of money you’ll have in your account at retirement. Step 6: Calculate the difference in values of Step 5 and Step 3. Step 7: Adjust your annual savings rate such that you have at least enough money to cover your retirement. You are done when you have a bequeathment (i.e., inheritance) that’s at least zero .
Background image of page 1
Some suggested simplifying assumptions: The annual inflation rate is 3% for the rest of your life. The average return on your investments will be 7%. You will plan to retire on December 31 st of the calendar year you turn 67. You have no retirement savings currently. You plan to live you are 90 years old; i.e., you will pass away December 31 of the year you turn 90. Your retirement spending, beginning January 1 of the year you turn 68, will be $250,000 or 70% of your age 67 salary, whichever is lower. This spending will increase each year at the cost of living (i.e., inflation). (That’s actu ally not an unreasonable figure many retirees take up new hobbies or travel to fill up their time and consider that a nursing home costs $61,500 per year today after inflation over 40+ years could come out to $200,000 per year when you retire!) This savings plan will begin on January 1, 2013. Any savings you make will be done on December 31 of a given year, including on the day you plan to retire. These savings will probably increase each year to account for inflation. (Where does this cash come from? L et’s suppose you stockpile it in your checking account all year and then make a single contribution to your plan at the end of the year. I’ll acknowledge that this is extraordinarily simplistic, but convenient for our purposes.) All savings will be done after taxes are paid on income. Your effective tax rate will depend on how much income you have. We’ll simplify this a lot: o If you make less than $40,000 your effective tax rate is 10% o If you make at least $40,000 and less than $150,000, your effective tax rate is 17% o If you make more than $150,000, your effective tax rate is 25%. We also need to make some assumptions about your career path. The current median starting salary for a business major is $40,000 per year. If you start a job in the year you turn 22 , we’ll assume that you get paid a full year salary on January 1 of the year you turn 22. You will get a cost of living raise every year (i.e. at the rate of inflation). Every three years, you’ll get a 4% raise for good performance on the job (or you move to a better paying job, etc.) on top of your cost of living increase. If you’d like to add a layer of complexity, you can get a 2 -year MBA, but it will remove two years from your earnings. (We’ll assume that you can get a full -tuition scholarship and stipend so it won’t cost anything other than the opportunity cost of working.) o If you go straight from college without any significant prior work experience, your starting salary will be $50,000. With fewer than four years of work experience, your salary will increase by 1.5 times your pre-MBA salary. If you wait until you have at least four years of work experience before going for your MBA, your salary after earning a masters ’ will be 2 times your pre-MBA salary.
Background image of page 2
Show entire document
Sign up to view the entire interaction

Recently Asked Questions

Why Join Course Hero?

Course Hero has all the homework and study help you need to succeed! We’ve got course-specific notes, study guides, and practice tests along with expert tutors.

-

Educational Resources
  • -

    Study Documents

    Find the best study resources around, tagged to your specific courses. Share your own to gain free Course Hero access.

    Browse Documents
  • -

    Question & Answers

    Get one-on-one homework help from our expert tutors—available online 24/7. Ask your own questions or browse existing Q&A threads. Satisfaction guaranteed!

    Ask a Question