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.
The VAT rate is in H1. In C2, work out the VAT on each invoice, rounded to the nearest penny.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Supplier | Net | VAT | VAT rate | 20% | |||
| 2 | Kestrel Cleaning | £611.43 | ||||||
| 3 | Willow Training | £849.08 | ||||||
| 4 | Summit Software | £875.21 | ||||||
| 5 | Redbrook Couriers | £899.69 | ||||||
| 6 | Northgate Supplies | £334.66 | ||||||
| 7 | Oakfield IT | £132.71 | ||||||
| 8 | Brightway Print | £38.49 | ||||||
| 9 | Ashby 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
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Invoice | Net | VAT | Gross | Net again | VAT rate | 20% |
| 2 | INV-01 | £120.00 | |||||
| 3 | INV-02 | £45.50 | |||||
| 4 | INV-03 | £89.99 |
=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).=B2+ROUND(B2*$G$1,2)in D2. Gross is net plus VAT: £144.00 for the first invoice.=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
- The VAT rate is in H1. In C2, work out the VAT on each invoice, rounded to the nearest penny.
- In D2, work out the gross amount of each invoice: the net amount plus the VAT in column C.
- These are receipts showing the gross amount, VAT included at the rate in H1. In C2, work out the net amount, to the penny.
- Invoices are due at the end of the month after the invoice date. In C2, work out each payment due date.
- 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.