How to Calculate GST/QST in Excel: Simple Formulas for Businesses — Montreal CPA Firm Near You

Introduction
Calculating GST (Goods and Services Tax) and QST (Quebec Sales Tax) accurately is essential for every Quebec business. Whether you run a retail store, consultancy, or e-commerce business, applying the correct sales tax ensures compliance with Revenu Québec (ARQ) and the Canada Revenue Agency (CRA) while avoiding costly errors in billing or filing.

While accounting software automates these calculations, many small businesses still use Microsoft Excel or Google Sheets for quotes, invoices, and tracking. Excel formulas make it easy to calculate GST/QST precisely, even across hundreds of transactions.

At Mackisen CPA Montreal, we help entrepreneurs and finance teams build smart, compliant Excel templates for tax tracking and remittances. This guide shows you the simplest formulas for calculating GST/QST in Excel and how to apply them to your sales, purchases, and returns.

Legal and Regulatory Framework
The applicable tax rates and calculation requirements are set under:

• Excise Tax Act (Canada) s.165(1) — establishes the 5% GST on taxable supplies across Canada.

• Taxation Act (Quebec) s.255(1) — imposes the 9.975% QST on taxable supplies in Quebec.

• Tax Administration Act (Quebec) s.59(3) — mandates interest and penalties for under-collected or miscalculated tax.

• Revenu Québec Bulletin TVQ.204.1 — clarifies how to calculate QST on the GST-inclusive amount.

These rules mean that QST (9.975%) is always calculated on top of the price including GST, resulting in a combined effective rate of 14.975% in Quebec.

Step-by-Step: How to Calculate GST/QST in Excel

Let’s assume:

• Base Price (before tax): $100.00

• GST Rate: 5%

• QST Rate: 9.975%

1. Calculate GST Amount

In Excel, if your price is in cell A2, enter this formula:

= A2 * 0.05

This gives you the GST amount (5% of $100 = $5).

You can label this cell “GST Amount.”

2. Calculate QST Amount

Under Quebec’s rule, QST is charged on the price including GST.
So the formula becomes:

= (A2 + (A2 * 0.05)) * 0.09975

or simplified:

= A2 * 1.05 * 0.09975

This gives you QST = $10.47 (rounded).

Label this “QST Amount.”

3. Calculate Total Sales Price (Including GST & QST)

Add both taxes to the base price:

= A2 + (A2 * 0.05) + ((A2 + (A2 * 0.05)) * 0.09975)

Simplified version:

= A2 * (1 + 0.05) * (1 + 0.09975)

Result:
Total price = $115.47

That’s the correct final selling price for $100 in Quebec with both taxes applied.

4. Combine Formulas in an Invoice Template

You can build a simple table in Excel:

5. Calculate Total GST/QST for Multiple Sales

If your sales prices are listed in column B, you can calculate total GST and QST with SUM formulas:

=SUM(B2:B20)*0.05       → Total GST
=SUM(B2:B20)*1.05*0.09975   → Total QST

Or, if you already calculated GST and QST per line in columns C and D, simply use:

=SUM(C2:C20)   (Total GST)
=SUM(D2:D20)   (Total QST)

6. Reverse Calculation (If You Have the Total With Taxes)

If you know the total price including both taxes (e.g., $115.47) and want to find the pre-tax amount, use this formula:

= A2 / (1.05 * 1.09975)

Example:
$115.47 / 1.1547375 = $100 (base price).

You can then find GST and QST separately:

GST = Base Price * 0.05
QST = (Base Price * 1.05) * 0.09975

Pro Tip: Simplify With Named Cells
Instead of hardcoding rates, assign names to your rate cells (e.g., “GST_Rate” = 0.05, “QST_Rate” = 0.09975).
Then your formulas become:

= Base_Price * GST_Rate
= (Base_Price + (Base_Price * GST_Rate)) * QST_Rate

If rates ever change, you only need to update them once.

Jurisprudence and Legal Insight
Accurate tax calculation isn’t just a matter of arithmetic — it’s a legal obligation:

• Canderel Ltd. v. Canada (SCC 1998) — filings must reflect actual commercial reality.

• Lac d’Amiante du Québec Ltée (SCC 2001) — Revenu Québec can audit calculations and reassess undercollected QST.

• Hickman Motors Ltd. v. Canada (SCC 1997) — taxpayers must prove that taxes were correctly calculated and remitted.

Keeping precise records (like your Excel formulas and invoice backups) is key to defending your filings.

Documentation: Excel Recordkeeping Best Practices
For CRA and Revenu Québec compliance, retain:

• Your Excel tax calculation template.

• Copies of invoices showing GST and QST charged separately.

• Monthly or quarterly Excel reports reconciling sales and taxes.

• Proof of payments or refunds.

At Mackisen CPA Montreal, we help businesses convert Excel-based records into compliant, audit-ready formats that integrate with CRA and ARQ filing systems.

How CRA and Revenu Québec Review Tax Calculations
Auditors verify that:

• Correct tax rates (5% GST, 9.975% QST) are applied.

• QST is calculated on GST-inclusive prices.

• Taxes collected match reported amounts in FPZ-500-V and GST34 filings.

• Roundings are consistent and traceable.

Excel reports can serve as your first line of defense if verified properly.

Winning With CRA and Revenu Québec
At Mackisen CPA Montreal, we turn Excel into a reliable compliance tool:

1. Custom Excel Templates – We design bilingual templates with automated tax formulas.

2. Reconciliation Reports – We match Excel summaries with your CRA/ARQ returns.

3. Audit Proofing – We build documentation logs linking invoices to calculations.

4. Training – We teach your staff how to calculate, reconcile, and file accurately.

5. Penalty Relief – If errors occur, we file corrections and relief applications under Tax Administration Act s.94.1.

Mackisen Service Hub: Tax Accuracy Through Technology
Our Mackisen Service Hub helps businesses automate their GST/QST management with:

• Excel and cloud-based invoice systems.

• Monthly reconciliation and filing reviews.

• CRA/ARQ audit readiness reports.

• Integration with accounting and tax filing portals.

Our bilingual CPA auditors, tax lawyers, and compliance professionals trained at McGill, Université de Montréal, and Concordia University ensure precision in every calculation.

Real Client Example
A Quebec construction supplier used manual invoices with inconsistent QST rounding, triggering a Revenu Québec audit. Mackisen CPA rebuilt their Excel system using standardized formulas, corrected two years of filings, and recovered $9,800 in denied Input Tax Refunds.

Why Mackisen
With over 35 years of combined CPA and legal experience, Mackisen CPA Montreal ensures every dollar of GST and QST you calculate is accurate, compliant, and defensible.

When you use Excel with Mackisen guidance, your tax math isn’t just correct it’s court-proof, audit-ready, and professionally aligned with CRA and Revenu Québec standards.