A spreadsheet can test the main Business Asset Disposal Relief conditions for a share sale. This post builds one workbook, step by step, with a formula for each cell.
The workbook is for one case: a person selling shares they hold in a UK company. The rules come from GOV.UK and the HMRC Capital Gains Manual, read in November 2026. The formulas are practical reasoning built on those rules. A result prompts a question and is not tax advice.
Step 1: Lay out the workbook
Use six tabs: Inputs, Holding, Role, Trading, Limit and Dates. Add a seventh, Log, for sources and assumptions. Keep every fact in one labelled cell, with its source beside it. Format date cells as dates and cells H2, H3 and H8 as percentages, so 5% is stored as 0.05.
The formulas below type rates and limits in for clarity. In your own sheet, give each rate and limit its own cell, so a change in the rules needs one edit.
Step 2: Set the dates
On the Inputs tab, B1 is the sale date. The legislation treats the sale date as the last day of the two-year period. HMRC's example gives a last day of 17 April 2019 and a first day of 18 April 2017. This formula gives the same pair of dates:
B2: =EDATE(B1,-24)+1
B2 is the start of the window. Each condition below must hold on every day from B2 to B1.
The tax year of the sale ends on the next 5 April:
B3: =DATE(YEAR(B1)+(B1>DATE(YEAR(B1),4,5)),4,5)
GOV.UK gives three rates by date. The rate formula is:
B4: =IF(B1>=DATE(2026,4,6),0.18,IF(B1>=DATE(2025,4,6),0.14,0.1))
A sale on or after 6 April 2026 returns 0.18. A sale from 6 April 2025 to 5 April 2026 returns 0.14. An earlier sale returns 0.1. HMRC's manual shows that disposals before 23 June 2010 used a 4/9 reduction instead, so do not use B4 for those.
Step 3: Test the shareholding
On the Holding tab, list the start of the window and every date the holding changed. Put your ordinary shares at nominal value in column B and all ordinary share capital in column C. Put your votes in D and all votes in E.
Three points from HMRC decide what goes in those cells:
- Ordinary share capital leaves out any share whose holder gets a fixed-rate dividend and nothing more from the profits.
- Only votes you can use count. HMRC's example is a preference share that votes only when its dividend is six months late.
- Shares held as a trustee never count towards your own 5 per cent. For jointly held shares, each holder is treated as holding a part that matches the value of their interest.
The test for each row is:
F2: =IF(C2="","",IF(AND(B2/C2>=0.05,D2/E2>=0.05),"Met","Not met"))
Fill it down, then add a summary cell:
G1: =IF(COUNT(B2:B10)=0,"No data",IF(COUNTIF(F2:F10,"Not met")=0,"Met on every row","Check the rows"))
Make A2 equal to Inputs!B2, so the first row always sits on the first day of the window.
Step 4: Test the economic interest
GOV.UK adds a second test for shares that are not from an Enterprise Management Incentive. Two routes exist, and either one needs a 5 per cent entitlement. Put the entitlements on the Holding tab:
H2: share of distributable profits
H3: share of assets on a winding up
The first way needs both H2 and H3 to reach 5 per cent:
H5: =IF(AND(H2>=0.05,H3>=0.05),"Met","Not met")
HMRC tests what you would be entitled to, not what was paid. It says the test may fail where the entitlement depends on directors' discretion or a financial hurdle. Four further points matter:
- Winding-up assets exclude goodwill that the balance sheet does not recognise.
- Growth shares that carry no entitlement until an event occurs are treated as having none before it.
- The entitlement must come from the shareholding, not from a side agreement.
- The results of each accounting period are used, so the answer may not be clear until the period ends.
HMRC adds that a company with one class of shares and no other arrangements is likely to meet the first way without checking the accounts. With more than one class, the second way is often simpler to model. It works from the company's market value at your sale date, or at the date it stopped trading in the Step 7 case:
H6: entitlement in pounds on a sale of the whole company
H7: market value of the company at your sale date
H8: =H6/H7
H9: =IF(H8>=0.05,"Met","Not met")
The two HMRC examples show why that matters. In the first example a seller holds 6 per cent, sells for £170,000, and the whole company is valued at £5 million. Preferential rights held by other shareholders mean the seller would take only £200,000 of a £5 million sale. H8 is 4 per cent and H9 shows "Not met". In the second, a holder with 6 per cent receives £600,000 when the company is bought for £10 million, so H8 is 6 per cent. HMRC applies the market value at the date of the sale to the whole earlier period.
HMRC also notes that a small holding can be worth proportionally more here, because the valuation takes no minority discount. The proceeds are not what you actually received. They are what you would have been entitled to if all the ordinary share capital had been sold. This route cannot be used if arrangements aim to secure the relief. A last cell gives the overall answer:
H10: =IF(OR(H5="Met",H9="Met"),"Met","Not met")
Step 5: Log the role
On the Role tab, list each office or employment, sorted by start date. Put the start in column A and the end in column B. For a role that is still running, enter the sale date as the end. A director appointment will normally be an office, but check the Employment Status Manual. HMRC sets no minimum for working hours or pay.
Flag any gap before a row. The MAX allows for roles that overlap:
C3: =IF(A3="","",IF(A3>MAX(B$2:B2)+1,"Gap","OK"))
Then test whether the roles cover the whole window:
E1: =IF(AND(MIN(A2:A6)<=Inputs!B2,MAX(B2:B6)>=Inputs!B1,COUNTIF(C3:C6,"Gap")=0),"Covered","Check")
Suppose a seller left the board on 30 June 2025, with no other role, and rejoined on 1 October 2025. C3 shows "Gap", so a sale within two years of that gap needs a closer look at the role condition. This is an illustration, not a real case.
Step 6: Log the trading status
The company must be a trading company or head a trading group. GOV.UK says trading must be its main activity, as against non-trading activity such as investment. HMRC accepts that most companies carry on some activity that is not trading. A company still counts as trading unless non-trading activity is substantial.
HMRC lists four indicators and says they are not percentage tests:
- non-trading income
- the company's asset base
- the cost and staff time spent on each activity
- the company's history
Some indicators may point one way and some the other. On the Trading tab, record non-trading income in B1 and total income in B2. Record non-trading assets in B3 and total assets in B4. Then:
B5: =B1/B2
B6: =B3/B4
B7: =IF(MAX(B5,B6)<=0.2,"At or below 20%: screening note only, not a pass","Look closer")
Treat B7 with care. One line in the manual tells HMRC staff that a case probably needs no detailed review. The note covers cases where income and assets both stay under 20 per cent non-trading. The manual is silent on cases above 20 per cent, and it never calls 20 per cent a safe level. The sheet cannot settle the question.
Add rows for expenses, staff time and history, with a written note for each. HMRC's examples of trading activity are useful prompts for those notes:
- Land bought as stock by a developer, or as a factory site by a manufacturer, is probably trading. Land bought for rent or growth normally is not.
- A fixed cash deposit that a travel agent must keep for bonding is part of the trade.
- Surplus funds earmarked for a trade purpose, or held briefly before a dividend, may count as trading. Long-term retention of significant earnings may be an investment activity.
- Shares taken in place of cash fees, or held as a condition of trading, depend on why they are held.
- A company between trades, actively seeking a new one, may still be trading. It must acquire or start one as quickly as the circumstances allow.
HMRC's view is that the company usually knows its own status best, and that the status is finally a question of fact. HMRC can disagree, and the First-tier Tribunal can then decide. If the doubt is about how HMRC reads the legislation, not the facts, the company can ask HMRC for an opinion. The route is the Non-Statutory Clearance service. The manual also cites a 2020 First-tier Tribunal decision, confirmed on appeal in 2021, where a company held a large investment portfolio.
Step 7: Handle a company that stopped trading
GOV.UK still allows a claim when the sale comes within 3 years after the company stopped being a trading company. HMRC's manual then tests the conditions over the two years that end on the day it stopped. That day need not be the day the trade stops. On the Dates tab, put the cessation date in B1.
B2: =IF(B1="","",IF(B1>=EDATE(Inputs!B1,-36)+1,"Within 3 years","Outside 3 years"))
B3: =IF(B1="","",EDATE(B1,-24)+1)
B3 is the start of the two-year window that ends on the cessation date. To test that window, point Holding!A2 and Role!E1 at Dates!B3 for the start and Dates!B1 for the end. The 3-year formula counts like the two-year one, so check the boundary day with your adviser.
Step 8: Handle shares from an option scheme
Shares that came from an Enterprise Management Incentive face two tests from GOV.UK instead of the shareholding tests. They must have been bought after 5 April 2013. The option must have been granted at least 2 years before the sale. HMRC adds that the employment requirement must be met during that two-year period.
B5: date the shares were bought
B6: date the option was granted
B7: =IF(AND(B5>DATE(2013,4,5),B6<=EDATE(Inputs!B1,-24)),"Met","Not met")
Run the Role tab for the two years that end on the sale date, the same window as Inputs!B2. HMRC's manual also allows EMI shares acquired from 6 April 2012, with an election for 2012 to 2013. Check that year with HMRC.
Step 9: Check the lifetime limit
The limit caps net gains: relevant gains minus relevant losses on qualifying sales. HMRC says there are no transitional rules, so the sale date alone picks the limit. On the Limit tab, build a table in A10:B14:
A10: 6 April 2008 B10: 1000000
A11: 6 April 2010 B11: 2000000
A12: 23 June 2010 B12: 5000000
A13: 6 April 2011 B13: 10000000
A14: 11 March 2020 B14: 1000000
Enter the dates as real dates in ascending order. Then:
B1: earlier qualifying gains, as HMRC counts them
B2: this sale's net qualifying gain
B3: =LOOKUP(Inputs!B1,A10:A14,B10:B14)
B4: =MAX(0,B3-B1)
B5: =MAX(0,MIN(B2,B4))
B6: =B2-B5
B4 is the room left, B5 the gain that can qualify and B6 the part taxed at the usual rates. A net loss is not eligible, which is why B5 never goes below nil. For an earlier sale made from 23 June 2010, count the amount charged at the relief rate, after other losses and the annual exempt amount. For an earlier sale before that date, count the gain the 4/9ths reduction applied to. HMRC also has anti-forestalling rules for some sales before 11 March 2020. The lookup does not model them.
HMRC's own examples check the logic:
- A confectioner made a £38,000 gain on goodwill and an £8,000 loss on premises. The relief applies to the net £30,000, which is
=38000-8000. - A shopkeeper sold two shops. The first sale in February 2020 gave £600,000, all of which qualified under the old £10 million limit. The second sale in April 2020 gave £500,000. B3 is £1 million and B1 is £600,000, so B4 and B5 are £400,000, and B6 is £100,000.
- If earlier claims already total £9,489,400, then
=MAX(0,1000000-9489400)gives nil for a sale after 11 March 2020. No relief is available.
Repeat claims are allowed, and GOV.UK sets no limit on how many. One lifetime amount serves all of them. A sale before 11 March 2020 may leave you with a higher limit, and GOV.UK says to contact HMRC to find out.
Step 10: Estimate the tax
GOV.UK gives four steps for someone whose gains all qualify. Start with the gain on each qualifying asset. Then total the gains and subtract qualifying losses. Subtract your tax-free allowance. Then charge the rate on what is left, up to the lifetime limit. For 2026 to 2027 the allowance is £3,000. This estimate assumes nothing else has used it:
B7: =MIN(MAX(0,B2-3000),B4)
B8: =B7*Inputs!B4
B8 is only the part taxed at the relief rate. For higher and additional rate taxpayers, gains outside the relief bear 24 per cent, and the allowance can go against those gains first. For a basic rate taxpayer, the basic rate band is applied to relief gains ahead of other gains. This sheet does not model other gains. The £3,000 and 24 per cent figures apply to 2026 to 2027 only. For an earlier sale, read the allowance and rates for that year.
The relief is not an exemption. HMRC's manual says so directly, and qualifying gains still bear tax at the lower rate.
Step 11: Check the dilution dates
A holding that falls under 5 per cent because of a new share issue may still support a claim, according to GOV.UK. The manual's rules apply to share issues from 6 April 2019. The issue must be wholly for cash, made for commercial reasons, and not aimed at a tax advantage. A sale of your whole holding just before the issue would also need to satisfy the conditions, with the two-year officer or employee test included.
The first election treats you as selling the shares and buying them back immediately before the issue. The deemed sale produces a gain that the relief can cover. A second election can postpone the tax until you really sell. Both elections are irrevocable.
Check the dilution itself first. Suppose you hold 6 shares out of 100 and the company issues 100 new shares for cash. This is an illustration, not a real case:
=6/(100+100)
That gives 3 per cent. Now put the 5 April that ends the tax year of the deemed sale in B10, not the share issue date:
B11: =DATE(YEAR(B10)+2,1,31)
B12: =EDATE(B10,48)
B11 is the latest date for the first election: the first anniversary of the 31 January after that tax year. For a deemed sale in 2026 to 2027, it is 31 January 2029. B12 is the last day for the second election. HMRC allows it up to 4 years after that tax year ends.
HMRC's example shows the arithmetic. In HMRC's example, a holder with 6 per cent fell to 3 per cent after a cash issue that paid for expansion into bigger premises. The deferred gain was =800000+10000, which is £810,000: £800,000 on the shares plus £10,000 on a loan note. After a deferral election, a claim for the relief is still needed. The deadline is the second 31 January after the end of the first tax year when the gain counts as arising.
Step 12: Set the claim deadline
GOV.UK offers two routes: put the claim on a Self Assessment return, or complete section A of the HMRC helpsheet. Its deadline table lists 31 January 2027 for 2024 to 2025 and 31 January 2028 for 2025 to 2026. Both fit one pattern, which gives the deadline from the tax year end in Inputs!B3:
=DATE(YEAR(Inputs!B3)+2,1,31)
Check the GOV.UK table for the year of your sale before you rely on that pattern.
Step 13: Keep a log
On the Log tab, add one row for each input and each assumption. Use these columns: item, source page, date read, assumption, reviewed by and date reviewed. This list is practical reasoning, not an official requirement. A second reviewer can then repeat the work and reach the same results.
Record the date you read each rate, limit and allowance. Rules move, and the sale date decides which figures apply.
What this sheet does not do
The sheet covers one case: an individual selling shares. GOV.UK lists other routes with their own tests. A sole trader or business partner needs 2 years of ownership. Closing a business adds a requirement to dispose of the assets within 3 years. Assets lent to a business, and trustees, have their own conditions. None of these is modelled here.
The sheet also cannot decide whether a company is trading, whether an entitlement is a real one or whether an election is valid. Key Ledgers Global's advice is neither legal advice nor regulated investment advice. A solicitor or tax adviser answers those questions on your facts.
Key Ledgers Global is based in Harrow, London, and its clients are owner-managed businesses. Its exit and succession page suggests starting 12 to 24 months ahead of a planned exit. More at https://www.keyledgersglobal.com/exit-succession/?utm_source=DEV
Top comments (0)