Excel Functionality

How To Calculate CPF Contribution With Excel [2024 Update]

by
How To Calculate CPF Contribution Using Microsoft Excel - Free Template in Excel

UPDATED FOR CPF CONTRIBUTION RATE CHANGES FROM THE YEAR 2024

How To Calculate CPF Contribution Using Microsoft Excel - Free Template in Excel
How To Calculate CPF Contribution Using Microsoft Excel – Free Template in Excel

Learn how to Calculate CPF contribution for Employees and Employers in Singapore by using Microsoft Excel. This covers Singapore Citizens and Singapore Permanent Residents SPRs.

If you want to join our Excel course in Singapore & improve your skills, we have multiple courses – Basic Excel for Analytics, Advanced Excel Courses in Singapore & VBA Macro Programming Courses.

Singapore CPF Rules
In Singapore, all salaried employees need to contribute 5-20% of their salary, depending on age to their Central Provident Fund or CPF. The employer contributes 7.5% to 17% of the salary to the CPF Fund, depending on the age of the worker.

The CPF contribution calculator for employers and employees can be done using Microsoft Excel.

CPF contribution calculator Excel File is available for download below.

Employee’s age
(years)		 CPF Contribution Rates changes from 1 Jan 2022
(monthly wages > $750) Based on CPF Board
Total
(% of wage)		 By employer
(% of wage)		 By employee
(% of wage)
55 & below 37 17 20
Above 55 to 60 28 14 14
Above 60 to 65 18.5 10 8.5
Above 65 to 70 14 8 6
Above 70 12.5 7.5 5
Excel for HR Professionals
Excel for HR Professionals

So payroll executives from Finance or HR professionals need to calculate the correct amount for the contribution to CPF, and provide the net salary to the employees who are Singapore Citizens or Singapore Permanent Residents (SPR). The employer also needs to know their total payroll expenses so as to know their Payroll Liability each month.

Calculating CPF Rates for Employees & Employers in Singapore
I have created a simple Excel spreadsheet to calculate the CPF Rates and Amounts in Singapore in this matter. If you wish to use this you can fill in your own Singapore Citizen & PR employee’s details, their age & basic pay, and the following is automatically calculated:

Learn Microsoft Excel 2019
Learn Advanced Excel 2019 @Intellisoft
  • Employee’s contribution,
  • The employer contribution,
  • Total CPF contributions,
  • Total payable to CPF,
  • Net salary to the employee
  • Gross Salary expense of the employer

The spreadsheet uses simple formulas that are already pre-programmed. So you can just begin to use this spreadsheet for your company.  It has been tested for CPF Contribution Rates in 2022 and beyond, and everything will work smoothly.

Increase in CPF contribution rates from 1 January 2022

  • The CPF contribution rates for employees aged above 55 to 70 have been increased.
  • The increase in the CPF contribution rates will be fully allocated to the employees’ Special Accounts to provide a bigger boost to their retirement income.
  • For those earning monthly wages of more than $500 to $750, the employee contribution rates will continue to be phased in.
  • There are no changes to the graduated contribution rates for first and second year Singapore Permanent Residents (SPRs). 

If you need any enhancements in calculating employee CPF & contributions payable for Payroll expenses for companies in Singapore or have any comments, do let us know. You can leave a comment below this post.

And of course, if you wish to learn how to create such excel spreadsheets, and use simple Microsoft Excel formulas and functions, you can also attend our Microsoft Excel Training classes in Singapore.

Advanced Excel 2019 Course (2 Days) – with SDF Funding

Basic / Intermediate Excel 2019 Training (2 Days) – With SDF Funding

Advanced Excel for HR Professionals (2 Day Classroom Training)

Hope you like this CPF Calculator Singapore Excel Template for calculating the CPF in Singapore. If you like it, I would appreciate it if you Like Us on Facebook, and leave a comment below!

Cheers,
Vinai Prakash, MBA, PMP, ITIL, GAP, Six Sigma
Master Trainer, Intellisoft Systems

Vinai teaches Advanced Excel Techniques, Dashboard Techniques using Excel, Data Interpretation and Analysis Training courses at Intellisoft. He has trained over 25,000 students in over 28 countries and regularly conducts Excel Workshops in Singapore, Malaysia, Indonesia, Australia, India, Dubai, Egypt, Zimbabwe, South Africa etc.

Contact Intellisoft to book a Certified Microsoft Excel Trainer for your Training needs.

DOWNLOAD EXCEL CPF CALCULATOR

What is Your Next Step After Learning Excel?

by
What is Your Next Step after Learning Advanced Excel?

What is Your Next Step After Learning Excel?

Wondering what to pursue after learning Excel? Let us find the answer. Before that, let’s first read a quick short story.

Once upon a time, a very strong woodcutter asked for a job in a timber merchant. The pay was good and so were the working conditions. So, the woodcutter was determined to do his best.

His boss gave him an axe and showed him the area where he would work.

The first day, the woodcutter felled 18 trees.

“Congratulations,” the boss said. “Go on that way!”

Motivated by the boss words, the woodcutter tried harder the next day, but he could only bring down 15 trees. However, the third day he tried even harder, but he could only manage 10 trees. Day after day, he finished with fewer trees.

“I must be losing my strength,” the woodcutter thought. He went to the boss and apologized, saying that he is week and could not understand what was going on too.

“When was the last time you sharpened your axe?” the boss asked.

“Sharpen? But I’ve had no time to sharpen my axe. I have been so busy trying to cut trees….”

This story states that equipping yourself shouldn’t ceases & sharpening the tool set & keep you updated is the only way to a long term success.

Now, why are we reading to this story?

Let me jump to the point directly.

The world is fast paced. So, we need to have the required tools ready & sharp for use. 

It is almost impossible to find work that does not require some amount of Excel. Having the right skills for using and applying Excel for Simple or Advanced tasks is a must.

Microsoft Excel is one of the most valuable skill sets that can take you far!

However, most of you already know & use Microsoft Excel quite well. Really glad to know that. Still, sometimes even experienced Excel experts miss out on the best features of Excel that seem to be hidden.

Is it Sufficient to know Excel well to prove yourself in this competitive world?

Are you ready for the Future?

Learning never ceases and you should keep updating you to keep you in par with the emerging industry and not becoming outdated.

You should grab other Skill set after finishing Microsoft Advanced Excel to keep growing in the Job Industry.

AVAILABLE OPTIONS AFTER MASTERING MICROSOFT EXCEL

Let us discuss the variety of options available in front of you to pursue using the Advanced Microsoft Excel Skills that open you up to various career prospects in addition to career growth.

  1. ATTEND SQL WORKSHOP:

I am an Excel Expert. How do I get into Data Analysis?

I am an Analyst. But I always stand at the mercy of IT peers to query my Data!

Are you relating to these situations?

Let’s talk about the first step you can take to resolve this.

Analysis comes into play only after you have the set of relevant information in your hand that are clean as well.

When you yourself can query data from the relational Database system & reorganize, then certainly you are independent, and you can control your own productivity & efficiency.

Learning the right tool to collect & organize your data can be a savior. If you don’t know what is SQL & Why should you learn it, get yourself acquainted first.

Learn SQL to query any databse at Intellisoft Singapore
Learn SQL to query any Database at Intellisoft Singapore

SQL could be the priority as it is an easy to learn, straight-forward programming language. It is a simple but popular program for Data Management.

Administering, updating, maintaining, and manipulating the database is easy with SQL.

With SQL you can – retrieve, analyze and report information quickly and efficiently.

Wondering How to Learn SQL Fast?

Grab SQL Skills today to be an independent analyst otherwise make a new career as Data Analyst, Database Administrator, Data Scientists or even Data Architect at Intellisoft Singapore.

  1. Join Excel VBA (Macro) Course:

I am an Excel expert. Do I only get to learn the skills that are into Data Analysis?

Well definitely not. As we have already discussed Excel is a program that is used in all the fields in which the Data Analysis is just a new popular.

Excel could be enhanced with further skill sets and utilized in various tasks as well.

One such skill set that is highly useful for people into leadership, or managerial position to review the work or report, or even for someone who is into repeating the functions, formulas, or a set of same steps dozens of times every day.

There is a boon to such people – Macros in Excel.

You can write & record macros for simple to complex steps that you accomplish every day. In addition to that, Macro Code runs much faster and save a huge amount of time, which in turn will increase productivity.

After all, you don’t get bored of what you are doing every day.

Learn to Automate repeated Steps with VBA at Intellisoft Singapore
Learn to Automate repeated Steps with VBA at Intellisoft Singapore

Looking to learn VBA (Macros) faster?

Join an Excel VBA (Macro) training at Intellisoft Singapore today to Learn practical ways to automate tasks & build a better workflow with VBA Macros and automation in your work.

  1. Data Interpretation & Analysis with Excel:

Data Analysis is a cool and exciting career to get into. When you already are aware of the Excel tool kit, now it’s time to learn the Techniques of Analyzing Data with Excel.

Data Analysis is a process of using logic & statistics to answer some of the business questions which consequently leads to decision-making.

You can learn to understand data and forecasting based on different criteria.

Charts & Modelling can be made using the Excel Features.

Correlation & regression techniques can be used to Analyze Data with different variables.

Data Interpretation & Analysis with Excel Training at Intellisoft Singapore
Data Interpretation & Analysis with Excel Training at Intellisoft Singapore

Above all, It is wise to grab the techniques of using the tool effectively, when you are an Excel Expert Already!

Wondering why you should learn Data Analysis? Check out the top 5 Cool Career Options that rely on Data Analysis

Also Join Intellisoft now to Grab Data Analysis Skills fast.

  1. Create Interactive Management Reports & Dashboards with Excel:

A Dashboard is the dynamic reporting technique used in business data reporting.

If you can present a huge amount of critical data in a Visually Appealing, Dynamic & Interactive Chart in Excel, apparently the decisions are made faster & you are the star of the boardroom already!

Visually Appealing, Dynamic & Interactive Report in Excel
Visually Appealing, Dynamic & Interactive Report with Excel Dashboard at Intellisoft Singapore

Why wasting time, learn Excel Dashboard & report metrics that matter!

Well, I hope that you now have a clear idea of  What can you do after learning Advanced Microsoft Excel!

Take a breath! Make time out of your calendar to learn the essential skills that matter!

Best of Luck!

Cheers,
Lavanya
Like us on Facebook, Follow us on Instagram & LinkedIn for more updates on Courses & Grants.

Are You Microsoft Office Proficient?

by
Microsoft Office Training in Singapore

Every job seeker would have this word written in their resume – Microsoft Office Proficiency.  

What Exactly is Microsoft Office Proficiency?

A Microsoft Office Proficient should be able to create an error-free, beautifully formatted document with header, footer, page number & Page breaks.

In Excel, they should be able to handle a large volume of data, format, sort, filter and create Pivot Tables for better visualization of Data.

In PowerPoint, the ability to make aesthetically appealing stellar presentation with graphics, images, GIFs, Animation is considered good proficiency.

Handling Microsoft Outlook like a pro to manage emails, create separate folders, track tasks, manage calendar, set reminder, marking them complete, and most important, getting things done quickly with these productivity tools.

This should not be overwhelming. It should be easy to do, and management expects everyone to be proficient in Microsoft Office.

It has been years since the Microsoft Skills have become mandatory for every person coming into the workforce – whether administration, data handling, HR or even the Managerial Position.

A research by IDC concludes that Microsoft Office is one of the top three skills desired by any employers next to oral and written communication Skills.

Possessing Microsoft Skills not only merely helpful in typing docs and tabulating number, rather they help us to get more productive, efficient and handle and present data professionally effectively than ever. Microsoft Office is the most effective, accessible, and easy to use software’s.

Lear Microsoft Office Skills at Intellisoft

Microsoft Word:

It has been used by many people as just a notepad or a typing software’s. But in real, it can do wonders in creating professional Documents.

If we can spend some time in learning a bit more than what the Home Tab has, it can considerably reduce the time we spend on making documents.

Word is useful to create better Proposals, Reports, Agenda, Meeting Minutes & more.

Microsoft Excel:

Excel is often used in accounting and finance for its ability to calculate and compute complex numbers. Even non-financial businesses use Excel because of its unbeatable ability to organize data in tables & for updating, organizing, and displaying the data nicely.

It is highly used in administration and managerial level to analyze data quickly and present to management.

It even supports Visual Basic Applications to create macros to automate functions that increase efficiency.

In fact, the Pivot Tables and Dashboards are inevitable when it comes to analysis and reporting. You must up your proficiency in Excel to master these functions.

Microsoft PowerPoint:

Everyone would have made a basic presentation in Schools and workplaces to present the work. But PowerPoint comes handy to make it visually appealing and effective.

Its intelligence in generating different design ideas for the slides is becoming so popular nowadays. Other than just suggestion, it allows you to use the plenty of shapes and icons that are in-built and even allows you to import illustrations and graphics from the internet while working.

In short, anyone can make a stellar presentation with a quick guidance and practice.

It is always wise to have a most sough after skill in hand to increase employability & cement a position in job industry.

While Microsoft Office is filled with short cuts and methods to create attractive and easy to manage documents, a comprehensive hands-on training can help you to master the hooks and corners of the Microsoft Office package within just 3 days.

Simply follow the step-by-step instructions and learn the art of creating professional business documents easily every time.

With this training you will be able to Create Impressive Proposals, Charts & Presentations to Woo Clients.

View the detailed Course Outline

Companies can apply for SDF FUNDING & Individuals can apply for SkillsFuture Credits to offset the Training Fees.

If you have questions on Funding or need an official quotation/course brochure, please contact me on 9343-2080 or drop us an Email.

Like us on Facebook, Follow us on Instagram and Do Check out our LinkedIn for more updates on the Courses & Grants!

P.S: Don’t forget to check out our Amazing Article on Best Practices For Managing Emails With Microsoft Outlook to Manage Emails like a Pro!

Exciting Microsoft Excel Shortcuts You Never Knew

by
Top-Exciting-Microsoft-Excel-Shortcuts-You-Never-Knew

Microsoft Excel is one of the most used software at work. People who claim to know Microsoft Excel software mostly are the ones with basic or intermediate knowledge & understanding of Excel. The productivity increases only when we make the most out of the useful software, and gain mastery.

There are many hidden gems and short cuts within Excel, that hardly anyone talks about. I am amazed when people with years of Excel experience tell me they haven’t heard about these. What a waste of time…

‘Let us discuss in this series the ultimate tips in Excel that will make things snappier, and make your co-workers say, “how did you do that??”. They will save a lot of time for you too and improve your productivity.

Tip Number 01 INSERT SERIAL NUMBERS IN A FLASH

What do we when we have to insert Serial Numbers in Excel? We type the number 1 in the cell A1, then 2 in the cell A2, and finally select both the cells & drag the mouse pointer until we get the required serial numbers generated. Right?

But what happens when we need Serial numbers until 100? 200? Or more?

Do we keep dragging? Certainly not!

There is an ultimately easy way in which you can just flash fill the serial numbers to whatever value needed.

Insert Serial Number in a Flash

Here are the steps:

Input Number 1 in the first cell

Step 1 Input number in Cell A1

Select Fill > Series from Editing Ribbon of Home Tab

  1. Home> Fill > Series

Step 02 Select Series from Fill option

  • Select Columns from the Dialog box, give Step & Stop values and Hit OK.

Step 03 Select Column and give stop value

NOTE: If you are looking to Fill Rows instead of columns, just select Rows in the dialog box and follow the same steps.
Bet you didn’t know this trick… Well, now you do.

Tip Number 2 FORGET COPY+PASTE – JUST DRAG & DROP

When we have a set of data in one column & we want it to move to another column. What do we do? Select them all, copy or Hit CTRL C and select the destination column, and paste or hit CTRL V?

Forget the hassle, now select the required data, just Drag & drop to the destination.

Drag and Drop to Copy

Tip Number 3: COPY MULTIPLE VALUES USING THE CLIPBOARD

Working with multiple values? Forget the hassle of copying and pasting one by one.
Use the built in Clipboard feature of Excel.

You can expand the clipboard from the icon lists or use the Win (Windows Key) + V to click on the required values

Copy Multiple values using Clipboard

Tip Number 4: DUPLICATE A SHEET IN JUST ONE-CLICK

Want to copy the whole sheet? Are you selecting and copy pasting?
Or right click, copy, select the sheet, move to bottom?? So many steps for duplicating the sheet?

Now Duplicate a Sheet like a Pro!

Just hold down Ctrl + Drag & Drop the sheet to desired position.

Duplicate sheet by drag and drop

Tip Number 5: UNDO & REDO IN ONE HIT

Undo & Redo would be the most used operations in Excel.
Now do undo and redo with just one click.
Use the buttons on top.

Undo and redo in one click

Tip Number 6 Use the QUICK ACCESS TOOLBAR

Tedious to find the operations that you frequently use?

CHANGE ALL CAPS TO Mixed Case for Next 3 Rows.

CLICK THE DROP DOWN ARROW TO SEE THE OPTIONS
YOU CAN ADD THE FREQUENTLY USED OPTION TO QUICK ACCESS TOOLBAR

EXTRA TIP: YOU CAN ADD ANY OTHER COMMANDS THAT YOU USE FREQUENTLY

Quick Access Toolbar

Hope that helps! Want to learn more exciting tips, tricks & shortcuts and improve your productivity in Microsoft Excel?

If you wish to build your knowledge to have a better Excel Operations knowledge, you must start working on it. You will do well in your career, and get ahead faster, even land that dream job with better skills & competence in Excel.

We at Intellisoft are here to guide you.

Intellisoft Systems offers 2 day extensive workshops on Microsoft Excel.

Simply pick a program – Basic/Intermediate Excel OR Advanced Microsoft Excel

Join us to get more out of Microsoft Excel to make the best use of the software in your day-to-day work!

Discover Lesser Known Features of Excel

by
Hidden features of Excel

Want to go beyond the common use of Excel and Explore its Extremely Useful but Lesser-Known Features of Microsoft Excel?

Microsoft Excel’s extensive features can help you to increases productivity and also saves time. Become proficient in Data Management & Business Analytics – Turn Data into Insights, Develop better tables & charts.

Lesser Known Features of Microsoft Excel

Conditional Formatting:

Apply formatting to cells automatically depending on the value of the cell. It is also a powerful feature, given that conditions can be based on any Excel formula. Conditional Formatting works by letting you set rules for how cells can be customized.

Conditional formatting makes it easy to highlight required cells or ranges of cells, emphasizing unusual values. In addition to that, it is used to visualize & analyze data in Excel by using data bars, color scales, and icon sets. Overall, it helps you to identify important data faster.

Learn Lesser Known Shortcuts of Excel at Intellisoft Singapore
Learn Lesser Known Shortcuts of Excel at Intellisoft Singapore

Advanced Filters:

This Advanced command works differently from the Filter command in several ways. It displays the Advanced Filter dialogue box instead of the Auto Filter menu. You can filter the data in place, or to a different location. You can also specify that you want to see unique items only.

All the duplicates are removed from the filtered list, with Advanced filter. The Data tab contains all the features of Advanced Filters. The advantage of using advanced filter is that you can see the filtered criteria in Excel cells. You can also easily add new filters by typing the values directly in the cells. Thus, it is absolute must learn feature.

Learn Advanced Filter for more Productivity at Intellisoft Singapore
Learn Advanced Filter for more Productivity at Intellisoft Singapore

Database Functions:

Database functions is a Seldom used Gem in Microsoft Excel. With this, you can not only perform basic calculations but also use complex criteria, that allow you to perform the calculation for a specified subset of the records in your database.

These functions treat the entire set as a Database, just like a SQL Database.

Learn the Database Function at Intellisoft Singapore
Learn the Database Function at Intellisoft Singapore

Consolidating Multiple Worksheet:

If you receive information in multiple sheets or workbooks that you want to summarize, the Consolidate command can help you pull data together onto one sheet. It allows you to combine information from multiple workbooks when you need them one place.

The Excel consolidate function lets you select data from its various locations and creates a table to summarize the information for you, automatically, painlessly.

Learn Consolidate Function at Intellisoft Singapore
Learn Consolidate Function at Intellisoft Singapore

Preventing others from Changing Master Data:

This feature allows you to prevent other users from accidentally or deliberately changing, moving, or deleting data in a worksheet. You can lock the cells on your Excel worksheet & then protect the sheet with a password.

With worksheet protection, you can make only certain parts of the sheet editable and users will not be able to modify data in any other region in the sheet.

You can lock the structure of your workbook by specifying a password.
Locking the workbook structure prevents other users from adding, moving, deleting, hiding, and renaming worksheets.

While Microsoft Excel is packed with gems, these are just a few advantages of learning Advanced Excel with us at Intellisoft. We’ll make you an expert in these lesser-known, but advanced features in Excel.

Avoid Crowded Worksheets with this button

by
Learn the Hot Keys to Avoid Crowded Worksheet at Intellisoft Singapore

One simple way to reduce worksheet crowding is to rotate your column labels so that they read up, down, or vertically.

What are Advanced Excel Skills?
What are Advanced Excel Skills?

Add them to the Format toolbar, as follows:

  1. Choose Tools + Customize.
  2. Then Click the Commands tab.
  3. Under Categories, choose Format.
  4. Under Commands, find the Vertical Text button and drag it into place on your Formatting toolbar.
  5. Repeat Step 4 to drag the Rotate Text Up, Rotate Text Down, and, if needed Angle Text Upward and Angle Text Downward buttons to the Formatting toolbar

Now, whenever you want to angle or rotate text, just select the cell(s) and click the appropriate button. It will save you space, and make it easier to read and avoid eye strain.

Check this out if you are still Wondering Why learn advanced Excel?

Free Tips, Tutorials & Training Grants Info

Learn from expert tips, tricks and resources for Excel, PowerPoint, Photoshop, Python, Power BI, Project Management, IT, Soft Skills & more with our Email Newsletter.
Plus get the latest news on Grants. Join Today!

Yes! Send Me Exclusive Tips
Key Links
Facebook-f Youtube Twitter Linkedin-in Wordpress

Found What You Were
Looking For?

Just Tell us...

We're Here To Help You!