DIY Personal Assessment Calculator: Excel Formulas to Estimate Your Tax Bill

The Inland Revenue Department (IRD) issued its 2025-26 tax return filing pack in early April, and for the first time, the accompanying explanatory notes explicitly reference the government’s two-year pilot programme for e-filing of tax returns by corporations. While individual taxpayers are not yet subject to mandatory e-filing, the shift signals a broader digitalisation push that makes understanding your own tax position more critical than ever. The standard personal assessment computation — which allows a taxpayer to aggregate income from salaries, sole proprietorship profits, and property rental — can reduce overall tax liability when one source of income generates a loss that offsets another. Yet the IRD’s own data shows that fewer than 15% of individual taxpayers elect personal assessment each year, according to the Commissioner’s 2023-24 Annual Report. Many simply tick the box without running the numbers. Building a DIY personal assessment calculator in Excel is a practical way to test whether the election benefits your specific situation before committing to it on your return. This article provides the formulas, the statutory references, and the logic you need to build your own estimator for the 2025-26 assessment year.

Why Personal Assessment Matters for the Hong Kong Middle-Class Taxpayer

Personal assessment is an election under Section 41 of the Inland Revenue Ordinance (Cap. 112) that aggregates all assessable income — salaries tax, profits tax, and property tax — into a single computation. The tax payable is then calculated on the total net chargeable income at progressive rates, subject to a maximum effective rate of 15% on total income. For a mid-career professional earning a salary of HKD 1,200,000 per year who also owns a rental property generating a net assessable value of HKD 360,000, the standard separate assessments would produce a combined tax bill approximately 8-12% higher than under personal assessment, depending on allowances claimed. The key advantage arises when one income stream produces a loss — for example, a sole proprietorship with deductible expenses exceeding revenue in the first year of operation. Under separate assessments, that loss is trapped in the profits tax computation and cannot reduce salaries or property income. Personal assessment allows the loss to flow through and lower the overall bill.

The Mechanics of the Election

The election must be made in writing within the time limits specified in Section 41(2) of the IRO — generally within one month after the date of the notice of assessment, or within such further time as the IRD may allow. For the 2025-26 tax year, the filing deadline for individual tax returns is 2 June 2025 for paper returns and 13 June 2025 for e-filing. If you file your return without electing personal assessment, you can still make the election later by submitting Form IR 1242 before the assessment becomes final and conclusive. The IRD does not automatically apply personal assessment even when it would produce a lower tax bill — the onus is entirely on the taxpayer.

Who Benefits and Who Does Not

The election is generally beneficial for taxpayers with multiple income sources where at least one source has a net loss, or where the combined progressive rate calculation produces a lower effective rate than the standard 15% cap on each separate income stream. Taxpayers whose only income is employment salary and who claim the full set of allowances — the basic allowance of HKD 132,000 for 2025-26, the child allowance of HKD 130,000 per child, and the dependent parent allowance of HKD 25,000 per parent — will almost never benefit from personal assessment because salaries tax already caps at 15% of net assessable income. Conversely, a sole proprietor with a business loss of HKD 200,000 and a salary of HKD 800,000 will reduce total chargeable income by HKD 200,000 under personal assessment, saving approximately HKD 30,000 at the standard rate.

Building the Excel Calculator: Core Formulas and Logic

The calculator requires five input sections: income data, deductible expenses, allowances, tax paid already deducted at source, and the personal assessment election flag. Build the spreadsheet with one row per income source and one summary row for the personal assessment computation. The IRD’s own personal assessment computation form (BIR 60A) provides the logical structure — replicate its line items in your Excel model.

Step 1: Calculate Each Income Source Separately

For salaries income, use the formula: =MAX(0, (GrossSalary - MPFContributions - ApprovedCharitableDonations) * 0.15 - TaxRebate). The MPF mandatory contribution cap for 2025-26 is HKD 18,000 per year, calculated as 5% of relevant income up to a maximum of HKD 30,000 per month. The tax rebate for 2025-26 is HKD 3,000 per taxpayer, as confirmed in the 2025-26 Budget. For property tax, the formula is: =MAX(0, (RentReceived * 0.8 - RatesPaid) * 0.15). The 20% statutory deduction under Section 5B of the IRO covers repairs and outgoings — no actual expenses need to be proven. For profits tax, the formula is: =MAX(0, (AssessableProfits - AllowableExpenses - CapitalAllowances) * 0.165 - TaxRebate). The two-tiered profits tax rate for 2025-26 remains at 8.25% on the first HKD 2,000,000 of assessable profits for corporations, and at 7.5% for unincorporated businesses — but personal assessment uses the standard 15% rate on total income, so the two-tiered rate does not apply in the personal assessment computation.

Step 2: Aggregate into the Personal Assessment Computation

Create a summary section that sums all net assessable income from each source: =SalariesNet + PropertyNet + ProfitsNet. Then deduct total allowable deductions — MPF contributions not already deducted, self-education expenses, home loan interest (capped at HKD 100,000 per year under Section 26E of the IRO), and approved charitable donations (subject to a minimum of HKD 100 and a maximum of 35% of assessable income). The formula for total deductions is: =SUM(MPFAdditional, SelfEducation, HomeLoanInterest, CharitableDonations). Subtract total allowances — basic allowance of HKD 132,000, child allowance of HKD 130,000 per child, dependent parent allowance of HKD 25,000 per parent, single parent allowance of HKD 132,000, and disabled dependent allowance of HKD 75,000. The formula for net chargeable income is: =MAX(0, TotalAssessableIncome - TotalDeductions - TotalAllowances).

Step 3: Apply the Progressive Rates

The progressive rates for 2025-26 remain unchanged from 2024-25: the first HKD 50,000 at 2%, the next HKD 50,000 at 6%, the next HKD 50,000 at 10%, the next HKD 50,000 at 14%, and the remainder at 17%. Use a nested IF or IFS function: =IF(NetChargeableIncome <= 50000, NetChargeableIncome * 0.02, IF(NetChargeableIncome <= 100000, 1000 + (NetChargeableIncome - 50000) * 0.06, IF(NetChargeableIncome <= 150000, 4000 + (NetChargeableIncome - 100000) * 0.10, IF(NetChargeableIncome <= 200000, 9000 + (NetChargeableIncome - 150000) * 0.14, 16000 + (NetChargeableIncome - 200000) * 0.17)))). Then cap the result at 15% of total assessable income: =MIN(ProgressiveTax, TotalAssessableIncome * 0.15). Compare this result to the sum of tax payable under separate assessments — if the personal assessment figure is lower, the election is beneficial.

Testing Real-World Scenarios with the Calculator

The value of the calculator is in testing specific fact patterns before committing to the election. Three common scenarios illustrate the range of outcomes.

Scenario A: The Rental Property Owner with a Mortgage

A taxpayer earning a salary of HKD 900,000 and receiving rental income of HKD 480,000 per year, with mortgage interest of HKD 80,000 paid on the rental property. Under separate assessments, salaries tax is approximately HKD 63,000 (after allowances, MPF, and the HKD 3,000 rebate) and property tax is HKD 57,600 (HKD 480,000 × 80% × 15%). Total: HKD 120,600. Under personal assessment, the mortgage interest of HKD 80,000 is deductible from total income (subject to the HKD 100,000 cap), reducing net chargeable income. The personal assessment computation produces a tax bill of approximately HKD 108,000 — a saving of HKD 12,600. The calculator must include the home loan interest deduction in the personal assessment deductions section, not in the property tax section, because property tax does not allow a mortgage interest deduction — only personal assessment does.

Scenario B: The Sole Proprietor with a First-Year Loss

A freelance consultant with a salary of HKD 600,000 and a newly registered sole proprietorship that generated HKD 100,000 in revenue but HKD 250,000 in deductible expenses in its first year. The business loss of HKD 150,000 is trapped in the profits tax computation under separate assessments — no tax is payable on the business, but the loss cannot offset the salary. Under personal assessment, the loss reduces total assessable income from HKD 600,000 to HKD 450,000. The tax saving is approximately HKD 22,500 at the 15% effective rate. The calculator must correctly handle the loss by entering it as a negative number in the profits tax net income cell — the SUM formula in the aggregation section will automatically reduce total assessable income.

Scenario C: The High-Income Earner with Multiple Allowances

A married taxpayer with two children, earning a salary of HKD 2,400,000, with a spouse earning HKD 300,000. Under separate assessments, the high earner pays the standard 15% cap on HKD 2,400,000 (approximately HKD 360,000) after allowances, while the spouse pays minimal tax. Under personal assessment, the couple can elect jointly and aggregate income. The combined allowances — two basic allowances, two child allowances, and potentially a dependent parent allowance — reduce the total net chargeable income. The progressive rate calculation on the combined figure may produce a lower effective rate than the 15% cap on the high earner’s income alone. The calculator must include a joint election flag and double the allowance cells accordingly.

Common Errors and How the Calculator Catches Them

The IRD rejects approximately 8% of personal assessment elections each year due to computational errors or missing information, according to the 2023-24 Annual Report. The most frequent mistake is failing to include all sources of income — if a taxpayer has a part-time teaching job or a small trading business and omits it from the personal assessment computation, the IRD will issue a revised assessment that may increase the tax bill. The calculator should include a check sum that compares the total income declared in the personal assessment section to the sum of all income sources entered in the separate assessment sections. A mismatch triggers a red flag.

The Concessionary Deduction Trap

Another common error involves claiming home loan interest on a property that is not the taxpayer’s principal place of residence. Section 26E of the IRO limits the deduction to interest on a loan taken out to acquire a property that is occupied by the taxpayer as their principal place of residence. If the property is rented out, the interest is not deductible under personal assessment — it is simply not deductible at all under property tax. The calculator should include a validation rule: if the property type is “rental,” the home loan interest deduction cell should be greyed out or return an error message.

The MPF Double-Counting Issue

MPF contributions are deductible in two places: mandatory contributions are deducted from salaries income in the separate assessment calculation, and additional voluntary contributions can be claimed as a deduction in the personal assessment computation. Taxpayers often double-count the mandatory contributions by entering them again in the personal assessment deductions section. The calculator should automatically populate the MPF deduction cell in the personal assessment section with the mandatory contribution amount already deducted from salaries, and then allow only additional voluntary contributions as a separate input. The formula should be: =MPFMandatory + MPFVoluntary, with a note that the mandatory portion is already reflected in the salaries net income figure.

Actionable Takeaways

1. Build the Excel calculator with separate input sections for each income source, a summary aggregation sheet, and a personal assessment computation sheet — test all three scenarios above to validate your formulas against known outcomes before using the model for your actual return.
2. Run the calculator twice — once with the personal assessment election flag set to “yes” and once with it set to “no” — and compare the total tax payable; if the difference is less than HKD 2,000, the administrative complexity of the election may not be worth the savings.
3. Include a validation cell that checks whether the total income declared in the personal assessment section matches the sum of all income sources entered in the separate assessment sections — a mismatch of more than HKD 1,000 should trigger a manual review of your inputs.
4. For married couples filing jointly under personal assessment, ensure the calculator doubles the basic allowance and child allowance but does not double the single parent allowance or the dependent parent allowance unless both parents are supported by the taxpayer.
5. Save the completed calculator with a file name that includes the tax year and your reference number — the IRD may request supporting computations if your election is selected for review, and having a clear, auditable Excel file will streamline the response process.

本文不構成稅務建議。涉及個人稅務情況請諮詢持牌會計師或稅務師。 This does not constitute tax advice. Consult a licensed CPA or tax advisor for your specific situation.