Mini Case shown below to answer questions

1. Question-How to calculate the $750 Social Security payment which will increase at a rate of 4% due to inflation over the next 20 years?

2. Question-How to calculate how the 4% inflation will decrease the $180,000 investment portfolio even though it is earning 9% interest?

3. Can Mr. Road safely spend all the interest from his investment portfolio? How much can he withdraw at year-end from that portfolio if he wants to keep its real valueintact?

Would prefer these responses in Excel showing the formulas being used.

Old Alfred Road, who is well-known to drivers on the MaineTurnpike, has reached his seventieth birthday and is ready toretire. Mr. Road has no formal training in finance but has savedhis money and invested carefully. Mr Road owns his home-the mortgage is paid off-and dose not want tomove.He is a widower,and he wants to bequeath the house and anyremaining assets to his daughter. He has accumulated savings of $180,000, conservatively invested.The investments are yielding 9 percent interest. Mr. Road also has$12,000 in a savings account at 5 percent interest. He wants tokeep the savings account intact for unexpected expenses oremergencies. Mr. Road's basic living expenses now average about $1,500 permonth, and he plans to spend $500 per month on travel and hobbies.To maintain this planned standard of living, he will have to relyon his investment portfolio. The interest from the portfolio is$16,200 per year (9 percent of $180,000), or $1,350 per month. Mr. Road will also receive $750 per month in social security payments for the rest of his life. These payments are indexed forinflation. That is, they will be automatically increased inproportion to changes in the consumer price index. Mr. Road's main concern is with inflation. The inflation rate has been below 3 percent recently, but a 3 percent rate is unusually low by historical standards. His social security payments will increase with inflation, but the interest on his investment portfolio will not. What advice do you have for Mr. Road? Can he safely spend all the interest from his investment portfolio? How much could he withdraw at year-end from that portfolio if he wants to keep its real value intact? Suppose Mr. Road will live for 20 more years and is willing to use up all of his investment portfolio over that period. He also wants his monthly spending to increase along with inflation over that period. In other words, he wants his monthly spending to stay the same in real terms. How much can he afford to spend per month? Assume that the investment portfolio continues to yield a 9 percent rate of return and that the inflation rate will be 4 percent.