SQL raised to a power of error "An invalid floating point operation occurred."

Question

I have this code

declare @termtomonths float
set @termtomonths=120/365.00 
set @termtomonths= round(@termtomonths,2,1)*12
select power(1-(1+0.11/12.00), -3.84)

When i run it, it return an error of

    Msg 3623, Level 16, State 1, Line 6
An invalid floating point operation occurred.

It try this formula in MS EXCEL and it works

=1-(1+0.11/12)^-3.84

expected result

  0.034432865

How can I possibly convert this excel formula to sql formula?

Thanks

Answer 1

Your expression is wrong in SQL. To get the same as you have in Excel you should do this:

SELECT  1 - POWER(( 1 + 0.11 / 12.00 ), -3.84);

This gives you: 0.0344326
Note you have the 1 - inside the POWER function in SQL, but not in Excel.

The error is what also causes you to get the floating point error, because your base cannot be negative.