Pension calculator in Excel for central government employees: pension, commutation, gratuity, family pension and DR, with OPS vs UPS

A central government employee's retirement benefits come from a handful of formulas: the basic pension, Dearness Relief (DR) on it, the commutation lump sum, gratuity and family pension. Each one is short, and all of them fit on one Excel sheet. This post lays out that sheet: the inputs in column B, the formula for every output, and the result for one example employee. Then it puts the same employee through the Unified Pension Scheme (UPS), so you can see where the two differ.

Scope: central civil employees. The old pension comes from the Central Civil Services (Pension) Rules, 2021, which cover employees who joined on or before 31 December 2003. Railway servants, the All India Services, defence pensioners and state government employees have their own rules and are out of scope. There is no file to download: copy the cells below into a blank sheet. All figures are estimates for information, not advice.

Source: Gazette of India (Extraordinary), G.S.R. 868(E), 20 Dec 2021: CCS (Pension) Rules, 2021, rules 2, 31, 32, 44, 45 and 50

The example employee

A Level 10 officer retires at 60 on 31 December 2026. The last basic pay is ₹1,01,400, a cell in the Level 10 row of the 7th CPC pay matrix, and it was the same for all of the last twelve months. Qualifying service is exactly 23 years. DA and DR are 63% from 1 July 2026, after the Union Cabinet approved the 3-point rise on 28 October 2026.

Source: PIB, Ministry of Finance, 28 Oct 2026: Cabinet approves DA to employees and DR to pensioners w.e.f. 01.07.2026 (increase of 3% over the existing 60%)

Try it on your numbersSarkari Pay CalculatorIn-hand salary for any 7th CPC pay level: today, after 5 years and after 10 years

Inputs (column B)

Yellow cells in your own sheet. Change them to your own figures.

Input: Last basic pay (emoluments)
Example: 101400
Where it comes from: Rule 31: basic pay drawn just before retirement, plus non-practising allowance for doctors. DA is not included.

Input: Average basic pay of the last 10 months
Example: 101400
Where it comes from: Rule 32. Higher than B2 only if your pay fell in those months.

Input: Average basic pay of the last 12 months
Example: 101400
Where it comes from: UPS only: the 'P' in the payout formula, certified by your Head of Office.

Input: Qualifying service, completed years
Example: 23
Where it comes from: Your service book.

Input: Months beyond the completed years
Example: 0
Where it comes from: 0 to 11.

Input: DA / DR rate
Example: 63%
Where it comes from: 63% from 1 July 2026.

Input: Share of pension to commute
Example: 40%
Where it comes from: Up to 40% (Rule 5(1), Commutation Rules). You can choose less.

Input: Commutation factor
Example: 8.194
Where it comes from: Age next birthday 61, which applies when you retire at 60.

Cell	Input	Example	Where it comes from
B2	Last basic pay (emoluments)	101400	Rule 31: basic pay drawn just before retirement, plus non-practising allowance for doctors. DA is not included.
B3	Average basic pay of the last 10 months	101400	Rule 32. Higher than B2 only if your pay fell in those months.
B4	Average basic pay of the last 12 months	101400	UPS only: the 'P' in the payout formula, certified by your Head of Office.
B5	Qualifying service, completed years	23	Your service book.
B6	Months beyond the completed years	0	0 to 11.
B7	DA / DR rate	63%	63% from 1 July 2026.
B8	Share of pension to commute	40%	Up to 40% (Rule 5(1), Commutation Rules). You can choose less.
B9	Commutation factor	8.194	Age next birthday 61, which applies when you retire at 60.

Outputs: the old pension (CCS (Pension) Rules, 2021)

Our formulas, written from the rules cited in each step below. ROUNDUP to the next rupee follows rules 44(9) and 50(4) and the DR orders; ROUNDDOWN on the commuted portion follows the rule that paise are ignored. Monthly figures are before income tax.

B12

Output: Pay for pension
Formula: =MAX(B2,B3)
Example result: ₹1,01,400

B13

Output: Basic pension
Formula: =IF(B5>=10,MIN(MAX(ROUNDUP(B12*50%,0),9000),125000),0)
Example result: ₹50,700

B14

Output: DR on pension
Formula: =ROUNDUP(B13*B7,0)
Example result: ₹31,941

B15

Output: Monthly pension, no commutation
Formula: =B13+B14
Example result: ₹82,641

B16

Output: Commuted portion
Formula: =ROUNDDOWN(B13*B8,0)
Example result: ₹20,280

B17

Output: Commutation lump sum
Formula: =B16*B9*12
Example result: about ₹19,94,092

B18

Output: Monthly pension after commuting
Formula: =B13-B16+B14
Example result: ₹62,361

B19

Output: Six-monthly periods for gratuity
Formula: =MIN(B5*2+INT(B6/6)+IF(MOD(B6,6)>=3,1,0),66)
Example result: 46

B20

Output: Retirement gratuity
Formula: =MIN(ROUNDUP(B2*(1+B7)*B19/4,0),2500000)
Example result: ₹19,00,743

B21

Output: Family pension, enhanced rate
Formula: =MIN(MAX(ROUNDUP(B12*50%,0),9000),125000,B13)
Example result: ₹50,700

B22

Output: Family pension, normal rate
Formula: =MIN(MAX(ROUNDUP(B12*30%,0),9000),75000)
Example result: ₹30,420

B23

Output: DR on normal family pension
Formula: =ROUNDUP(B22*B7,0)
Example result: ₹19,165

Cell	Output	Formula	Example result
B12	Pay for pension	=MAX(B2,B3)	₹1,01,400
B13	Basic pension	=IF(B5>=10,MIN(MAX(ROUNDUP(B12*50%,0),9000),125000),0)	₹50,700
B14	DR on pension	=ROUNDUP(B13*B7,0)	₹31,941
B15	Monthly pension, no commutation	=B13+B14	₹82,641
B16	Commuted portion	=ROUNDDOWN(B13*B8,0)	₹20,280
B17	Commutation lump sum	=B16B912	about ₹19,94,092
B18	Monthly pension after commuting	=B13-B16+B14	₹62,361
B19	Six-monthly periods for gratuity	=MIN(B5*2+INT(B6/6)+IF(MOD(B6,6)>=3,1,0),66)	46
B20	Retirement gratuity	=MIN(ROUNDUP(B2(1+B7)B19/4,0),2500000)	₹19,00,743
B21	Family pension, enhanced rate	=MIN(MAX(ROUNDUP(B12*50%,0),9000),125000,B13)	₹50,700
B22	Family pension, normal rate	=MIN(MAX(ROUNDUP(B12*30%,0),9000),75000)	₹30,420
B23	DR on normal family pension	=ROUNDUP(B22*B7,0)	₹19,165

Step 1: basic pension

Rule 44(1): after at least ten years of qualifying service, the pension is 50% of emoluments or average emoluments, whichever is more beneficial, with a minimum of ₹9,000 and a maximum of ₹1,25,000 a month. Rule 44(8) treats nine years and nine months or more as ten years. Under these rules there is no cut for service below 20 or 25 years: ten years is enough for the full 50%. Rule 44(9) rounds any paise up to the next rupee.

Pay: the higher of ₹1,01,400 and ₹1,01,400 = ₹1,01,400
Pension: ₹1,01,400 × 50% = ₹50,700, which is between ₹9,000 and ₹1,25,000
DR at 63%: ₹50,700 × 63% = ₹31,941
Monthly pension: ₹50,700 + ₹31,941 = ₹82,641

The DR orders round a DR figure with paise up to the next rupee, which is why B14 uses ROUNDUP. Here the DR comes to exactly ₹31,941, so nothing changes.

Source: DoPPW OM No. 42/02/2024-P&PW(D)/E-9475, 24 Apr 2026: DR on basic pension/family pension, fraction of a rupee rounded to the next rupee (the order for the earlier 60% rate)

Step 2: commutation lump sum

Lump sum = commuted portion × factor × 12. The factor comes from the table in the CCS (Commutation of Pension) Rules, 1981, read at your age on the next birthday after the commutation becomes absolute. For someone retiring at 60 that age is 61, and the factor is 8.194 (DoPPW OM of 25 October 2022). For other ages, change B9: 8.287 at 60, 8.093 at 62 and 7.982 at 63.

Commuted portion: ₹50,700 × 40% = ₹20,280 a month
Lump sum: ₹20,280 × 8.194 = ₹1,66,174.32, × 12 = about ₹19,94,092, about ₹19.94 lakh
Pension left: ₹50,700 − ₹20,280 = ₹30,420
DR stays on the full ₹50,700, so it is still ₹31,941 (DoPPW OM of 25 October 2022)
Monthly pension after commuting: ₹30,420 + ₹31,941 = ₹62,361
The full ₹50,700 comes back 15 years after the reduction starts (Rule 10A)

Whether to commute is a separate question. Our commutation guide works out the break-even and the interest rate the lump sum implies.

Source: Gazette of India, 13 Nov 2010: DoPPW Notification S.O. 2806, CCS (Commutation of Pension) Amendment Rules, 2010 (Rule 10A and the commutation table)

Source: DoPPW OM No. 42/15/2022-P&PW(D)/2, 25 Oct 2022: commutation value for retirement at 60 is that of age 61

Source: DoPPW OM No. 42/15/2022-P&PW(D)/1, 25 Oct 2022: DR payable on the original basic pension before commutation

Commutation of pension calculator: factor table, break-even and tax

Step 3: retirement gratuity

Rule 45: gratuity = ¼ × emoluments × completed six-monthly periods of qualifying service, up to 16½ times the emoluments (66 periods, or 33 years). For gratuity, emoluments are basic pay plus the DA admissible on the date of retirement. A leftover of three months or more counts as one more six-monthly period, and you need at least five years of service. The ceiling has been ₹25 lakh since 1 January 2024 (DoPPW OM of 30 May 2024).

Emoluments: ₹1,01,400 + 63% DA ₹63,882 = ₹1,65,282
Periods: 23 years × 2 = 46
Gratuity: ₹1,65,282 × 46 ÷ 4 = ₹1,65,282 × 11.5 = ₹19,00,743, below the ₹25 lakh ceiling
Had the same officer served 33 years: ₹1,65,282 × 16.5 = ₹27,27,153, so the ceiling applies and ₹25,00,000 is paid

Source: PIB, Ministry of Personnel, 1 Jun 2024: maximum retirement and death gratuity raised from ₹20 lakh to ₹25 lakh (DoPPW OM dated 30.05.2024, w.e.f. 1 January 2024)

Retirement gratuity calculation: periods, ceiling and death gratuity

Step 4: family pension

Rule 50 sets two rates on the same pay. The normal rate is 30% of pay, at least ₹9,000 and at most ₹75,000 a month. If a pensioner dies after retirement, the family first gets the enhanced rate of 50% of pay, but not more than the pension itself, for seven years or until the date the pensioner would have turned 67, whichever is shorter. The enhanced rate has a ceiling of ₹1,25,000. Commuting does not reduce family pension, because the pension authorised on retirement includes the commuted part (Explanation 2).

Enhanced rate: ₹1,01,400 × 50% = ₹50,700, equal to the pension, so it is paid in full. With DR: ₹50,700 + ₹31,941 = ₹82,641
If the officer died at 63, the enhanced rate runs 4 years (to the 67th birthday); at 61 it runs 6 years
Normal rate after that: ₹1,01,400 × 30% = ₹30,420, plus DR ₹30,420 × 63% = ₹19,164.60, rounded up to ₹19,165. Total ₹49,585 at today's DR

Family pension calculation, step by step (Hindi)

The same employee under UPS

Nobody chooses between the two schemes. The old pension covers employees who joined on or before 31 December 2003. UPS is an option inside NPS for central employees appointed from 1 January 2004. But 23 years of service at 31 December 2026 sits right on that line: someone who joined in late December 2003 is on the old pension, and a colleague who joined on 1 January 2004 on the same pay could be on UPS. So the sheet runs the same pay and service through the UPS formulas from the PFRDA regulations. It assumes the individual corpus is at least the benchmark corpus and no final withdrawal.

Formulas from PFRDA (Operationalisation of UPS under NPS) Regulations, 2025, regulations 14, 15, 16 and 17, as amended in December 2025. Rounded up to the next rupee, as in the Gazette's worked example.

B25

UPS output: Months counted (Q, capped at 300)
Formula: =MIN(B5*12+B6,300)
Example result: 276

B26

UPS output: Assured payout
Formula: =IF(B5*12+B6>=120,MAX(ROUNDUP(B4/2*B25/300,0),10000),0)
Example result: ₹46,644

B27

UPS output: DR on payout
Formula: =ROUNDUP(B26*B7,0)
Example result: ₹29,386

B28

UPS output: Monthly payout
Formula: =B26+B27
Example result: ₹76,030

B29

UPS output: Lump sum at retirement
Formula: =ROUNDUP(B2*(1+B7)/10*INT((B5*12+B6)/6),0)
Example result: ₹7,60,298

B30

UPS output: Family payout to spouse
Formula: =ROUNDUP(B26*60%,0)
Example result: ₹27,987

B31

UPS output: DR on family payout
Formula: =ROUNDUP(B30*B7,0)
Example result: ₹17,632

Cell	UPS output	Formula	Example result
B25	Months counted (Q, capped at 300)	=MIN(B5*12+B6,300)	276
B26	Assured payout	=IF(B512+B6>=120,MAX(ROUNDUP(B4/2B25/300,0),10000),0)	₹46,644
B27	DR on payout	=ROUNDUP(B26*B7,0)	₹29,386
B28	Monthly payout	=B26+B27	₹76,030
B29	Lump sum at retirement	=ROUNDUP(B2(1+B7)/10INT((B5*12+B6)/6),0)	₹7,60,298
B30	Family payout to spouse	=ROUNDUP(B26*60%,0)	₹27,987
B31	DR on family payout	=ROUNDUP(B30*B7,0)	₹17,632

Assured payout: ₹1,01,400 ÷ 2 = ₹50,700, × 276 ÷ 300 = ₹50,700 × 0.92 = ₹46,644. Service below 25 years scales it down.
DR at 63%: ₹46,644 × 63% = ₹29,385.72, rounded up to ₹29,386. Monthly ₹76,030
Lump sum: E = ₹1,65,282 (basic + DA), L = 276 ÷ 6 = 46 six-month periods. ₹1,65,282 ÷ 10 × 46 = ₹7,60,297.20, rounded up to ₹7,60,298. It does not reduce the payout.
Family payout: 60% of ₹46,644 = ₹27,986.40, rounded up to ₹27,987, plus DR ₹17,632 = ₹45,619 a month for life
With the full 60% final withdrawal, the payout falls to 40%: ₹18,658 plus DR ₹11,755 = ₹30,413 a month
Gratuity: UPS employees get gratuity under the NPS gratuity rules, which use the same formula and ceiling, so ₹19,00,743

Source: PFRDA (Operationalisation of UPS under NPS) (Amendment) Regulations, 2025 (Gazette, 23 Dec 2025): regulation 15(1), assured payout and ₹10,000 minimum

UPS pension calculation: assured payout, family payout and lump sum, step by step

Old pension vs UPS, side by side

Level 10, last basic ₹1,01,400, 23 years, retiring at 60 on 31 December 2026, DA/DR 63%. UPS column assumes the individual corpus is at least the benchmark corpus and no final withdrawal. Before income tax. Our calculation.

Basic pension / assured payout

Old pension (CCS Pension Rules): ₹50,700 (full 50% after 10 years)
UPS (same pay, same service): ₹46,644 (276 of 300 months)

DR at 63%

Old pension (CCS Pension Rules): ₹31,941
UPS (same pay, same service): ₹29,386

Monthly total

Old pension (CCS Pension Rules): ₹82,641
UPS (same pay, same service): ₹76,030

Lump sum, besides gratuity

Old pension (CCS Pension Rules): Up to about ₹19,94,092 by commuting 40%; monthly falls to ₹62,361 for 15 years
UPS (same pay, same service): ₹7,60,298; monthly payout not reduced

Retirement gratuity

Old pension (CCS Pension Rules): ₹19,00,743
UPS (same pay, same service): ₹19,00,743

Spouse after the pensioner's death

Old pension (CCS Pension Rules): ₹50,700 + DR for up to 7 years (or to age 67), then ₹30,420 + ₹19,165 = ₹49,585
UPS (same pay, same service): ₹27,987 + ₹17,632 = ₹45,619

Minimum

Old pension (CCS Pension Rules): ₹9,000 a month (10+ years)
UPS (same pay, same service): ₹10,000 a month (10+ years)

Item	Old pension (CCS Pension Rules)	UPS (same pay, same service)
Basic pension / assured payout	₹50,700 (full 50% after 10 years)	₹46,644 (276 of 300 months)
DR at 63%	₹31,941	₹29,386
Monthly total	₹82,641	₹76,030
Lump sum, besides gratuity	Up to about ₹19,94,092 by commuting 40%; monthly falls to ₹62,361 for 15 years	₹7,60,298; monthly payout not reduced
Retirement gratuity	₹19,00,743	₹19,00,743
Spouse after the pensioner's death	₹50,700 + DR for up to 7 years (or to age 67), then ₹30,420 + ₹19,165 = ₹49,585	₹27,987 + ₹17,632 = ₹45,619
Minimum	₹9,000 a month (10+ years)	₹10,000 a month (10+ years)

Three things drive the gap. First, service: the old pension pays the full 50% from ten years, while UPS reaches half of the twelve-month average only at 25 years. With 25 years or more, the assured payout here would also be ₹50,700. Second, the lump sum: the old pension's bigger lump sum is bought by giving up ₹20,280 of basic pension a month for 15 years, while the UPS lump sum costs nothing from the payout. Third, UPS depends on the corpus: if it falls short of the benchmark and is not topped up, the payout is cut. A UPS employee also pays 10% of basic plus DA into the NPS account every month during service.

Tax, in one line each

Commutation lump sum: not taxed for central government pensioners (Income-tax Act, 2025, section 19(1), Table, Sl. No. 7).
Retirement gratuity under the CCS (Pension) Rules, 2021: not taxed (section 19(1), Table, Sl. No. 3, read with section 19(2)(g)).
Monthly pension and DR: taxed as salary (section 16).
Tax on the UPS lump sum and payout is out of scope here; check with your PAO.

Source: Income-tax Act, 2025, section 19(1), Table Sl. No. 3 and 7, and section 19(2)(g)

Source: Income-tax Act, 2025, section 16(b): salary includes any annuity or pension

Before you trust the sheet

Use the qualifying service in your service book, not the years since you joined. Some breaks and leave do not count.
If your pay was cut in the last ten months, put the average in B3; the sheet takes the higher figure for pension.
Retiring on or after 1 July 2026 means 63% DA counts for gratuity. For a retirement on 30 June 2026 it was 60%.
Match every figure with your pension papers and PPO. If they differ, ask your pension office before you sign.

Retirement calculator: will your pension and savings last?DA arrear calculator: July 2026 arrears on 63%All central government pension guides and calculators in one place

Frequently asked questions

How is pension calculated for central government employees?: Under Rule 44 of the CCS (Pension) Rules, 2021: 50% of the last basic pay or the average of the last ten months, whichever is higher, after at least ten years of qualifying service. The minimum is ₹9,000 and the maximum ₹1,25,000 a month. DR is added on top: 63% from 1 July 2026.
What is the Excel formula for central government pension?: =IF(years>=10,MIN(MAX(ROUNDUP(MAX(last basic,10-month average)*50%,0),9000),125000),0). Add DR with =ROUNDUP(pension*63%,0).
Do you need 20 years of service for full pension?: Not under the CCS (Pension) Rules, 2021. Rule 44(1) gives 50% of emoluments after ten years of qualifying service, and nine years nine months counts as ten. UPS is different: the full assured payout needs 25 years.
Does commuting pension reduce family pension or DR?: No. DR stays on the full pension (DoPPW OM of 25 October 2022), and family pension is worked out on the pension including the commuted part (Rule 50, Explanation 2).

Educational content, not financial advice. Figures are illustrative and based on the rules current at the time of writing; verify specifics with a qualified advisor.