Your problem probably has nothing to do with floating vs numeric data type. From the definition of IRR and the linear optimization method it follows that sometimes the IRR estimate @X may be <= -1 (or -100 %), and raising zero to a negative power causes an error (as it is equivalent to raising (1/0) to a positive power). Also, IRRs at or below -100 % make little sense, so you may want to control the result somehow anyway.

I wrote a set-based solution, an inline table valued function for IRR (in TSQL), capable of calculating an unlimited number of IRRs simultaneously. See https://sqlperformancemeasurement.blogspot.com/2024/12/calculating-irr-dpi-rvpi-and-tvpi-part.html