Create pension tax table in Excel - so it works
With the spreadsheet program Excel you can create your own pension tax table. In this practical tip, we will show you how.
How to create a pension tax table in Excel
- Copy the income share table to the clipboard on the Internet. It is the second table on the website under the item "bb".
- Paste the copied table into your empty Excel sheet. To do this, click on the field [A1] and insert the contents.
- The columns [A] and [B] are now filled. In field [C1] you enter "pension amount", in field [D1] the term "share of earnings".
- Next, click in the [D2] field to insert the formula for calculating the share of earnings. If you have marked the field [D2], enter the formula "(B2 / 100) * C2" and confirm with the Enter key.
- Next, transfer this formula to all fields in column [D]. You can mark the field [D1] and click with the mouse on the lower right corner of the field and drag it down. The formula adapts to the respective line numbers.
- You must fill in the fields [C2] to [C60] by hand with the pension amount. If you have done so, you can see the respective share of earnings in column [D].
So that you do not lose the overview in the pension tax table, you can fix the top row in the Excel table so that it is displayed when you scroll.