A recent online survey commissioned by Reliance-IMRB revealed that building a retirement corpus is the top priority for Indians. The survey found that 46% of respondents feel that retirement planning is the most important financial goal followed by children’s education (22%), buying a house (20%) and marriage (12%).
However, a number of studies show that 4 out of 5 people in India are not prepared for their retirement. In an earlier interview with Cafemutual, Himanshu Vyapak, Deputy CEO, had said, “Only 11% of the Indian workforce has any security in the form of pensions etc. on retirement. In Australia, the retirement assets are 147% of GDP. In most developed countries, retirement assets form 70% of GDP. In India, the entire retirement space constitutes less than 15% of GDP.”
Retirement planning comprises two phases - accumulation phase (a period in which a client invest to build retirement corpus) and disbursal phase (a period in which a client spends the accumulated corpus till his life expectancy).
Here is a case study to help you prepare a retirement plan in Excel:
Ajay earns Rs.50,000 per month out of which he spends Rs.35,000. He is 30 years old and plans to retire at 60. His life expectancy is 90 years. Ajay wants to invest monthly in an equity mutual fund scheme. He expects to get 15% CAGR through equity funds. Post retirement, he would invest the accumulated corpus in an income fund from which he expects to get 9% per annum. Inflation throughout his life expectancy is 6% per annum.
Step 1: The value of money erodes over time due to inflation. Hence, we will first find out the future value of current expenses by considering inflation.
To calculate it in MS Excel, you have to click on ‘fx’ which is on the top extreme left of the sheet. After you click on ‘fx’, a window would appear which will ask you to select a category. You can either select ‘all’ or ‘financial’ and just below that you will find an option to select a function. Choose ‘FV’ from the given list. It indicates future value.
Another window would appear (see below) asking you to key in Rate (rate of return in %), Nper (no. of term), pmt (payment per month), pv (present value) and type (indicates ‘set’ which means occurrence of cash flow. Use 1 for begin mode and 0 for end mode transactions).
Key in the data
Rate = 6% (inflation rate), Nper = retirement age – current age i.e. 60-30 = 30, Pmt =0, PV = -35000 (negative sign indicates cash outflow per month) and Type = 1.
This future value indicates that Ajay will require Rs.2.01 lakh per month to maintain a similar standard of living post retirement.
Step 2: Post retirement, the accumulated corpus which we have to find out, will be invested in an income fund yielding 9%. At the same time, inflation will grow at 6%. Hence, his monthly expense has to keep pace with inflation post retirement to maintain a similar standard of living throughout his life expectancy. Therefore, to calculate how much corpus is required at retirement age i.e. PV at 60, we will consider real rate of return.
Choose PV function to calculate it.
Real rate of return can be calculated through a formula ((1+rate of investment% / 1+inflation%)-1)*100 i.e. ((1+9%) / (1+6%)-1)*100 = 2.83%
Rate = 2.83%/12 (since it will be a monthly transaction)
Nper = retirement age – life expectancy i.e. 30 * 12 (monthly transaction),
PMT= Rs.2.01lakh, FV=0 and Type = 1
PV = 4,88,48,594 i.e. Rs.4.88 crore
Hence, Ajay will require Rs.4.88 at 60 as a retirement corpus.
Step3: The final step is to calculate how much Ajay needs to invest to accumulate Rs.4.88 crore.
Here, we need to calculate PMT as he wants to invest monthly. Go to function key and select PMT.
Rate = 15%/12 (rate of return from equity mutual fund)
Nper = 30*12= 360, PV= 0, FV, 48848594 and Type = 1
PMT comes out to Rs.6969 i.e. close to Rs.7000.
Hence, Ajay needs to start an SIP of Rs.7,000 per month to withdraw an inflation adjusted Rs.2.02 lakh per month post retirement to maintain the same standard of living.
This answer may vary as we have avoided decimals during calculation. Also, this is a basic case study. Let us know if you have any other queries on retirement planning which are more complex.
