Note for context: when originally written, Steady Aim did not exist. I've since attempted to include the math/examples for the Steady Aim cases into the original answer, but did not rewrite the entirety of the analysis/answer as it is an optional feature.
There is no point at which +2 Dex increases your DPR more than gaining an extra attack as a bonus action via Crossbow Expert, if you are always spending your bonus action on more damage, if Steady Aim is not available, and if you would just be attacking once with a light crossbow in the case where you don't have Crossbow Expert.
Steady Aim being allowed makes +2 Dex a significantly stronger option against med-high AC targets, and makes up most of the difference at low AC levels.
For the following, I'm assuming that pre-level 4 you are already at 16 Dex from the numbers in the question. I'm also assuming you'll be able to use your sneak attack on any target you hit.
As you note, the effects of +2 to Dex are +1 to hit and +1 to damage, while Crossbow Expert grants a second chance to hit for your usual attack damage. So, to compare, we'll be using the following stats:
Level 4 with Crossbow Expert:
- +5 to hit (+3 from 16 Dex, +2 from proficiency)
- Two attacks at 1d6+3 each
Level 4 with +2 Dex & light crossbow:
- +6 to hit (+4 from 18 Dex, +2 from proficiency)
- One attack at 1d8+4
- As of Tasha's Cauldron of Everything, there is a new optional feature called Steady Aim that would let you more reliably attack with advantage at the cost of a bonus action, which I've analyzed separately in the below examples.
Sneak Attack for both is 2d6 if any hits occur on the turn, limited to once per turn.
Let's start by looking at the best-case scenario for +1 to hit- when you otherwise would require a 20 to hit, and the +1 to hit effectively doubles your hit chance by letting you also hit on a 19. So, for this example, let's say the target has AC 25.
Vs 25 AC
Crossbow Expert
You would have 2 5% chances to critically hit for 2d6+3 damage each, and a 9.75% overall chance of Sneak Attack occurring for 4d6 (since you have a 9.75% chance of at least one attack hitting, which is when Sneak Attack would occur, and its damage would double from the usual 2d6 due to the hit being critical).
With 3.5 being the average result of a d6, this works out to an average expected DPR against a 25 AC target of:
((2d6+3) * 0.05 * 2) + (4d6 * 0.0975)
(((2 * 3.5) + 3) * 0.05 * 2) + (4 * 3.5 * 0.0975) = 2.365 expected DPR
+2 Dex
You would have 1 10% chance to hit for 1d8+4 damage, plus your Sneak Attack for 2d6 more. Essentially, 5% of your rolls will be for 'normal' damage of d8+4+2d6, and 5% of your rolls will be for critical damage of 2d8+4+4d6, with the rest being misses.
With 4.5 being the average result of a d8, this works out to an average expected DPR against a 25 AC target of:
((d8+4+2d6) * 0.05) + ((2d8+4+4d6) * 0.05)
((4.5 + 4 + (2 * 3.5)) * 0.05) + (((2 * 4.5) + 4 + (4 * 3.5)) * 0.05) = 2.125 expected DPR
+2 Dex, Steady Aim
You would have 1 19% chance to hit for 1d8+4 damage, plus your sneak attack for 2d6 more. Essentially, 9.25% of your rolls will be for normal damage of d8+4+2d6, and 9.75% of your rolls will be for critical damage of 2d8+4+4d6, with the rest being misses.
With 4.5 being the average result of a d8, this works out to an average expected DPR against a 25 AC target of:
((d8+4+2d6) * 0.0925) + ((2d8+4+4d6) * 0.0975)
((4.5 + 4 + (2 * 3.5)) * 0.0925) + (((2 * 4.5) + 4 + (4 * 3.5)) * 0.0975) = 4.06625 expected DPR
As you can see, without Steady Aim, the Crossbow Expert feat provides a higher DPR in this case- 11.3% higher, or so. This gap widens as you get closer to the middling ranges of hit chances (~50%), as that favors two chances to hit more than a flat +1 to hit once- mainly because of how big the impact of two chances to land your one sneak attack per turn is.
Steady Aim on top of the +1 to hit does a lot of heavy lifting in this specific scenario, giving a much higher chance of landing a single hit for triggering Sneak Attack. It has a ~72% DPR lead over Crossbow Expert.
Let's do the math again with the target having 23 AC instead, just to demonstrate that the gap (without Steady Aim) is widening as AC begins going in the direction of more reasonable ranges (and that the benefit from Steady Aim is shrinking):
Vs 23 AC
Crossbow Expert
You would have 2 15% chances to hit the target for 1d6+3, and an overall 27.75% chance for at least landing one attack so that Sneak Attack can occur. Each attack that lands has a 1/3 chance of being a critical hit. Assuming you use Sneak Attack on the first attack that hits, that means that 1/3 of the times Sneak Attack goes off will be on a critical hit.
(d6+3 * 0.15 * 2) + (d6 * 0.05 * 2) + (2d6 * 0.2775) + (2d6 * 0.2775 * (1/3))
Breaking this down a bit, just for clarity's sake:
- (d6+3 * 0.15 * 2): You have 2 chances of hitting for d6+3 with 15% accuracy.
- (d6 * 0.05 * 2): On each of your two attack attempts, you have a 5% chance of dealing d6 extra damage due to a crit.
- (2d6 * 0.2775): You have a 27.75% chance of landing any attacks at all, allowing you to Sneak Attack once for 2d6 extra damage.
- (2d6 * 0.2775 * (1/3)): Since your landed hits have a 1/3 chance of being critical hits, 1/3 of your Sneak Attacks will deal an extra 2d6 damage.
((3.5 + 3) * 0.15 * 2) + (3.5 * 0.05 * 2) + ((2 * 3.5) * 0.2775) + ((2 * 3.5) * 0.2775 * (1/3)) = 4.89 expected DPR
+2 Dex
You would have 1 20% chance to hit the target for 1d8+4, plus 2d6 for your Sneak Attack. Each attack that lands has a 1/4 chance of being a critical hit, so 1/4 of your Sneak Attacks will be on critical hits for double damage.
((d8+4 + 2d6) * 0.2) + ((d8 + 2d6) * 0.05)
Breaking this down a bit, just for clarity's sake:
- ((d8+4 + 2d6) * 0.2): 20% chance to hit for d8+4 damage, plus a Sneak Attack for 2d6.
- ((d8 + 2d6) * 0.05): 5% of the time, you also deal an extra d8 + 2d6 damage due to a critical strike.
((4.5 + 4 + (3.5 * 2)) * 0.2) + ((4.5 + (3.5 * 2)) * 0.05) = 3.675 expected DPR
+2 Dex, Steady Aim
You would have 1 36% chance to hit the target for 1d8+4, plus 2d6 for your Sneak Attack. Each attack has a 9.75% chance of being a critical hit.
((d8+4 + 2d6) * 0.36) + ((d8 + 2d6) * 0.0975)
((4.5 + 4 + (3.5 * 2)) * 0.36) + ((4.5 + (3.5 * 2)) * 0.0975) = 6.70125 expected DPR
Yep, just like we thought, the DPR gap has widened even more in Crossbow Expert's favor, with it now being ~33% higher than +2 Dex without Steady Aim.
In the Steady Aim scenario, we can see its lead is starting to shrink, but it still has a significant ~37% DPR lead at this AC level.
For thoroughness' sake, let's check the other end of things, where a 3+ on the D20 is a hit and the +2 Dex would bring that to a 2+ (target AC of 8):
Vs 8 AC
Crossbow Expert
You would have 2 90% chances to hit the target for 1d6+3, and an overall 99% chance for at least landing one attack so that Sneak Attack can occur. Each attack that lands has a 1/18 chance of being a critical hit. Assuming you use Sneak Attack on the first attack that hits, that means that 1/18 of the times Sneak Attack goes off will be on a critical hit.
(d6+3 * 0.9 * 2) + (d6 * 0.05 * 2) + (2d6 * 0.99) + (2d6 * 0.99 * (1/18))
((3.5 + 3) * 0.9 * 2) + (3.5 * 0.05 * 2) + (2 * 3.5 * 0.99) + (2 * 3.5 * 0.99 * (1/18)) = 19.365 expected DPR
+2 Dex
You would have 1 95% chance to hit the target for 1d8+4, plus Sneak Attack for 2d6 more. Each attack *that lands* has a 1/19 chance of being a critical hit, so 1/19 of your Sneak Attacks will be on critical hits for double damage.
((d8+4 + 2d6) * 0.95) + ((d8 + 2d6) * 0.05)
((4.5 + 4 + (3.5 * 2)) * 0.95) + ((4.5 + (3.5 * 2)) * 0.05) = 15.3 expected DPR
+2 Dex, Steady Aim
You would have 1 99.75% chance to hit the target for 1d8+4, plus Sneak Attack for 2d6 more. Each attack has a 9.75% chance of being a critical hit.
((d8+4 + 2d6) * 0.9975) + ((d8 + 2d6) * 0.0975)
((4.5 + 4 + (3.5 * 2)) * 0.9975) + ((4.5 + (3.5 * 2)) * 0.0975) = 16.5825 expected DPR
So even at the opposite end of things, this gap still exists, favoring Crossbow Expert at 27% higher than +2 Dex (without Steady Aim). You can see that the gap is smaller than it was at 23 AC, as even with only one attack you're still likely to get Sneak Attack off.
With Steady Aim, we can see that the benefit of advantage is pretty minor when it's already so easy to hit the target AC, and it no longer makes up the gap with Crossbow Expert.
Now, let's do one last example right in the middle ranges of AC when the benefits of 2 attacks over one (or advantage) are near their peak:
Vs 16 AC
Crossbow Expert
You would have 2 50% chances to hit the target for 1d6+3, and an overall 75% chance for at least landing one attack so that Sneak Attack can occur. Each attack *that lands* has a 1/10 chance of being a critical hit. Assuming you use Sneak Attack on the first attack that hits, that means that 1/10 of the times Sneak Attack goes off will be on a critical hit.
(d6+3 * 0.5 * 2) + (d6 * 0.05 * 2) + (2d6 * 0.75) + (2d6 * 0.75 * (1/10))
((3.5 + 3) * 0.5 * 2) + (3.5 * 0.05 * 2) + (2 * 3.5 * 0.75) + (2 * 3.5 * 0.75 * (1/10)) = 12.625 expected DPR
+2 Dex
You would have 1 55% chance to hit the target for 1d8+4, plus your Sneak Attack for 2d6 more. Each attack *that lands* has a 1/11 chance of being a critical hit, so 1/11 of your Sneak Attacks will be on critical hits for double damage.
((d8+4 + 2d6) * 0.55) + ((d8 + 2d6) * 0.05)
((4.5 + 4 + (3.5 * 2)) * 0.55) + ((4.5 + (3.5 * 2)) * 0.05) = 9.1 expected DPR
+2 Dex, Steady Aim
You would have 1 79.75% chance to hit the target for 1d8+4, plus your Sneak Attack for 2d6 more. Each attack has a 9.75% chance of being a critical hit.
((d8+4 + 2d6) * 0.7975) + ((d8 + 2d6) * 0.0975)
((4.5 + 4 + (3.5 * 2)) * 0.7975) + ((4.5 + (3.5 * 2)) * 0.0975) = 13.4825 expected DPR.
In the middle ranges of hit chances, you can see Crossbow Expert has a ~39% DPR lead over +2 Dex without Steady Aim. It's also starting to close the gap with the Steady Aim variant, with Steady Aim having only a ~7% lead.
(Crossbow Expert will overtake the Steady Aim variant at the target having 13 AC or lower, with Crossbow Expert having an expected DPR of 15.415 and Steady Aim having an expected DPR of 15.22625 at 13 AC.)
Below is a chart using the same stats as above to calculate the expected DPR against a target of varying armor classes. The orange line is the Crossbow Expert rogue, and the purple line is the rogue who opted for +2 Dex and is using a light crossbow (without Steady Aim) instead.
Of course, this is all purely in regards to comparing one light crossbow shot vs 2 hand crossbow ones- the gap is smaller if we compared, say, going Crossbow Expert vs. using two shortswords and Two-Weapon Fighting with +2 Dex instead.
Plus, the route of +2 Dex has non-DPR benefits of initiative, ability checks, and AC that should be taken into account, as well as the fact that rogues have a wide variety of other uses for their bonus actions. But the scope of this question is about a Crossbow Expert hand crossbow user vs a light crossbow user with +2 Dex instead, so I'll leave it at that.
