The tradeoff is too large to justify using at any level
As mentioned in other answers, hit points are a very valuable resource in this game. If you're going to use them for offensive purposes (which has not been done in official spells, as far as I know) then they'll need to either give a very strong effect, reflecting how this self-damage could almost kill you instantly. There's also the issue that this self-damage could interrupt any concentration spell you're currently sustaining, which could be a huge loss during a fight.
A possible approach to balancing spells using the existing ones
Most offensive spells in a chosen tier have an "average damage" they tend to gravitate around. The way those spells are balanced around this value without being identical is by adding "modifiers" to them. Those modifiers can be positive or negative :
- Positive modifiers are usually additional effects (debuffs on target, buffs on self or allies, long-term effect in an area or on a target...).
- Negative modifiers are usually conditions that make the spell harder to use (saving throws to reduce/nullify the spell's impact, restrictions on possible targets, damage being dealt over time rather than instantly...).
Usually those modifiers are used in tandem with adjusting the actual damage value, in order to make a spell better in some situations and worse in others. All of that to say, balancing a purely offensive spell means making it stronger than other spells in the same tier in some situations and weaker in other situations.
If you really want to make a hit-or-get-hurt spell, here's an idea of a rebalance of your spell as a cantrip.
The gambler's Vicious Mockery ?
The closest bard cantrip I could find is Vicious Mockery (which, conveniently enough, has identical casting conditions), with the following :
- Positives : target doesn't need to understand you ; 1d4 psychic damage (one of the least resisted damage types in the game) ; on hit, gives disadvantage to the target's next attack roll.
- Negatives : Creature needs to hear you ; has a saving throw ; does nothing on a successful saving throw.
With that in mind, let's go over each of those elements (keeping damage for last), and how to modify them according to your spell idea :
- Target doesn't need to understand you : this one is a bit difficult, because it depends heavily on your DM and the kind of enemies you'll face. Having the need for the enemy to understand you would be nothing more than flavor in some settings, while it could make the spell near-useless in other settings. For the sake of keeping it on par with Vicious Mockery in terms of possible uses, I'd either keep that in, or make it so that the spell makes it possible for the target to magically understand the meaning of the words linked to the spell.
- Giving disadvantage on the target's next attack roll : this is what gives the spell flavour and utility, and is also what makes the cantrip unique. Removing it means removing a strong aspect of the spell, so we'll need to compensate later.
- Creature needs to hear you : mostly flavour, the implications are very rare. We can keep that in.
- Saving throw : here, the saving throw is the key component of our spell's "gambling" aspect, so it makes sense to keep it as you mentioned it. I'm unsure of the balancing implications of having it be either Charisma or Wisdom, this might be worth a question of its own.
- When the target's saving throw is a success : and here's the spell's second key component. Dealing a small amount of damage to the user on failure will introduce the "gamble" aspect properly. The damage in itself doesn't need to be incredibly high, a simple 1d4 of psychic damage would be enough, especially since the damage could break concentration. Alternatively, you could make it so that the self-damage is higher (1d6 for example) but does not create concentration checks.
Finally, the damage itself. To balance out the conditions and risks of using this spell, we need damage high enough to make the player hesitate between this spell and other alternatives. Keeping the psychic damage type makes sense with the spell's theme, but it's also important to note that fire damage, while effective early on, is one of the most resisted damage types later on. Because of this, my proposition is to give this spell the following damage and scaling :
- Base damage : 1d4 psychic + 1d6 fire (1d4 psychic self-damage on fail)
- At 5th level : 1d4 psychic + 2d6 fire (1d4 + 1 psychic self-damage on fail)
- At 11th level : 2d4 psychic + 2d6 fire (1d4 + 2 psychic self-damage on fail)
- At 17th level : 3d4 psychic + 2d6 fire (1d4 + 4 psychic self-damage on fail)
This kind of trade-off spell could work better for prepared casters
As a bard, you already have very limited spell choices. Because of this, it will be much more difficult to make "tradeoff" versions of spell work, since you usually can't have both of them at the ready to make the choice of which one to use depending on the situation. However, I could definitely see this concept for a spell working on a "prepared" caster.