How can I calculate the break even point for Chromatic Orb versus Fireball?

Under the stated conditions, chromatic orb pulls ahead at level 6

The expected damage is proportional to the expected number of damage dice

Consider each potential damage die labeled by \$(c, d)\$, where \$c\$ is the index of the creature hit and \$d\$ is the index of the die within those that hit that creature.¹ By linearity of expectation, the expected damage of chromatic orb is equal to the sum of the expected damage contribution \$E\left[x_{c,d} \right]\$ of these possible damage dice, even though they are not independent:

$$ E\left[X\right] = \sum_{c, d} E\left[x_{c,d} \right] $$

In turn, since whether we got to use each potential damage die is already determined before we roll it, it's not possible for the number it rolls to be correlated with whether we got to use it. Thus, the expected damage contribution of each die is the probability \$p_{c, d}\$ of us getting to use it times its average damage of 4.5:

$$ E\left[x_{c,d}\right] = 4.5 p_{c, d} $$

which makes the overall damage

$$ E\left[X\right] = \sum_{c, d} 4.5 \; p_{c, d} = 4.5 \sum_{c, d} p_{c, d} $$

So the expected total damage is simply 4.5 times the expected number of damage dice.

The expected number of damage dice

The full distribution of the number of damage dice is a reasonable computation. Here is an example using my own Icepool Python package:

from icepool import d, map def attack_roll(to_hit): """Returns a die with 2 = crit, 1 = hit, 0 = miss.""" def hit_type(x): if x == 20: return 2 elif x >= to_hit: return 1 else: return 0 return d(20).map(hit_type) def step(total_d8s, can_chain, hit_type, base_d8s): """Updates the total number of d8s and whether chaining can continue.""" if not can_chain: return total_d8s, False next_total_d8s = total_d8s + hit_type * base_d8s if hit_type * base_d8s >= 9: # pigeonhole principle next_can_chain = True else: next_can_chain = d(8).pool(hit_type * base_d8s).largest_count() >= 2 return next_total_d8s, next_can_chain print('| Level | Chromatic orb | Fireball | Ratio |') print('|-------|---------------|----------|-------|') for level in [3, 4, 5, 6, 7, 8, 9]: base_d8s = level + 2 result = map(step, (0, True), attack_roll(2).highest(2), base_d8s, repeat=level + 1) orb_mean = result.marginals[0].mean() * 4.5 fireball_mean = (level + 5) * (level + 1) * 3.5 print(f'| {level} | {orb_mean:0.1f} | {fireball_mean:0.1f} ' + f'| {orb_mean / fireball_mean:0.2f}') 

You can try this in your browser here. You can change the attack roll by changing attack_roll(2).highest(2); for example, hitting on 6s but with Elven Accuracy would be attack_roll(6).highest(3).

The results:

If spell slots continued indefinitely above level 9, eventually chromatic orb would fall behind again due to the the miss chance putting an asymptote on the expected number of hits. Then again, you can also only fit so many creatures in a fireball's radius.

Final notes

Note that this gives the correct expected damage (because linearity of expectation holds regardless of independence), but you can't just sample the number of dice and roll that many d8s to get the correct distribution of damage (because this can be affected by correlation -- though I expect it to be fairly close in this case).

If you prefer, you could instead apply linearity of expectation on the damage dealt to each target rather than individual dice, in which case you arrive at something like Eddymage's answer. Equivalently, you could start with the fact that expected damage is proportional to the number of damage dice and apply linearity of expectation a second time on the expected number of damage dice per target.

¹ \$c \in \left\{1 \ldots \ell + 1\right\}\$ and \$d \in \left\{1 \ldots 2 \left( \ell + 2 \right)\right\}\$, where \$\ell\$ is the spell slot level, and the double number of damage dice is due to the possibility of crits.

The break-even point is at 6th slot

Some preliminary computations:

• Succeeding in attacking with advantage with a target AC of 2 has a probability of 0.9975.
• The probability to get a nat 20 with advantage is 0.0975.
• The expected damage of an attack is \$p\cdot D\$, where p is the probability to hit and D is the expected output of the dice.

Basic case: Level 3 Slot

At level 3, the expected dice damage of the 1st hit is \$D=23.6250=4.5\cdot5\cdot1.0975\$, where the 1.0975 accounts also for critical hit. The maximum number of further leaps is 3, for a grand total of possible hits equal to 4, and the i-th leap occurs if the (i-1)-th hit, starting from i=2.

The probability to have at least 2 equal values on the 5 dice for the i-th leap is \$P_{2}(l_i)=1-P_{\neq}(l_i,5)\$, where \$P(l_i,5)_\neq\$ stands for the probability of having all different values. One has $$ P_{\neq}(l_i,5)= \frac{8\cdot7\cdot6\cdot5\cdot4}{8^5}, $$ since one has 8 possibilities for the 1st die, 7 for the second, 6 for the 3rd and so on. Moreover, this probability is the same for each leap, so we'll call it just p. Checking also the dice from the critical hit leads to a probability of $$ P_2(l_i) = (1-0.0975)(1-P_\neq(5)) + 0.0975(1-P_\neq(10)) $$ where the notation has been shortened. Denote with h the probability to hit: therefore, the expected damage for the 3rd level Chromatic Orb is $$ \begin{eqnarray*} E[dmg] &=& hD + h^2P_{2}(l_1)\cdot D + h^3P_{2}(l_1)\cdot P_{2}(l_2)\cdot D+ h^4P_{2}(l_1)\cdot P_{2}(l_2)\cdot P_{2}(l_3)\cdot D\\ &=& hD + h^2pD + h^3p^2D+h^4p^3D \\ &=& D\sum_{i=0}^3h^{i+1}p^i\\ &=& 74.2 \end{eqnarray*} $$

Generalization for further upcasting

The general formula for \$P_\neq\$ with k leaps, so for the further upcasting, is $$ P_\neq(k) = \left(\displaystyle\prod_{i=8}^{9-k}i\right) \Big/ 8^k $$ which naturally leads to the formula for \$P_2\$. For k=9, \$P_2=1\$, obviously. Hence, the comparison with Fireball is now straightforward:

The break-even point is at level 6, from a pure damage point of view.

As observed in HighDiceRoll's answer, Fireball will overcome Chromatic Orb in impossible scenarios, when slot levels increase towards infinity.

Other considerations

The two spells are quite different in usage: Fireball is the signature spells for Wizards and the like and can hit several enemies at once, but, unless one increments their initiative bonus for casting at the beginning of the fight, it requires precise situations for avoiding friendly fire. Moreover, this spell deals only fire damage, unless some game feature (or the DM) allows to change it. On the other hand, Chromatic Orb is a more versatile from this point of view, since the caster can chose the damage type and hence adapt it to the enemy, but it deals less damage and hits a lower number of creatures.

The major consideration, at least for me

For my characters, I prefer role playing and personalization, and since it seems to me that it is the same for you, I suggest to take whatever you like to play, disregarding min/max strategies.