Confident Cells

Invoices and VAT: practice exercises

VAT to the penny, gross and net, due dates, days overdue. 5 exercises on real-looking data, each marked the moment you press Check. The first exercise is below to try now; the full lesson is part of Pro.

Your turn

The VAT rate is in H1. In C2, work out the VAT on each invoice, rounded to the nearest penny.

ABCDEFGH
1SupplierNetVATVAT rate20%
2Kestrel Cleaning£611.43
3Willow Training£849.08
4Summit Software£875.21
5Redbrook Couriers£899.69
6Northgate Supplies£334.66
7Oakfield IT£132.71
8Brightway Print£38.49
9Ashby Stationers£277.62

How it works

VAT is the net amount × the VAT rate, rounded to the penny with ROUND(…,2). Gross is net + VAT.

Going back from a gross amount, divide by 1 + the rate: =ROUND(D2/(1+$H$1),2). Taking 20% off the gross gives the wrong answer, because the VAT was 20% of the net.

EOMONTH(date, 1) gives the last day of the following month, a common payment term. Use a date on the sheet rather than TODAY() when you want a fixed "as at" date.

=ROUND(B4*$G$1,2) =B4+C4 =ROUND(D4/(1+$G$1),2) =EOMONTH(A2,0) =MAX(0,G2-F2)

Adding VAT

Keep the VAT rate in one cell, say G1, and lock it with $ so every row uses it: =B2*$G$1. If the rate changes, you change one cell.

Money has whole pennies, so wrap the sum in ROUND(..., 2). Otherwise 20% of £89.99 shows as £18.00 but is stored as 17.998, and totals end up a penny out.

Worked example

ABCDEFG
1InvoiceNetVATGrossNet againVAT rate20%
2INV-01£120.00
3INV-02£45.50
4INV-03£89.99
  1. =ROUND(B2*$G$1,2) in C2. VAT at the rate in G1, rounded to the penny: £24.00, £9.10 and £18.00 (17.998 rounded).
  2. =B2+ROUND(B2*$G$1,2) in D2. Gross is net plus VAT: £144.00 for the first invoice.
  3. =ROUND(144/(1+$G$1),2) in E2. Going back from a gross price: divide by 1.2, don't take 20% off. £144 ÷ 1.2 is £120, but £144 less 20% would be £115.20.

Due dates and balances

  • EOMONTH(date, 1) gives the last day of the next month: a common "pay by" date. With 0 it gives the end of the same month.
  • A balance that can't go below zero: =MAX(0, amount owed - paid). If someone overpays, MAX picks 0 instead of a minus figure.

What you'll practise

  1. The VAT rate is in H1. In C2, work out the VAT on each invoice, rounded to the nearest penny.
  2. In D2, work out the gross amount of each invoice: the net amount plus the VAT in column C.
  3. These are receipts showing the gross amount, VAT included at the rate in H1. In C2, work out the net amount, to the penny.
  4. Invoices are due at the end of the month after the invoice date. In C2, work out each payment due date.
  5. In D2, work out how many days each invoice is overdue on the date in H1. Invoices that aren't due yet should show 0.