Excel || How to calculate a retirement age (or any other one)

byJan Bolhuis

199 followers

11 years ago

Introduction

A long time ago I saw a discussion how to calculate a retirement age. Because I know a lot of people do apply the same approach as I show you below, I decided to post my comment on that one.

In fact you need a good understanding of how Excel deals with dates (and times) but I don't explain that in this blog.

Let's start with the first comment of the person I will call Mr X

Comment of Mr. X

This really simple formula will give you the retirement date, based on the person's birth date and age at retirement:

=A1+(365.25*60)

A1 represents the cell with the person's birth date in it (so it may not be A1).
60 is the retirement age, so just adjust if the retirement age is different (e.g.: 55, 62, 70, etc.).

Don't forget to have the cells formatted for a date value!

My comment on Mr X

@Mr. X, I've seen a lot of answers like yours. But with all respect I don't like the approach because it's not always accurate.

Why not using the built-in functions of excel.

First I will show you one example in which the formula with the hardcode value of 365.25 won't work.

Cell A1 43 (the age)
Cell A2 = 15-8-1965 (15th of August 1965 = Date of birth)
Cell A3 =A2 (365.25*A1)

Outcome: 14th august 2008

Some formulas which always have a correct outcome:

Cell A1 = date of birth
Cell A2 = retirement age or whatever age you need
Cell A3 the following built-in function DATE

=DATE(YEAR(A1) A2,MONTH(A1),DAY(A1)) or
=EDATE(A1,A2*12)

or if you always need the first day of the month

=DATE(YEAR(A1) A2,MONTH(A1),DAY(1))

or if you need the last day of the month

=DATE(YEAR(A1) A2,MONTH(A1),DAY(EOMONTH(A1;0)))

or if you need the first day of the NEXT month

=DATE(YEAR(A1) A2,MONTH(A1),DAY(EOMONTH(A1;0)) 1) or

=DATE(YEAR(A1) A2,MONTH(A1) 1,DAY(1))

Cheers and have fun