This is not attractive at any tier of play
Your chance to die: If it backfires, you take 9d10 damage, on average 49.5. You still have 1% chance to take 70 or more and could take up to 90. A typcial Bard has d8 hit points per level. Even with max hits in level one, 5 hits per additional level and a light Con bonus (Bards need high Dex due to light armor, so unlikely to be better than +1), you would need to be 13th level or higher to cast this with a reasonably low risk of dying, and 17th level to survive for sure, assuming you are at full health. You would never want to cast this at low levels.
Your chance to win the contest: What is your chance to win the contest? The Bard will have +5 bonus to Charisma ability rolls from level 8 on if they maximize their Charisma, as typically is done, and get Jack of all Trades, so a total +7 from level 9, and +8 at best. The average CHA bonus in the Monster Manual is +0, however monsters of CRs greater then 10 tend to have a bonus more in the +3 to +4 range.
By the time the bard could use this without significant risk to die, the margin is about +4 away from an even 50/50 chance. So your expected result is dealing 6d10 (33 at face value, many monsters have fire resistance, espcially at higher levels, so in practice you can expect about 30 damage) 66% of the time and eating 9d10 33% of the time. Overall, you would expect to deal next to nothing net with this (about 3 points).
Now, you can of course pick and choose whom you use this against, and if you can figure out which monsters have low Charisma you can improve the odds. But outside of a couple deplorable (and typically weak) monsters with negative Charisma boni, even in the best case of +8 on the contest, your odds would be only about 80% to win vs 20% to lose, and you only get to deal 14 points more than you expect taking.
Your expected pay-off: So in effect, you risk dying for the chance to net make 3-14 points of damage. There is no spell level where this becomes a good deal, not even on level one.
Bards already have few spells to pick and having narrow ones is not the way to go for them. This spell is as Thomas says, useless and a bad choice to try on any level of play.
The design is not a good fit for 5e: As an added note, 5e in general does have very few spells with adverse effects on the caster in case of unlucky rolls (Contact Other Plane or a botched Teleport, are about as bad as it gets). The swingyness of the outcome here creates a powerful effect that could deal damage in amounts one would expect maybe from a level four or five spell, but low net benefit would point towards a much lower level spell, even if the numbers are tweaked. In general, anything swingy is to the disadvantage of the characters, as they will be in many, many combats, while the typical opponent is only in one against them. 5e is aiming at more predictable combat outcomes, with larger amounts of hit points, and fewer save-or-die effects than older editions of the game.