Download our free NPS excel calculator. This calculator tells you the lump sum amount and regular pension that you can expect given your NPS contributions, age, expected portfolio returns and expected annuity returns.
Many such calculators are available online such as the NPS Trust Pension Calculator, HDFC NPS calculator, SBI NPS calculator etc. However with these calculators you cannot see the working behind the calculations. Our excel calculator lets you to do that. With the same inputs as the official NPS Trust Pension calculator, it should give you exactly the same answer.
We have also added some additional features such as
(1) accounting for a time-varying portfolio and hence changing expected portfolio returns over time
(2) suggestions for annuity rates under different types of annuity plans
(3) considering the impact of tax and
(4) allowing for increasing contributions over time.
For those looking to reach a certain level of monthly pension, the percentage allocated to buy annuity is an additional decision variable. Expected annuity rate and marginal income tax rate are not decision variables.
If you have any problems in downloading the NPS Excel Calculator or any questions about how to use it, please write to us in the comments. Hope you find the calculator useful!
If you want to know more about NPS rules we suggest tha you go through our in-depth post: All details on the NPS retirement planning scheme. For those interested in investing in NPS, please read our post on how to open your NPS account.
Many such calculators are available online such as the NPS Trust Pension Calculator, HDFC NPS calculator, SBI NPS calculator etc. However with these calculators you cannot see the working behind the calculations. Our excel calculator lets you to do that. With the same inputs as the official NPS Trust Pension calculator, it should give you exactly the same answer.
We have also added some additional features such as
(1) accounting for a time-varying portfolio and hence changing expected portfolio returns over time
(2) suggestions for annuity rates under different types of annuity plans
(3) considering the impact of tax and
(4) allowing for increasing contributions over time.
To make best use of this calculator, we suggest that you familiarise yourself with all National Pension Scheme details. Hope you find the NPS excel calculator useful!
Download the NPS Excel calculator
How to use the calculator?
Output
The NPS calculator generates the following output:
- Total Pension Wealth at retirement
- Lump sum amount that you can withdraw
- Monthly Pension/annuity - Pre and post tax
Inputs
The NPS calculator uses the following inputs:
Age and Expected age of retirement
Together these two inputs provide the amount of time for which the investor will stay invested.
The longer the time of investment, the larger will be the final pension corpus and eventual annuity amount.
The longer the time of investment, the larger will be the final pension corpus and eventual annuity amount.
Monthly contribution:
The amount of contribution every month by the NPS subscriber. If both the employer and employee are contributing to NPS, this should be the sum of both contributions
The higher the contribution, the more will be the final pension corpus and eventual annuity amount.
The higher the contribution, the more will be the final pension corpus and eventual annuity amount.
Growth in contribution every year
As an investor's salary/income increases with time, their savings towards retirement are also likely to increase. In the NPS excel calculator, we accommodate this by adding an additional input which is the % growth in monthly contribution every year i.e. by what percentage do your monthly contributions grow from one year to next.
This functionality is not provided by most online calculators and is an additional feature. However it can be disabled by putting in a value of 0%.
This functionality is not provided by most online calculators and is an additional feature. However it can be disabled by putting in a value of 0%.
The higher the growth rate in contributions, the larger will be the final pension corpus and eventual annuity amount.
Current Corpus
This is an input for existing NPS investors who would also have some corpus from the previous investments apart from their current regular contributions. Again this extra functionality can be disabled by putting a value of 0.
The higher the current corpus, the more will be the final pension corpus and eventual annuity amount.
Expected rate of portfolio return
This is the return that you expect to earn on your NPS portfolio over the duration of investment. As you can see it is a crucial input in computing the final pension portfolio but it is a lot less definite than other inputs. Hence users may want to play with a couple of different values and methods here. The NPS excel calculator allows the users to experiment with 4 different methods for arriving at expected portfolio returns. We discuss these methods in more detail in the next section ‘How to calculate NPS expected returns’.
As you would expect, a higher assumption on expected portfolio return, would mean a larger final corpus and monthly pension.
With the five inputs mentioned above, you can calculate the NPS pension corpus at the start of retirement. How this then translates into a regular pension will depend on the following inputs:
Percentage for which annuity is purchased
It is the percentage of the pension corpus which is used to purchase annuity. NPS withdrawal rules require investors to use (1) at least 40% of the pension corpus to buy annuity if the age of retirement is 60 or beyond and to use (2) at least 80% of the pension corpus to buy annuity if the age if retirement is less. Hence we have imposed these restrictions on the inputs that this cell can take.
Annuity rate
This is the rate at which annuity will be paid out. This multiplied by the amount used to purchase annuity will give the annual annuity amount. Again this is an assumption, like portfolio expected returns, so users may want to experiment with a couple of different values. We provide some estimates of annuity rates that you can use for different type of annuity options. Our methodology for coming up with these rates can be found in the section "What annuity rate to use"
At the end of this stage, we have all the inputs that we require to calculate the pre-tax output. The last step is to calculate the post-tax values for which we need the following input:
Marginal income tax rate at retirement
As we discussed in the NPS tax benefits post, lumpsum withdrawal from NPS corpus is now tax-free. Also there is no tax on the amount used to purchase annuity. So we only need to worry about tax on the regular annuity payouts. These are taxed at the marginal income tax rate of the financial year in which they are paid out. Hence our last input is the expected marginal income tax rate at retirement which helps us to calculate the post-tax annuity payout.
How to calculate NPS expected return
NPS expected portfolio returns are one of the most important inputs in the calculation of the final pension corpus. However unlike other inputs such as age, contribution etc. this is a lot more uncertain. And hence users may want to experiment with a couple of different assumptions.
The NPS excel calculator allows users to experiment with four different options:
- Static portfolio return: A constant expected portfolio return number for the entire period.This is the option that most internet calculators have where you input one expected portfolio return for the entire period.
- Derived, static portfolio return: This is a variation of the above method. In this also there is a constant expected portfolio return number but this is not specified directly. Instead the user can provide the asset class weights on each of the 4 asset classes available under NPS (Equities, Government Debt, Corporate Debt and Alternatives) and their expected asset class returns. The portfolio return is then just a weighted average of the asset class returns. One reason for preferring this method over the previous method is that assumptions on asset class expected returns are more easily available from different sources.
- Auto-choice option: As we discussed in the NPS investment options post, you can either choose the portfolio weights yourself or go with the Auto-choice option. In the Auto-choice option, weights change automatically based on the age of the subscriber. As the weights change, so will the expected portfolio return. We allow for this option in our NPS excel calculator. In this case a user just needs to specify the expected asset class returns and whether they want to go with the Aggressive, Moderate or Conservative option. The calculator automatically picks up the right weight matrix and generates a dynamic series of expected portfolio returns which is used in our calculation.
- Dynamic, Active choice option: In Auto-choice option the weights matrix is predetermined. But investors can also choose to create their own dynamic portfolio. If the users want to work with such an option in the NPS excel calculator, they can input their own set of custom weights together with the expected asset class returns. These weights should not exceed the cap set by NPS on various asset classes (discussed in the NPS investment options post) The calculator will then pick the custom weight matrix and multiply it with asset class expected returns to get a dynamic series of expected portfolio returns.
What annuity rate to use
Users need to provide their own estimate of annuity rate in the NPS excel calculator. However we have also provided some indicative annuity rates for 4 common annuity options:
However it is important to remember that what we are interested in are the annuity rates that will exist at the time of retirement while what we have are the current annuity rates. Future annuity rates will depend on the inflation and interest rates in the economy at that time. Also annuity markets in India are currently not that well-developed which partly explains the low annuity rates compared to inflation/other interest rates. This may also change with time as annuity markets develop.
With these caveats in mind, current annuity rates are still among the better estimates of future annuity rates. We can use these rates (with possibly some deduction if you are being conservative) in our calculations.
- Lifetime annuity without return of principal
- Lifetime annuity with return of principal on death
- Lifetime annuity for subscriber and their spouse without return of principal
- Lifetime annuity for subscriber and their spouse with return of principal
However it is important to remember that what we are interested in are the annuity rates that will exist at the time of retirement while what we have are the current annuity rates. Future annuity rates will depend on the inflation and interest rates in the economy at that time. Also annuity markets in India are currently not that well-developed which partly explains the low annuity rates compared to inflation/other interest rates. This may also change with time as annuity markets develop.
With these caveats in mind, current annuity rates are still among the better estimates of future annuity rates. We can use these rates (with possibly some deduction if you are being conservative) in our calculations.
Decision variables in the NPS Excel Calculator
The output of the NPS excel calculator is the pension wealth at retirement and the monthly pension. There are multiple inputs. If we change any input, then the output will be affected. However it is important to remember that not all input variables are decision variables.
An obvious example is the age of the subscriber. We know that if we change current age in the calculator then the final output will change. In particular if your current age is lower, all else equal, that will increase the time of investment and increase the final corpus. But in actuality, if you want to increase your eventual pension wealth, changing your age is not an option! The age is what it is. Hence if you want to take the pension corpus to a desired level, current age is not a decision variable.
An obvious example is the age of the subscriber. We know that if we change current age in the calculator then the final output will change. In particular if your current age is lower, all else equal, that will increase the time of investment and increase the final corpus. But in actuality, if you want to increase your eventual pension wealth, changing your age is not an option! The age is what it is. Hence if you want to take the pension corpus to a desired level, current age is not a decision variable.
A less obvious example is the expected portfolio returns. If given current contributions and time to investment, you are not able to meet the desired pension wealth/monthly pension criteria, it is tempting to increase the expected portfolio return by going for a more risky portfolio. However this is an incorrect approach to take. Your portfolio composition should be determined independently based on your age, your risk appetite etc. Just because you need more returns you should not opt for a more risky portfolio. So even expected portfolio return is not a decision variable.
If you are trying to reach a certain level of pension wealth, the decision variables in this calculator are: age of retirement, monthly contributions, their growth rate and current corpus. If you want to reach a certain level of pension wealth but are nor able to do so with your current choices, then the things you can change are 1. The fact that you can retire later and hence stay invested for more time 2. Increase your monthly contributions or make sure they grow at a faster rate or 3. Increase current corpus by investing a lump sum amount today itself.
If you are trying to reach a certain level of pension wealth, the decision variables in this calculator are: age of retirement, monthly contributions, their growth rate and current corpus. If you want to reach a certain level of pension wealth but are nor able to do so with your current choices, then the things you can change are 1. The fact that you can retire later and hence stay invested for more time 2. Increase your monthly contributions or make sure they grow at a faster rate or 3. Increase current corpus by investing a lump sum amount today itself.
For those looking to reach a certain level of monthly pension, the percentage allocated to buy annuity is an additional decision variable. Expected annuity rate and marginal income tax rate are not decision variables.
Conclusion
Download link for the NPS Excel CalculatorIf you have any problems in downloading the NPS Excel Calculator or any questions about how to use it, please write to us in the comments. Hope you find the calculator useful!
If you want to know more about NPS rules we suggest tha you go through our in-depth post: All details on the NPS retirement planning scheme. For those interested in investing in NPS, please read our post on how to open your NPS account.
Comments
Post a Comment
Thank you for you feedback/question. We will post it once it has been approved by the moderator.