How to Calculate VAT and PPH in Excel: Complete Guide
Calculating Value Added Tax (VAT) and Income Tax (PPh) in Excel are important skills that must be mastered by every accountant and entrepreneur. In this article, we will discuss how to calculate VAT and PPh in Excel in comprehensive detail. Let’s get started!
What is VAT and PPH?
Ppn stands for Value Added Tax, which is a consumption tax imposed at each stage of production of goods and services. In Indonesia, the current VAT rate is 11%.
PPH Income tax, which is imposed on income received by individuals or business entities. The income tax rate varies depending on the type of income and the tax subject.
Why calculate VAT and PPh in Excel?
Calculating VAT and PPH manually can be time consuming and prone to errors. By using Excel, you can make calculations faster, more accurate, and efficient. Excel provides various functions and formulas that can be used to calculate taxes easily.
How to Calculate 11% VAT in Excel
To calculate 11% VAT in Excel, you can follow these steps:
Step 1: Setting up the data
Create a table in Excel with the following columns:
- name of goods/services
- Price
- VAT (11%)
- Total Price
Step 2: Calculating VAT
To calculate 11% VAT, use the following formula in the VAT column:
=Price*0.11
Step 3: Calculating the Total Price
To calculate the total price that includes VAT, use the following formula in the Total Price column:
=price+(price*0.11)
Example:
| name of goods/services | Price | VAT (11%) | Total Price |
|---|---|---|---|
| item a | 100000 | =100000*0.11 | =100000+(100000*0.11) |
| item B | 150000 | =150000*0.11 | =150000+(150000*0.11) |
Thus, for item A at a price of Rp. 100,000, the calculated VAT is Rp. 11,000, so that the total price becomes Rp. 111,000.
how to calculate pph in excel
Calculating PPh is more complex because the rate varies based on the type of income and tax subjects. Here is how to calculate PPh with general rates for individuals in Excel.
Step 1: Setting up the data
Create a table in Excel with the following columns:
- individual name
- Gross income
- Non-Taxable Income (PTKP)
- Taxable income
- PPH
Step 2: Calculating Taxable Income
To calculate taxable income, subtract gross income with PTKP:
= Gross Income-PTKP
Step 3: Calculating PPh
PPh in Indonesia has several layers of tariffs. Here is an example of the applicable rates:
| taxable income layer | PPh rate |
|---|---|
| up to IDR 50,000,000 | 5% |
| IDR 50,000,001 – IDR 250,000,000 | 15% |
| IDR 250,000,001 – IDR 500,000,000 | 25% |
| more than IDR 500,000,000 | 30% |
You can use the following formula to calculate pph based on the tariff layer:
=IF(Taxable Income<=50000000, Taxable Income*0.05, IF(Taxable Income<=250000000, 50000000*0.05+(Taxable Income-50000000)*0.15, IF(Taxable Income<=500000000, 50000000*0.05+200000000*0.15+(Taxable Income-250000000)*0.25, 50000000*0.05+20000000000.15+2500000000.
Example:
| individual name | Gross income | PTKP | Taxable income | PPH |
|---|---|---|---|---|
| Mind | 120000000 | 54000000 | = 120000000-54000000 | =IF(… the above formula…) |
| Ani | 300000000 | 54000000 | = 300000000-54000000 | =IF(… the above formula…) |
Tips for Using Excel for Tax Calculation
- Use cell reference: Use cell references in formulas to facilitate data updating.
- Data Validation: Make sure the data entered is correct to avoid calculation errors.
- number format: Use the appropriate number format for more readable results.
- Formula documentation: Document the formula used to make it easier for others to understand your calculations.
FAQ
1. What is VAT? Value Added Tax (VAT) is a consumption tax imposed at each stage of production of goods and services.
2. What is the VAT rate in Indonesia? The current VAT rate in Indonesia is 11%.
3. What is PPH? Income Tax (PPh) is a tax imposed on income received by individuals or business entities.
4. How to calculate VAT in Excel? To calculate VAT in Excel, multiply the price of goods/services by 11% (0.11).
5. How to calculate PPh in Excel? Calculating PPh in Excel involves several steps, including calculating taxable income and applying appropriate PPh rates.
6. What are the benefits of using Excel for tax calculations? Using Excel for tax calculations makes the process faster, more accurate, and efficient.
Calculating VAT and PPh in Excel is not difficult if you understand the basics and follow the right steps. With this guide, we hope you can do tax calculations more easily and accurately. Always be sure to re-check your calculations and stick to the applicable tax regulations.
