Everyone wants to retire rich but nobody knows how much money they need to secure their retirement days.
In fact, a survey conducted Mackenzie Investment in Canada shows that 75% people want to know how much they should save to fund their retirement. These investors are open to work with financial professionals to plan for retirement. While the study was carried out in Canada, it has great relevance in India, as retirement is top priority for Indian clients followed closely by children’s education/marriage, according to a Cafemutual 2020 survey.
This provides a huge opportunity to MFDs to tap this market. Hence, we have worked on a case study to help you do retirement planning through a user friendly software Microsoft Excel.
To start with, retirement planning comprises two phases - accumulation phase (a period in which a client invests 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:
Let us assume, one of your clients is Cheteswar. He 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 70 years, the current average expectancy in India according to World Bank data published in 2018. Cheteswar wants to invest monthly and expects to get 10% CAGR on his investments. Post retirement, he would invest the accumulated corpus largely in fixed income instruments from which he expects to get 7% per annum. Also assume that inflation throughout his life expectancy is 5% 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 will 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 (retirement age – current age), 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 = 5% (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 Cheteswar will require Rs 1.51 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 largely in fixed income products yielding 7%. At the same time, inflation will grow at 5%. To fund post retirement expenses, Cheteswar’s monthly income has to keep pace with inflation to maintain a similar standard of living throughout his life expectancy. Therefore, to calculate how much corpus is required at retirement age i.e. at 60, we will consider the real rate of return.
Choose the PV function to calculate it.
Real rate of return can be calculated through a formula:
[ { (1+rate of investment%) / (1+inflation%) } -1] * 100
This means [ { (1+7%) / (1+5%) } - 1] * 100 = 1.90%
Rate = 1.90%/12 (since it will be a monthly transaction)
Nper = retirement age – life expectancy i.e 10*12 (monthly transaction) = 120
PMT= Rs 1.51 lakh
FV=0
Type = 1
PV = 1,65,16,893 i.e. Rs.1.65 crore
Therefore, Cheteswar will require Rs 1.65 crore at the age of 60 to fund his post retirement expenses.
Step3
The final step is to calculate how much Cheteswar needs to invest today to accumulate Rs 1.65 crore at his retirement age. Here, we need to calculate PMT as he wants to invest monthly.
Go to the function key and select PMT.
Rate = 10%/12 (rate of return from equity mutual fund)
Nper = 30*12= 360
PV= 0
FV =1,65,16,893 and Type = 1
This shows that Cheteswar can secure his post retirement life with an SIP of nearly Rs.7,250 per month if his investments deliver 10% CAGR. This will help him maintain the same standard of living throughout his life span.
Now, to earn this 10% CAGR in the long-term, investing in a fund that has exposure to equity will be ideal. Various investment vehicles can offer exposure to equity. However, a retirement mutual fund scheme can be a good choice. These schemes invest in a mix of equity and debt assets and are well placed to generate relatively higher returns in long run. Such funds also mandates a disciplined approach. This is an extremely important factor as investors often sideline retirement planning and use the corpus for other short-term needs such as buying a house, buying a car, children education and so on.
