4. On retirement, how much will Mr. Z have in his retirement corpus?
Case- Mr. Z, aged 52 years, is working in a leading company. His net savings are Rs. 50,000 p.m. Based on salary growth and other factors, he expects this to rise by 20% p. till his retirement at age 60. This does not include monthly contributions of Rs. 9,000 (Rs.4000 own contribution; Rs.5000 employer contribution) to various funds towards retirement corpus. These are expected to grow by 20% p. till retirement. The retirement corpus by the end of the year will be Rs. 12 lakhs, entirely in debt, which will yield 8 % p. on average. Besides his own residential house and the retirement corpus, his savings and investments will amount to Rs.50 lakhs by the end of the year, 30% of which will be in equity. He has a practice of investing, at the end of each year, his disposable savings into debt and equity in the ratio of 80:20. In the long run, he expects equity to yield 15% and debt to yield 8.5%. At the end of age 55, he expects an outflow of funds amounting to Rs5lakhs, which he hopes to meet out of annual savings. He expects inflation of 10% and post-retirement investment return on his portfolio at 11%. His current expenses are Rs40,000 per month. Assume zero date as the end of age 5Calculations are to be done on annual basis. Ignore taxation and interest income on savings and contributions during the year.
The retirement corpus will be calculated based on the given savings, contributions, and investment returns.
To calculate Mr. Z’s retirement corpus using Excel formulas, we will follow these steps:
Create a table with the following columns:
– Year
– Net Savings
– Contributions
– Total Savings
– Equity Portion
– Debt Portion
– Equity Returns
– Debt Returns
– Total Returns
– Inflation-adjusted Returns
– Retirement Corpus
In the “Year” column, starting from zero (age 52), list the years until retirement (age 60).
In the “Net Savings” column, enter the formula to calculate the net savings for each year:
– In cell B2, enter “=50000*(1+0.20)^A2” (assuming the Year column is in column A).
– Drag the formula down to fill the column with values for each year.
In the “Contributions” column, enter the formula to calculate the contributions for each year:
– In cell C2, enter “=9000*(1+0.20)^A2” (assuming the Year column is in column A).
– Drag the formula down to fill the column with values for each year.
In the “Total Savings” column, enter the formula to calculate the total savings for each year:
– In cell D2, enter “=B2+C2” (assuming the Net Savings and Contributions columns are in columns B and C, respectively).
– Drag the formula down to fill the column with values for each year.
In the “Equity Portion” column, enter the formula to calculate the equity portion of the total savings for each year:
– In cell E2, enter “=D2*0.3” (assuming the Total Savings column is in column D).
– Drag the formula down to fill the column with values for each year.
In the “Debt Portion” column, enter the formula to calculate the debt portion of the total savings for each year:
– In cell F2, enter “=D2-E2” (assuming the Total Savings and Equity Portion columns are in columns D and E, respectively).
– Drag the formula down to fill the column with values for each year.
In the “Equity Returns” column, enter the formula to calculate the equity returns for each year:
– In cell G2, enter “=E2*0.15” (assuming the Equity Portion column is in column E).
– Drag the formula down to fill the column with values for each year.
In the “Debt Returns” column, enter the formula to calculate the debt returns for each year:
– In cell H2, enter “=F2*0.085” (assuming the Debt Portion column is in column F).
– Drag the formula down to fill the column with values for each year.
In the “Total Returns” column, enter the formula to calculate the total returns for each year:
– In cell I2, enter “=G2+H2” (assuming the Equity Returns and Debt Returns columns are in columns G and H, respectively).
– Drag the formula down to fill the column with values for each year.
1In the “Inflation-adjusted Returns” column, enter the formula to calculate the inflation-adjusted returns for each year:
– In cell J2, enter “=(1+I2)/(1+0.10)^(A2-52)” (assuming the Year column is in column A).
– Drag the formula down to fill the column with values for each year.
1In the “Retirement Corpus” column, enter the formula to
calculate the retirement corpus for each year:
– In cell K2, enter “=1200000*J2” (assuming the Inflation-adjusted Returns column is in column J).
– Drag the formula down to fill the column with values for each year.
1The retirement corpus at the time of retirement (age 60) will be the value in the last row of the “Retirement Corpus” column.
The correct answer to the question is Rs. 48,65,917, which is the retirement corpus at age 60 (the value in the last row of the “Retirement Corpus” column).