Could you please help with A, B, C ,D in EXCEL please

Question 11 answer all parts) John has just turned 20 and his rich relatives are thinking of giving him an investment while he is young, which will pay for his retirement. They hope that by investing early. the investment they make for him will grow sufficiently to guarantee him a comfortable retirement. They therefore approach a financial advisor to help them make the calculations. The plan is for John to retire on his 70th birthday (i.e., in 50 years' time). His life expectancy is 85 years. During his retirement (ie.. until his 85 birthday), they plan for John to receive 30,000 per year, starting on his 71" birthday The interest rate that applies to his retirement period is 5% per year. a) Find the amount of money that John would need to have when he is 70, in order to fund an annuity which would give him the desired cash flows from 71" birthday to his 85 birthday (10 marks) b) John's financial advisor suggests investing in a AAA-rated government zero- coupon bond, which would provide him with enough money upon its maturity. when he is 70, in order to purchase the annuity in part a). The annualized yield to maturity of a set of these zero-coupon bonds is observed as follows: Maturity(years) 20 40 50 60 Zero-Coupon YTM 3.60% 3.80% 4.00% 4.20% 4,40% Calculate how much John would need to invest in a suitable zero-coupon bond at the age of 20, in order to be able to fund this retirement plan. (5 marks) 30 c) John's relatives become concerned that his retirement pension might not be enough to live on over time due to inflation. They therefore instruct the financial advisor to present an alternative plan, in which John retires at 70, and receives a yearly pension which would give him 30,000 on his 71" birthday, and afterwards would give him annual payments which grew at a rate of 2% every year, finishing on his 85h birthday. The interest rate that applies to his retirement period is 5% per year. They still plan to fund this by investing in a suitable zero-coupon bond, which would be bought when John is 20, and which would mature when he is 70. How much would need to be invested in a suitable zero-coupon bond when John is 20. to pay for this alternative retirement plan? (10 marks) Question 11 continued overleaf PTO Page 3 of 7