My last CPU upgrade in my desktop came with a fan that about doubled the noise from my system, and more when the CPU got cranked up. At suggestion of a friend, I installed a closed-loop water cooler on it (which cost me ~$70 IIRC). Night and day.
It actually runs cooler all the time (I was monitoring the temp before/after because I was curious) and the whole computer now makes less noise than before. When the furnace comes on, the sound of the air coming from the vent is actually louder, to put it in perspective.
Also, my computer has no 'window' in the case, extra lights, or any of that 'enthusiast' crap that you'd probably associate with liquid cooling and people that brag about it. In fact, aside from recommending it to another friend once, this is the most I've ever said/typed on the subject.
TL;DR: liquid cooling makes your computer nearly silent, is very effective at cooling, and you don't have to be a fanatic to use it.