---
tags: excel
---

# **How do you calculate your Retirement Corpus?**

You may have gone through a few of the retirement corpus calculators available online, getting surprised to find out how ludicrous the final number could be or starting out as a beginner in this journey trying to figure out your way through, looking for a starting point giving a brief about basic numbers to consider. This may be the place.

**Note** changing some of the numbers *[which we will discuss soon]* can drastically change the final corpus required.

Let me consider a case here to see how much a person may require based on when that person plans to retire.

Case in Hand
---

> I, a 25y old male belonging to a middle-income family working as a software engineer at a service IT firm, have started my investment journey, a few years back. Most of my early investments have been in safe options like FDs at bank/post office, PPF, PF, and recently have started following asset allocation + goal-based investing. It's around ₹ 5,00,000 in total.
> One of my financial goals is to retire early, say at 55, go back to my native - start living in the natural beauty back home. I don't spend a lot as my needs have always been very limited. I need ₹ 10,000 for Groceries, Electricity bill, water bill, wi-fi bill, telephone bill, and petrol for 6y old Activa. My monthly rent is around ₹ 8,000. I spend another 8,000ish on dining out, buying something for parents and/or partner, my shopping, online subscriptions, etc. Can this be called frugal? Perhaps, penny wise pound foolish? I don't know.
> I don't want to burden my next generation for my expenses once I retire. Hence, I need a corpus that will take care of me for my lifetime.
---

Important numbers we could gather which are necessary for further calculations -

1. Current Age of the person - 25
2. Age at which the person wishes to retire - 55
3. Age the person can expect to live up to - 80
4. Current investments - ₹ 5,00,000
5. Average monthly expenses - ₹ 26,000; approx - ₹ 30,000
6. Annual Average Expenses - 12*30000 = ₹ 3,60,000

So, the person would require about (Annual Expenses)*(Age expected to live up to - Retirement age). This turns out to be 3,60,000 * 25 = ₹ 90,00,000.

Easy math? **No**

This where things start to get tricky. Inflation - it is a devil which could ruin your retirement party. To put it simply, Inflation is when you pay ₹ 200 for haircut you used to get for ₹ 100 when you had hair. Whether we like it or not, inflation is going to eat into our hard-earned money. That's the reason, we encourage sensible investment to try and stay ahead of inflation.

We have talked about inflation, which is a bit simplistic; we will need to consider the rate of inflation. We will also need to consider the rate of return on Investments a person can make/have made. Mind you, the pre-retirement and post-retirement rates of return on investments can vary drastically.

Other inputs we will require are -

1. Inflation Rate - 7% (Generally it varies between 5-10% in India). We are assuming that this rate will not change till the person is alive. It makes our calculation simplistic. Don't expect this.
[Advise]Hence, goal-based investing following proper asset allocation with half-yearly/yearly review is suggested.
2. Pre-Retirement Return on Investments - 10%
3. Post-Retirement Return on Corpus Accumulated - 8%

Calculation
---
**Step 1 -**

Open Excel, Let's start adding the aforementioned 8 parameters/variables, i.e. Current Age, Retirement Age, etc, in a column, and appropriate values for each of the parameters in an adjacent column - see below.


| Variable | Value |
| ------------------------------------ | ---------- |
| Current Age | 25 |
| Retirement Age | 55 |
| Age you are expecting to live upto | 80 |
| Present value of annual expenses | ₹ 3,60,600 |
| Inflation Expectation | 7.0% |
| Current Investments | ₹ 5,00,000 |
| Pre-retirement return on investments | 10.0% |
| Post-retiremnt return on investments | 8.0% |

*Note: You can give a name to a particular cell value. This may be helpful in the future when you refer back to the calculations.
e.g. 25 Value is in C2 cell. Instead of using C2 in further calculations we can name the cell and refer to that particular name. Let's call the cell current_Age.*


| Variable | Cell Name |
| ------------------------------------ | ------------------ |
| Current Age | current_Age |
| Retirement Age | retirement_Age |
| Age you are expecting to live upto | final_Age |
| Present Value of Annual Expenses | ann_Expenses |
| Inflation Expectations | inflation |
| Current Investments | current_Investment |
| Pre-retirement Return on investments | pre_Ret_Return |
| Post-Retirement Return on corpus | post_Ret_Return |

**Step 2 -**
Let's calculate the number of years the person has for accumulating the final corpus, number of years the person has to live off of the corpus.



We will have following set of numbers,


| Variable | Value |
| --------------------- | ----- |
| Pre Retirement Years | 30 |
| Post Retirement Years | 25 |

We shall name these two cells appropriately.

| Variable | Cell Name |
| --------------------- | -------------- |
| Pre Retirement Years | pre_Ret_Years |
| Post Retirement Years | post_Ret_Years |

**Step 3 -**
We will need to calculate the inflation-adjusted annual expenses. We can calculate as [(1+inflation)^(no. of years)]*annualExpenses. This will give us the amount required for the first year after retirement. An easy way of calculating this would be to use the Future Value formula in Excel. It calculates the future value of an investment based on a constant interest rate.

The arguments for FV are,
1. Rate - The interest rate per period. We need to use Inflation. 7.0% for the case we're considering.
2. Nper - The total number of years/payment periods. This will be pre-Retirement years. As seen from the calculation, the person has about 30 years before retirement.
3. Pmt - Amount to shell out periodically over the "Nper". This will be zero as we need to calculate the FV of present expenses.
4. PV - The present value, or the lump-sum amount. We need to enter (Annual Expenses). This will carry a negative sign. The value to be entered is - 3,60,000.



| Variable | Value |
| ------------------------------------ | ----------- |
| Projected Expenses at Retirement Age | ₹ 27,40,412 |

This value comes out to be ₹ 27,40,412. Yes, you read it right. 27.4 lakh will be required annually to maintain the SAME LIFESTYLE(we haven't consider [lifestyle inflation](https://www.investopedia.com/terms/l/lifestyle-inflation.asp)) at the time of the person's retirement. Inflation erodes purchasing power in long run.

Let's give some name here as well.
| Variable | Cell Name |
| ------------------------------------ | -------------- |
| Projected Expenses at Retirement Age | expense_At_Ret |

**Step 4 -**
We are ready to arrive at the final number. We know, 27.4 lakh will be required annually for post-retirement years. That's a periodic outflow of money over the next 25 years. We will need to calculate the present value of this series of cashflow at the time of retirement i.e. what would be the present value of this cashflow be at 55 years of the person's age. This will be our final figure. Excel has a beautiful in-built formula for calculating this - PV formula.
PV calculates the present value of a loan or an investment, based on a constant interest rate. You can use PV with either periodic, constant payments, or a future value that's your investment goal.

The arguments for PV are -
1. Rate - Real Rate of return. We will use [inflation-adjusted return](https://www.investopedia.com/terms/r/realrateofreturn.asp) as it will continuously be eating into the corpus. e.g. We have considered return on investments to be 8% and inflation at 7%.

$$
Real Rate Of Return = \frac{1 + Rate Of Return On Invesments}{1 + Rate Of Inflation}- 1
$$

$$
Real Rate Of Return = \frac{1 + 0.08}{1 +0.07}- 1 = 0.0093 = 0.93\%
$$

2. Nper - The total number of years/payment periods. This will be Post-Retirement years.
3. Pmt - Amount shell out periodically over the nper. This will be the inflation-adjusted annual expenses, with a negative sign (outflow).
4. FV - Future value. This shall be zero.



Please refer to cell names above.

| Variable | Value |
| ----------------- | ------------- |
| Retirement Corpus | ₹ 6,08,43,306 |

**What do we get?**
₹ 6,08,43,306. Yes, you read it right. If we consider that the initial investment grew at the pre-retirement rate, the person will still require 5.2 crores. 90 lakh vs 5.2 crores.

**Reminder** These calculations are not meant to give you a practical final number as we haven't consider a lot of realistic scenarios such as one mentioned above - lifestyle inflation, and few others such as change in geo-political scenario which can change investment scenarios, other goals which will require big sum of money. Please be mindful and take these calculations with a bag of salt.

Playing around with numbers will give you a better idea of what the requirement can look like. For example, let's see how changes in Inflation and/or Interest rate affect size of corpus required. Be mindful of these numbers as we have made plenty of assumptions, prominent one being constant inflation throughout the course.

| Rate of Return Post Retirement | Inflation at 6% | Inflation at 7% | Inflation at 8% |
| ---------------------------------------------------- | --------------- | --------------- | --------------- |
| 6% | ₹ 5,16,91,421 | ₹ 7,75,82,505 | ₹ 11,65,26,805 |
| 7% | ₹ 4,58,56,479 | ₹ 6,85,10,295 | ₹ 10,24,35,753 |
| 8% | ₹ 4,09,09,483 | ₹ 6,08,43,306 | ₹ 9,05,63,912 |
| Projected Annual Expenses at Retirement Age, Year =1 | ₹ 20,67,657 | ₹ 27,40,412 | ₹ 36,22,556 |

Let's also have a look at another interesting factor, the age at which a person decides to start retirement planning. The person is 25 and his annual expenses are ₹ 3,60,000. He thinks it's too early for him to think about retirement, let alone plan and invest for it. Let's see how it affects him.

The corpus he will require at the age of 55 is ₹ 6.08 crores, not considering his initial investments.
To calculate monthly investments, we will use PMT formula which tells us the amount one needs to *invest* periodically(monthly in our case) to reach a number in future.

The arguments in PMT are -
1. Rate - The interest rate per period. We need to divide pre-retirement rate of return, which is annualized return, by 12 as we are going to calculate monthly investment.
2. Nper - The total number of years/payment periods. This will be pre-Retirement years * 12. We're multiplying by 12 to get period over which the investments are going to grow. So this number becomes 30*12 = 360
3. PV - We need not bother as we have future value of retirement corpus with us, i.e. final amount the person will need at the retirement age, 55.
4. FV - We input Retirement corpus here.



The person needs to invest at least ₹ 27,000 monthly, on average - we haven't considered that disposable income can and will increase over years. Hence, starting small in the beginning and then stepping it up also works. So don't get discouraged by the number you get. This exercise is to give you a crude idea about financial aspect of retirement.

Doing this exercise with different numbers, for age the person starts investing at, gives us valuable insights.

| Age | Monthly Investments (At least) |
| --- | ------------------------------ |
| 25 | ₹ 26,916 |
| 30 | ₹ 45,856 |
| 35 | ₹ 80,124 |
| 40 | ₹ 1,46,798 |
| 45 | ₹ 2,97,021 |
| 50 | ₹ 7,85,713 |


The sooner we start the better it is. One wouldn't be interested in investing more into riskier assets at the age of 50 to get 10% return we have considered. There can be burden for children's education or loan that one may have taken for house/luxury car or a foreign vacation one may be considering. These things makes it difficult to invest huge amounts into riskier assets when person is closer to retirement. Hence, starting soon, however small it may be, is prudent.