Question

Hi! Please show how to do in excel ONLY NEED QUESTIONS 6 AND 7! I ALREADY HAVE 1-5 COMPLETED Part 4: Financing Your Retirement You will graduate in a few years and start working and it’s never too early to start planning for your retirement and other financial events. Let’s fast forward to the beginning of your career. Here’s some assumptions to help you get started. Your starting annual salary will be $60,000. You plan to work for 43 years before retiring. You expect your salary to grow at an annual average rate of 4% after year 1 on the job. When you retire you want a 22-year retirement annuity that begins 43 years from today with an equal annual payment equal to 80% of your final working year salary. Assume the first retirement annuity payment would occur immediately upon retirement 43 years from today. You realize your purchasing power will decrease over time during retirement. Assume any retirement fund savings will earn an 7.8% compounded annually before and after retirement Answer the following questions to help finalize your retirement planning. 1. What is your expected final year working salary? 2. What is your desired annual retirement income? 3. How much will you need at retirement 43 years after the beginning of your career to fund your desired retirement annuity? 4. How much will you have to deposit annually in the form of an equal annual end of the year deposit over 43 years to fund your desired retirement annuity? 5. Re-work questions 3 and 4 assuming you would like to leave your grandkids $200,000 when you die 22 years after retirement. 6. Now, forget the grandkids and imagine you get $25,000 in graduation gifts from your family that you deposit into your retirement savings account at the beginning of your work career. We-work the previous question (#5) with this new initial deposit. How large does your annual deposit over your working career need to be in addition to the initial $25,000? 7. Looking at your answers in the last two questions, the annual amount, while doable, might be a bit of a financial stretch for you early in your career. Let’s redo the scenario in the last question where you have the $25,000 initial deposit but will deposit $5000 at the end of year for the first 10 years and then another equal annual amount at the end of each year for the remaining working years. How large does this second annual deposit need to be to meet your retirement savings goal from question #3?

Answer 1

Formulas Used:-

	Starting salary	60000
	years to retirement	43
	years after retirement	22
	Rate of Interest	0.078
	salary Grotrh rate	0.04
	Final working Salary	=C1*(1+C5)^43
	Amount to require in retirement account at the time of retirement	=PV(C4,C3,-C6*80%)
6)	Graduation Gift	25000
	Amount of annual deposites require	=PMT(C4,C2,C8,-C7)
7)	amount to be deposited till 10 years	5000
	Value of the initial annual deposites after 10 years	=FV(C4,10,-C10)
	Second annual deposites require	=PMT(C4,C2-10,C11,-C7)