Modo doesn't have fluid dynamics.

RealFlow is one program for this sort of thing.

Hello,

If it is not for commercial use you might check out Houdini Apprentice HD for $99 plus a learning curve.

It really depends on how complicated your animation needs to be. You may not need physics simulations at all depending on what you're doing. In that case an easy to use and affordable program like Carrara might do the trick.