Is there even any reason to use a hand crossbow over higher damage crossbows?
With Crossbow Expert feat you get additional bonus attack with hand crossbow if you use action to attack with one handed weapon, which can be hand crossbow btw. Meaning at level 4 you can already have 2 attacks as opposed to one, that's 2d6, already better than heavy crossbow, especially considering that each attack gets your ranged modifier, dipping and so on. That's easy more than greatsword damage-wise.
You can then take a Hunter Ranger, combine with Sharpshooter perk at level 8 and Hunter's Mark and then if you use hand crossbow you will have 3 ranged attacks per turn, each with +10 damage bonus from Sharpshooter, additional 1d6 damage from Hunter's mark, additional 1d4 from dipping and maybe even poison on top. That is outright the best single target damage at that level or one of the top ones possible at level 10 cap intended for BG3 and it is better than Heavy Crossbow, because of Sharpshooter, Hunter's Mark, dipping and so on rolling of number of attacks and with hand xbow you get 3 compared to usual 2.
If you calculate all these bonuses 2 attacks as Hunter Ranger with Heavy Crossbow (2 attacks per turn) will net:
Heavy Crossbow: 2d10
Dexterity (16 -> +3): 6
Enchant (+1): 2
Hunter's Mark: 2d6
Total 34-68 damage in one turn, assuming it all hits.
As Hand Crossbow (3 attacks per turn):
Hand Crossbow: 3d6
Dexterity (16 -> +3): 9
Enchant (+1): 3
Hunter's Mark: 3d6
Total 51-90 damage in one turn, assuming it all hits.
That's a massive difference in favor of Hand Crossbow there.