How to write a spreadsheet for calculating age

[Richard Purvey]

How to write a spreadsheet for calculating age
Back in the 1980s, in the actuarial departments and claims departments of pensions and life insurance companies, the tabulation of “rates” and “factors” for age in 1/m years at last 1/m birthday, i.e. age in complete 1/m years, or for age in 1/m years at next 1/m birthday or for age in 1/m years at nearer 1/m birthday, was commonplace. For example, m=1 means age in years at last birthday, i.e. age in complete years, or age in years at next birthday or age in years at nearer birthday, while m=12 means age in months at last 1/12 birthday, i.e. age in complete months, or age in months at next 1/12 birthday or age in months at nearer 1/12 birthday.
And writing a spreadsheet which, given a date of birth along with an effective date and a value for m, will calculate age in 1/m years at last 1/m birthday, i.e. age in complete 1/m years, age in 1/m years at next 1/m birthday and age in 1/m years at nearer 1/m birthday, is easy and here is how to write such a spreadsheet;

COLUMN A
Head column A with DATE OF BIRTH and let cell A2 be for the input of the date of birth.
COLUMN B
Head column B with EFFECTIVE DATE and let cell B2 be for the input of the effective date.
COLUMN C
Head column C with m and let cell C2 be for the input of m. The only practical values for m are 1, 2, 3, 4, 6 or 12.
COLUMN D
Head column D with r and put 0 into cell D2. Into cell D3 put the formula =D2+1 and copy this formula down to AND INCLUDING cell D2000 so that cell D4 has the formula =D3+1, cell D5 has the formula =D4+1,… and cell D2000 has the formula =D1999+1
COLUMN E
Head column E with 12*r/m and put the formula =12*D2/$C$2 into cell E2 and then copy this formula down to AND INCLUDING cell E2000 so that cell E3 has the formula =12*D3/$C$2, cell E4 has the formula =12*D4/$C$2,… and cell E2000 has the formula =12*D2000/$C$2
COLUMN F
Head column F with DATE OF BIRTH WITH 12*r/m MONTHS, I.E. r/m YEARS, ADDED and put the formula =DATE(YEAR($A$2),MONTH($A$2)+E2,DAY($A$2)) into cell F2 and then copy this formula down to AND INCLUDING cell F2000 so that cell F3 has the formula =DATE(YEAR($A$2),MONTH($A$2)+E3,DAY($A$2)), cell F4 has the formula =DATE(YEAR($A$2),MONTH($A$2)+E4,DAY($A$2)),… and cell F2000 has the formula =DATE(YEAR($A$2),MONTH($A$2)+E2000,DAY($A$2))
COLUMN G
Head column G with AGE IN 1/m YEARS AT LAST 1/m BIRTHDAY, I.E. AGE IN COMPLETE 1/m YEARS and put the formula =XLOOKUP(B2,F2:F2000,D2:D2000,,-1) into cell G2
COLUMN H
Head column H with DATE OF LAST 1/m BIRTHDAY and put the formula =XLOOKUP(G2,D2:D2000,F2:F2000) into cell H2
COLUMN I
Head column I with NUMBER OF DAYS SINCE and put the formula =B2-H2 into cell I2
COLUMN J
Head column J with AGE IN 1/m YEARS AT NEXT 1/m BIRTHDAY and put the formula =G2+1 into cell J2
COLUMN K
Head column K with DATE OF NEXT 1/m BIRTHDAY and put the formula =XLOOKUP(J2,D2:D2000,F2:F2000) into cell K2
COLUMN L
Head column L with NUMBER OF DAYS UNTIL and put the formula =K2-B2 into cell L2
COLUMN M
Head column M with AGE IN 1/m YEARS AT NEARER 1/m BIRTHDAY and put the formula =IF(I2<L2,G2,J2) into cell M2
COLUMN N
Head column N with DATE OF NEARER 1/m BIRTHDAY and put the formula =IF(I2<L2,H2,K2) into cell N2

As easy as that, folks!

Richard Purvey
December 2024

 

[Author]

Posts