Blender is the best I’ve seen. You can get it at http://blender3d.org – it’s got a rather steep learning curve, but you can do amazing things with it :-)
I highly recommend SketchUp
(free). Especially for beginers! Removes all the complexity of knowing about Faces and other 3D jargon and the controls are pretty intuitive. From there you could move on to more advanced stuff like 3D Studio Max, Maya, and the other suggestions here…
blender is the most feature rich application for free. It has fluids, a built in compositor, it has a steep learning curve. It also has a reputation as being a fast modeller once you learn all its conventions ( keyboard commands etc.)
Blender. It’s free but the learning curve is steep so you have to be patient and put much effort in learning and using it. But it’s an all-in-one program so it’s worth the sweat :)