Gross up and gross down: the Excel formulas that should have been built in

Grossing up and grossing down are bread-and-butter accounting calculations. Add tax to a net figure. Remove tax from a gross figure. Every accountant does this constantly.

Excel has no built-in formula for either.

This guide covers why that matters, the manual approaches most people use, and how the TB.GROSSUP and TB.GROSSDOWN custom functions from the tech+bash Add-in simplify things.

The calculation

Gross up (net → gross): multiply the net value by (1 + rate)

Gross = Net × (1 + rate)
e.g. £1,000 net at 20% VAT → £1,000 × 1.20 = £1,200

Gross down (gross → net): divide the gross value by (1 + rate)

Net = Gross ÷ (1 + rate)
e.g. £1,200 gross at 20% VAT → £1,200 ÷ 1.20 = £1,000

The gross-down calculation is where mistakes happen. The instinct is to multiply by (1 - rate) — i.e. £1,200 × 0.80 = £960. That’s wrong. Multiplying by (1 - rate) gives you the tax-exclusive portion of a net figure, not the net component of a gross. The correct operation is always division by (1 + rate).

The problem with ad-hoc formulas

Most accountants build these calculations inline: =A2*1.2 or =A2/1.2. That works, but it creates problems at scale:

• Hard-coded rates scattered across dozens of cells and workbooks
• No audit trail — =A2/1.2 doesn’t tell a reviewer what the 1.2 represents
• Inconsistency — different team members write the formula differently; one of them will eventually write it wrong
• Maintenance — when a rate changes, you need to find and update every instance

A named formula with explicit parameters is a better pattern. It makes the intent clear, centralises the rate, and fails loudly if the inputs are wrong.

TB.GROSSUP and TB.GROSSDOWN

The tech+bash Add-in adds TB.GROSSUP and TB.GROSSDOWN to Excel as custom functions, accessible via the TB. namespace.

Syntax

=TB.GROSSUP(value, rate)
=TB.GROSSDOWN(value, rate)

• value — the cell reference or number you want to convert
• rate — the tax or uplift rate as a decimal (0.2 for 20%, 0.05 for 5%)

Examples

=TB.GROSSUP(A2, 0.2)    → adds 20% to the value in A2
=TB.GROSSDOWN(B2, 0.2)  → removes the 20% component from B2

Both functions accept cell references, so you can store the rate in a named cell and reference it consistently across a workbook:

=TB.GROSSUP(A2, $C$1)   → uses the rate stored in C1

Using the Formulas tab

Custom functions don’t have to be typed. They’re available through the standard Excel Formulas tab:

1. Click Insert Function
2. In the Category dropdown, select tech+bash
3. Choose TB.GROSSUP or TB.GROSSDOWN
4. Fill in the function arguments in the dialog

This is useful if you’re introducing the functions to a colleague who isn’t familiar with the TB. namespace yet.

Also in the TB namespace: TB.STATICRANDBETWEEN

While you’re in the custom functions, it’s worth knowing about TB.STATICRANDBETWEEN. It behaves like Excel’s RANDBETWEEN — returns a random integer between two bounds — except it only calculates once and doesn’t recalculate every time the sheet changes.

=TB.STATICRANDBETWEEN(1, 100)   → a random number between 1 and 100, fixed on first calculation

This matters more than it might seem: RANDBETWEEN recalculates every time the workbook changes, which can silently break an audit trail. See the full guide on TB.STATICRANDBETWEEN for why that’s a problem and when to use the static version instead.

Getting started

TB.GROSSUP, TB.GROSSDOWN, and TB.STATICRANDBETWEEN are all included in the tech+bash Add-in subscription. Once the Add-in is installed, the functions are available in any Excel workbook — Desktop or Online — by typing =TB. and selecting from the autocomplete list.