**Social security** is the benefit given to the citizen by the US government. In this article, we will discuss this social security scheme and the break-even calculator in Excel with proper illustrations.

**Table of Contents**hide

**Download Practice Workbook**

Download this practice workbook to exercise while you are reading this article.

**What Is a Social Security Scheme?**

The **US** govt. has different schemes for social security based on the birth year of the citizens. For the different birth years, **Full Retirement Age** (**FRA**) is different.

The **Full Retirement Age(FRA)** defines the age at which one gets his **100%** social security benefit. In the **USA**, the **FRA** varies concerning birth year. An image is attached in the last section, that explains different **FRA** with birth year. It ranges from age **66** to **67**.

Have a look at the below image for a clear idea.

- One major component of social security is the full retirement age. This ranges from age
**66**to**67**. But people can take early or late retirement. - Usually, it ranges from
**62**to**70**. - If anyone takes retirement before full retirement, he will get a less amount of benefit. On the other hand, if anyone takes retirement after the full retirement age, he will get an incremented benefit.
- The increase or decrease in the benefit will depend on when someone takes retirement. We can say that late retirement gives higher benefits, so everyone must take late retirement. But it is not obvious for all. Sometimes it is seen that due to bad health condition or some other family reason people takes early retirement.
- Another thing that needs to focus that at early retirement, people get benefits for a longer period with a decrement amount. And in late retirement, it is totally the opposite. People get a higher benefit for a shorter period. So, if someone dies at an early age early retirement is much more suitable for him compared to late retirement.
- But there is uncertainty, we don’t know or assume our lifetime. Comparing all the things, we calculate the cumulative benefit with different ages.
- At a certain age, all the cumulative benefits meet all, this is the break-even point. For this social security, we can say this is the social security break-even point.

**Steps to Create a Social Security Break Even Calculator in Excel**

Here, we will make a social security break-even calculator in Excel. We will consider the birth year of an individual as on or after **1960**. The **FRA** for people born on or after **1960** is at **67**. Here, collected the benefit rates of these people with different retirement ages from **ssa.gov**.

After age **70**, the benefit rate is similar. Also need to mention only at age **67**, will get **100%** benefit.

**📌 Step 1: ****Insert Primary Information**

- In this section, we will insert basic information to compare two ages and
**100%**benefits in the dataset.

**📌 Step 2: Calculate the Total Months**

- In this section, we will insert two new rows to calculate the total months in the dataset.

- Go to
**Cell D5**and put in the following formula.

`=$B5*12+$C5`

This will help to find out the benefit percentage based on the age at which retirement has taken. Also, expand this formula to the end.

- Put another formula for
**Cell I6**and**I7**.

`=$G6*12+$H6`

**Read More: How to Calculate Break Even Sales with Formula in Excel**

**📌 Step 3: Find out**** Benefit Percentage**

In this section, we will use the **VLOOKUP function** to calculate the benefit percentage at different retirement ages.

- Put the following formula on
**Cells J6**and**J7**.

**For Cell J6:**

`=VLOOKUP($I6,$D$5:$E$101,2,FALSE)`

**For Cell J7:**

`=VLOOKUP($I7,$D$5:$E$101,2,FALSE)`

We successfully find out the percentage from the list at different retirement ages.

**📌 Step 4: Calculate Cumulative Social Security Benefit**

Here the **1st** retirement age is at **63** years and **2** months. So, the person will get the benefit in the next month which is **63** years and **3** months. After that, this will continue for a lifetime.

- Here, we will apply a formula on
**Cell O6**to get the cumulative benefit for the**1st**retirement age.

`=IF($I$6<=($M5*12+$N5),$K$6*$J$6+$O5,0)`

Expand this formula to the last. We used the **IF function** in this formula.

- Use another similar formula on
**Cell P6**for the cumulative benefit at**2nd**retirement age.

`=IF($I$7<=($M5*12+$N5),$K$6*$J$7+$P5,0)`

Similarly, expand this to the last cell.

**Read More: How to Calculate Break Even Analysis with Formula in Excel**

**📌 Step 5: Insert Data in a Chart to Find the Break-Even Point**

Now, we will insert the following data to create a chart. From this chart, we will find out the break-even point.

- Here, we add a new column named
**Level**for year-defining value.

- Select the whole data from the three columns
**Age 1**,**Age 2,**and**Level**. - Go to the
**Insert**tab. - Choose the
**Line**option from the**Charts**group.

- Look at the following chart. Here, we will change the data of the horizontal axis.
- Choose the horizontal axis and press the right button of the mouse.
- Click on the
**Select Data**option from the menu.

**Select Data Source**window appears.- Choose the
**Level**option from the**Legend Entries**section. - Choose the
**Edit**option from the**Horizontal Axis Labels**section.

- Choose the
**Level**column as the**Axis label range**. - Finally, press the
**OK**button.

- Look at the chart now.

We will have an idea of the break-even point from the chart. But we have to find out the break-even point manually from the chart or the dataset.

**Read More: How to Make a Break-Even Chart in Excel (with Easy Steps)**

**📌 Step 6: Find Out the Break-Even Point**

In this section, we will find out the break-even point in Excel with formulas.

- We add a new column in the dataset. Now, put a formula on
**Cell R5**.

`=IF(IF(AND(O5<>0,P5<>0),O5-P5,"N/A")<>"N/A",ABS(O5-P5),"N/A")`

This formula is based on the **Nested IF function** to find out the absolute values of the difference between the benefit of age **1** and age **2**. We also added a condition that when **Age 1** and **Age 2** both are **0**, then it will not apply the subtraction formula.

**Formula Breakdown:**

*AND(O5<>0,P5<>0)*

It follows two conditions. Checks if **O5** and **P5 **are not equal to **0**.**Result: False**.

*IF(AND(O5<>0,P5<>0),O5-P5,”N/A”)*

**IF **function returns the difference between **O5** and **P5** when fulfilling the condition. Otherwise returns “**N/A**”**Result: N/A**.

*IF(IF(AND(O5<>0,P5<>0),O5-P5,”N/A”)<>”N/A”,ABS(O5-P5),”N/A”)*

**IF **function works based on the value of the previous section. If the value of the previous section is not equal to **N/A**, then give the absolute value of the difference between **Cell O5 **and **P5**, Otherwise, return **N/A**.**Result: N/A**.

- Now, we add a section in the dataset to find out the break-even point.

- We will use the following formula based on the
**MIN function**to get the minimum difference at**Cell T6**.

`=MIN($R$5:$R$352)`

`=INDEX($M$5:$M$352,MATCH($T$6,$R$5:$R$352,0))`

This returns the **Year** value. The **INDEX-MATCH** formula works based on the minimum difference between two ages.

- Similarly, we find out the
**Month**value.

`=INDEX($N$5:$N$352,MATCH($T$6,$R$5:$R$352,0))`

Finally, we get the break-even point at age **80** years and **2** months.

**Read More: How to Calculate Break Even Point in Excel (3 Effective Methods)**

**Conclusion**

In this article, we described the steps in detail to make a social security break-even calculator in Excel. I hope this will satisfy your needs. Please have a look at our website **ExcelDemy** and give your suggestions in the comment box.