ABCDEFGHIJKLMNOPQRSTUVWXYZAA
1
Utility for Estimated NPS corpus and UPS calculation and comparative analysis (v1.0)
2
N.B.: Recommended for those who have joined Govt service in last few years preferably within the period of 7th CPC.
(All figures in INR)Developed by : R@mit. All rights reserved
3
Instructions: 1. Enter data in the text box with yellow background only
2. Please ensure that you enter correct input data.
4
5
Enter basic as on year of joining(in Rs.):21060
6
7
Enter year of joining(01-Apr-2004 onwards):2010
8
9
Enter age as on year of joining:26
10
11
Enter DA at the time of joining (in %):0.35
12
13
Your year of retirement is :2044
14
Assumptions and considerations of the model:
15
1. Promotional benefits not considered.
2. Increment of 3% considered on Basic pay (col D) y-o-y basis.
3. DA (col[E]) is estimated with 5.5 % y-o-y increase considering trend for the past one decade.
4. Pay revision with estimated fitment factor of 2 considered every 10 years from 2006 onwards.
5. Employer contribution increased from 10% to 14% of Basic + DA from year 2019.
6. Retirement age : 60 years
7. Max. 40 years service is considered.
8. NPS contribution pertains to only NPS Tier-I contribution.
9. UPS Independent Corpus (col[N]) comprises of employee contribution @10% of Basic+DA with matching 10% contribution by Govt., 8.5% Govt contribution is not part of this corpus but is part of Pool Corpus.
16
Sl. No.
[A]		Year
[B]		Age (in years)
[C]		Basic pay(in Rs.)
[D]		DA Rate (in %)
[E]		DA (in Rs.)
[F]
Employee contribution per month in NPS
[G]		Employer Contribution per month in NPS
[H]		Total NPS contribution as on start of respective year
[I]=[H] +[G]		Value of NPS contribution at the time of retirement with 8% returns [J]=[I]*(1.08^(60-[C]))Value of NPS contribution at the time of retirement with 10% returns [K]
=[I]*(1.10^(60-[C]))		Value of NPS contribution at the time of retirement with 12% returns [L]
=[I]*(1.12^(60-[C]))		Value of NPS contribution at the time of retirement with 15% returns [M]
=[I]*(1.15^(60-[C]))		Value of UPS IC contribution (20% of basic +DA) at the time of retirement with returns @8%[N]
=0.2*12*([D]+[F])*(1.08^(60-[C]))
17
12011272100035.00%735028352835680408624781580240286390868516161580240
18
22012282160040.50%874830353035728358548731537826273727363778191537826
19
32013292220046.00%1021232413241777898453831493105261021259231141493105
20
42014302280051.50%1174234543454829018342021446569248370254890091446569
21
52015312340057.00%1333836743674881718215151398668235857350764971398668
22
6201632697000.00%06970697016728014431422412344399529383749762412344
23
7201733717005.50%39447564756418154414501882380046387141379035932380046
24
82018347880011.00%86688747874720992315526662501902399695379470202501902
25
92019358110016.50%133829448944822675615529302456830385486074645572456830
26
102020368350022.00%18370101871426229338618604112889770445319183982142408141
27
112021378600027.50%23650109651535131579218541612827697427972378605232356414
28
122022388850033.00%29205117711647933899018429352759475410188973373612299563
29
132023399110038.50%35074126171766436338018291932689100392589968393572240917
30
1420244012310044.00%54164177262481751052023795133434525492462983554502862105
31
1520254112670049.50%62717189422651854552023542993336348469843077637092780290
32
162026422000000.00%0200002800057600023017073202512442942071282612668760
33
172027432060005.50%11330217333042662591023158803163646429752667355872636371
34
1820284421210011.00%23331235433296067804123229313115581415666063448532596318
35
1920294521840016.50%36036254443562173277623244883060986401089659626412550822
36
2020304622490022.00%49478274383841379020923209963000816386183855912842500680
37
2120314723160027.50%63690295294134185043523128642935933371086852325472446611
38
2220324823850033.00%78705317214440991355023004752867112355917048877232389260
39
2320334924560038.50%94556340164762297964922841882795054340775945577122329211
40
2420345025290044.00%1112763641850985104882722643392720387325749742430902266989
41
2520355126040049.50%1288983893054502112117822412402643680310911639441662203066
42
262036523000000.00%0300004200086400015992041852061213923226429961543384
43
272037533090005.50%16995326004563993886616090511829583207553324974011524653
44
2820385431820011.00%350023532049448101722216142031802070200781523528961501725
45
2920395532770016.50%540713817753448109949916155251770754193769322114851475628
46
3020405633750022.00%742504117557645118584016133221736188186594220740421446824
47
3120415734760027.50%9559044319620471276387160788016988711793232#ERROR!1415726
48
3220425835800033.00%1181404761466660137128315994651659253172013818135221382711
49
3320435936870038.50%1419505106571491147067115883241617738164715116912711348115
50
3420446037970044.00%1670685467776548157469215746921574692157469215746921312243
51
352045000.00%000000000
52
362046000.00%000000000
53
372047000.00%000000000
54
382048000.00%000000000
55
392049000.00%000000000
56
402050000.00%000000000
57
TOTAL CONTRIBUTION
NPS CORPUS (WITH 8% RETURN)NPS CORPUS (WITH 10% RETURN)NPS CORPUS (WITH 12% RETURN)NPS CORPUS (WITH 15% RETURN)UPS IC (with 8% return)
58
22,657,86259,748,66580,191,363109,718,125#ERROR!69,694,057
59
UPSN.B.: Assuming UPS IC with 8% return (Grand total of Col[N]) & IC=BC
60
SCENARIO-I : No final WithdrawalSCENARIO-I
61
Monthly pension (approx) excl DR 273,384NPSANNUITY CORPUS (min. 40%)23,899,46632,076,54543,887,250#ERROR!
62
Lumpsum payment3,718,022LUMPSUM CORPUS (balance 60% max.)35,849,19948,114,81865,830,875#ERROR!
63
SCENARIO-II : With max final Withdrawal of 60% of ICMonthly pension (say @ 5% on annuity)99,581133,652182,864#ERROR!
64
Monthly pension (approx) excl DR109,354SCENARIO-II
65
Final Withdrawal amount (max 60% of IC, Grand total Col[N]), [X]*41,816,434
Total , [X]+[Y]
ANNUITY CORPUS (70%)41,824,06556,133,95476,802,688#ERROR!
66
Lumpsum payment, [Y]3,718,02245,534,457LUMPSUM CORPUS (balance 30% max.)17,924,59924,057,40932,915,438#ERROR!
67
* IC may be significantly higher than BC if the employee selects higher equity exposure for medium to long term in initial years of employment.
IC: Independent Corpus; BC: Benchmark Corpus		Monthly pension (say @ 5% on annuity corpus of 70% of total corpus, say)174,267233,891320,011#ERROR!
68
69
70
Disclaimer: The model presents a conservative estimate only. The actual returns are expected to be higher, but the same is not guaranteed. This model is intended for use in estimation purpose only.
71
Webhost courtesy: www.netvuze.com
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100