Having just graduated from HBU with a degree in finance, you landed a job as a finance counselor for a large company Your very first client is a young couple who want to put their financial business in order Both the husband and the wife are 27 years ago and in stable employment They want to retire together at the age of 65 Thus, they have 38 ( 65 27) years until retirement from now They want you to help them in their financial planning by answering a series of questions as follows The couple has combined annual income of $75,000 today They expect an annual average growth in their income of 4 Use Excel spread sheet to show your analysis How much will they be making annually by the time they retire They want to retire with $2 million in savings They also want to set aside a flat $5,000 every year, at the beginning of each year starting now, as savings toward the retirement goal What does the saving have to grow in value per year in order for them to achieve their retirement goal They have no savings for this purpose as of today Assume they have $5,000 already set aside for the purpose of retirement, how much would they have to save every year, at the beginning of each year starting now, in order to attain the goal of $2 million by the time they retire Use Excel spread sheet to show your analysis Assume they are able to achieve an average rate of return on their savings of 7 If they have retired with $2 million in savings, and they want to withdraw $100,000 every year for living expenses at the beginning of each year, how long can their retirement nest egg last (in years) if the rate of return on their savings is 3 post retirement The couple plan to have their first kid in exactly 2 years from now Today a college education costs about $30,000 a year Assume this education cost grows with an annual inflation rate of 6 How much will the college education cost per year when the child turns 18 You may work on the project in groups or pairs if you wish Each group should be no more than 4 students You may select any of your classmates to work on the project together however, you must still submit your own individual version and mention the contributions of others to the project in your group Each project should be submitted as an Excel sheet attachment

The Answer is in the image, click to view ...

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Sep 23, 2024

Having just graduated from HBU with a degree in finance, you landed a job as a finance counselor for a large company. Your very first

Having just graduated from HBU with a degree in finance, you landed a job as a finance counselor for a large company. Your very first client is a young couple who want to put their financial business in order. Both the husband and the wife are 27 years ago and in stable employment. They want to retire together at the age of 65. Thus, they have 38 (=65-27) years until retirement from now. They want you to help them in their financial planning by answering a series of questions as follows:

The couple has combined annual income of $75,000 today. They expect an annual average growth in their income of 4%. Use Excel spread sheet to show your analysis. How much will they be making annually by the time they retire?
They want to retire with $2 million in savings. They also want to set aside a flat $5,000 every year, at the beginning of each year starting now, as savings toward the retirement goal. What does the saving have to grow in value per year in order for them to achieve their retirement goal? They have no savings for this purpose as of today.
Assume they have $5,000 already set aside for the purpose of retirement, how much would they have to save every year, at the beginning of each year starting now, in order to attain the goal of $2 million by the time they retire? Use Excel spread sheet to show your analysis. Assume they are able to achieve an average rate of return on their savings of 7%.
If they have retired with $2 million in savings, and they want to withdraw $100,000 every year for living expenses at the beginning of each year, how long can their retirement nest egg last (in years) if the rate of return on their savings is 3% post-retirement?
The couple plan to have their first kid in exactly 2 years from now. Today a college education costs about $30,000 a year. Assume this education cost grows with an annual inflation rate of 6%. How much will the college education cost per year when the child turns 18?

You may work on the project in groups or pairs if you wish. Each group should be no more than 4 students. You may select any of your classmates to work on the project together; however, you must still submit your own individual version and mention the contributions of others to the project in your group. Each project should be submitted as an Excel sheet attachment.