How to calculate the retirement corpus you will need using MS Excel

Capitalstars Investment Advisor

Calculating the present value of an amount gets complicated if the investment generates a series of payments over a period of time.

An ideal retirement corpus should take care of all your postretirement expenses. But can you calculate the amount required? It involves taking into account life expectancy, interest rates, inflation and the time value of money and can be a bit tricky. Here we explain how to use MS Excel to calculate the amount easily. But first, let’s understand some basics.

The concept of the time value of money states that the worth of a rupee received today is more than a rupee received at a later date because of its earning potential. The concept of time value has two elements: Compounding and discounting. Compounding helps to estimate future values whereas discounting helps to estimate present values. For calculating your retirement corpus, it is the present value that matters.

Let us look at an example: An investment product promises Rs 8 lakh in 10 years if you invest Rs 4 lakh today; given interest/FD rates of 8% per annum, will this investment product be profitable? You will have to find out the present value of Rs 8 lakh at an 8% discount rate to arrive at the right answer. Present value is calculated by dividing Rs 8 lakh by (1+r) ^n, where ‘r’ is the discount rate (or interest rate) and ‘n’ is the tenure of investment. be Rs 3.7 lakh. Given that the present value of the amount that the product promises to pay (fund inflow) is less than the amount invested (fund outflow), the product is not profitable. In other words, the net present value of the investment product is negative. The net present value is the difference between the present value of cash inflows and the present value of cash outflows.

If the same Rs 4 lakh is invested in an FD for 10 years, offering 8% annual interest, the maturity proceeds work out to be Rs 8.63 lakh (assuming no tax)— Rs 63,000 higher than the aforementioned investment product.

Calculating the present value of an amount gets complicated if the investment generates a series of payments over a period of time. To calculate the current worth of such an investment, the present value of each payment in the entire series of payments needs to be derived. Technically, one needs to find out the present value of an annuity.

Estimating one’s retirement corpus involves calculating the present value of an annuity. This is because one expects to generate a stream of payments—monthly, quarterly or annually—from one’s retirement corpus for a given number of years at a certain rate. Such a stream of payments seeks to take care of one’s post-retirement expenses—based on one’s current expenses and assumed inflation rate.

A 38-year-old with a current annual expense of Rs 6 lakh can calculate his annual expenditure requirements when she retires at the age of 60, based on an assumed annual inflation rate over 22 years (the period after which she will retire). For instance, at 5% assumed inflation she will need Rs 17.5 lakh—6 lakh x (1+5%)^22. The ideal retirement corpus must generate a stream of Rs 17.5 lakh annually for 25 years after retirement, assuming a life expectancy of 85 years. Such a corpus can be arrived at by adding the present value of each stream of Rs 17.5 lakh discounted at an appropriate rate. The appropriate rate is generally the average long-term (10-year) yield on government securities. Additionally, post-retirement inflation also needs to be taken into account.

Although the methodology appears complex, MS Excel’s NPV function can help you do the calculations easily. NPV requires you to input the discount (or interest) rate and the series of expected inflows or estimated expenses.

At a 7% discount rate and assuming no inflation, the present value of the annuity works out to be Rs 2.04 crore. So, in our example, the working professional will have to accumulate Rs 2.04 crore for his retirement. However, if we assume postretirement inflation of 4.5% per annum, he will have to accumulate Rs 3.12 crore.

One can play with the numbers to see how changes in inflation, discount or interest rates changes the desired corpus.