NZ Income Tax Formula/Function for Google Sheets

README.md

NZ Income Tax Formula/Function for Google Sheets

Google sheets functions for calculating NZ income tax paid by individuals on salary/wages etc. Years ending 2025 (FY25) and 2026 (FY26) are supported. It has been tested against the IRD calculator for salaries of $50k and $200k.

How to use:

1. From your Google Sheet click Extensions-> Apps Scripts (https://developers.google.com/apps-script/guides/sheets/functions)
2. Paste in the code.
3. Click run.
4. The functions should now work in your sheet. For example =NZ_INCOME_TAX_FY26(100000).
5. This will return a table of cells. Use the index function to select the tax value, e.g. =INDEX(NZ_INCOME_TAX_FY26(100000),2,4).

code.js

function tests() {
  // Expected values taken from IRD calc tool: https://www.ird.govt.nz/income-tax/income-tax-for-individuals/how-income-is-taxed/work-out-tax-on-your-yearly-income
  run_test(200000, 57422.51, NZ_INCOME_TAX_FY25);
  run_test(50000, 7777.92, NZ_INCOME_TAX_FY25);
  run_test(200000, 57077.50, NZ_INCOME_TAX_FY26);
  run_test(50000, 7658.00, NZ_INCOME_TAX_FY26);
  console.log('All Tests PASS')
}

function run_test(income, expected_tax, func) {
  const result = func(income);
  const tax_result = result[1][3];
  if (tax_result != expected_tax) {
    throw Error(`Test for income $${income} failed. Got tax (${tax_result}), expected tax of ${expected_tax}`);
  }
}

// Tax rates from https://www.ird.govt.nz/income-tax/income-tax-for-individuals/tax-codes-and-tax-rates-for-individuals/tax-rates-for-individuals

// 0 - $15,600	10.5%
// $15,601 - $53,500	17.5%
// $53,501 - $78,100	30%
// $78,101 - $180,000	33%
// $180,001 and over	39%
const brackets_fy26 = [
  [0, 15600, 0.105],
  [15600, 53500, .175],
  [53500, 78100, .30],
  [78100, 180000, .33],
  [180000, null, .39]
];

// Tax rates from https://www.ird.govt.nz/income-tax/income-tax-for-individuals/tax-codes-and-tax-rates-for-individuals/tax-rates-for-individuals
// 0 - $14,000	10.5%
// $14,001 - $15,600	12.82%
// $15,601 - $48,000	17.5%
// $48,001 - $53,500	21.64%
// $53,501 - $70,000	30%
// $70,001 - $78,100	30.99%
// $78,101 - $180,000	33%
// $180,001 and over	39%

const brackets_fy25 = [
  [0, 14000, 0.105],
  [14000, 15600, .1282],
  [15600, 48000, .175],
  [48000, 53500, .2164],
  [53500, 70000, .30],
  [70000, 78100, .3099],
  [78100, 180000, .33],
  [180000, null, .39]
];

function get_function_fy25() {
  const res = render_function(brackets_fy25);
  console.log(res);
}

function get_function_fy26() {
  const res = render_function(brackets_fy26);
  console.log(res);
}

function render_function(brackets) {
  // =LAMBDA(x,SUMPRODUCT((x>{1;14000;48000;70000;180000})*(x-{0;14000;48000;70000;180000}),{0.105;0.07;0.125;0.03;0.06}))(A1)
  const boundaries = brackets.map(x => x[0]);
  let rates = [];
  for(let i=0; i< brackets.length;i++){
    // shift decimals to avoid floating point issues
    const rate = brackets[i][2] * 10000;
    const prev = i < 1 ? 0 : brackets[i-1][2] * 10000;
    rates.push(rate - prev)
  }
  rates = rates.map(x => x/10000)
  return `=LAMBDA(x,SUMPRODUCT((x>{${boundaries}})*(x-{${boundaries}}),{${rates}}))(A1)`;
}

function NZ_INCOME_TAX_FY26(income) {
  return calculate_tax(income, brackets_fy26);
}


function NZ_INCOME_TAX_FY25(income) {
  return calculate_tax(income, brackets_fy25);
}

function calculate_tax(income, brackets) {
  if (income === undefined) {
    return 0;
  }
  rows = []
  header = ['Tax Bracket', 'Rate', 'Income', 'Tax', 'Residual']
  let total_tax = 0
  let total_bracket_income = 0
  rows = brackets.map(bracket => {
    [min, max, rate] = bracket;
    const bracket_income = Math.max(0, Math.min(income, max ?? 99999999999) - min);
    total_bracket_income += bracket_income;
    const tax = bracket_income * rate;
    total_tax += tax;
    const residual = bracket_income - tax;
    return [`${min} ${max ? '-' + max : 'and over'}`, rate, bracket_income, tax, residual];
  });
  if (Math.abs(total_bracket_income - income) > .1) {
    throw Error(`Bracket income (${total_bracket_income}) not equal to total income (${income})`);
  }
  const total_rate = total_tax / income;
  const total_residual = income - total_tax;
  totals = ['Total', total_rate, total_bracket_income, total_tax, total_residual];

  return [header, totals, ...rows];
}