Short answer: No. For the vast majority of users, any kind of water cooling is, not only completely unnecessary, but also yields significant practical downsides regarding the maintenance, cost, and potential to damage other components in your rig.
It's hard to argue with the stunning looks that a custom water cooling loop, or even an AIO (All In One liquid cooler) bring to your PC's aesthetic. To be totally honest, that is the main reason I would even consider liquid cooling: It makes your PC look cool as hell.
Aesthetics aside, Liquid cooling does offer some practical benefits. The actual liquid running inside the loop has much more heat capacity than a typical air cooling heatsink. This means that the liquid cooler can absorb and hold a lot more of that heat, keeping CPU temps stable for brief, but demanding workloads. This has the side effect of taking a bit longer to transmit that heat into the surrounding air, which makes it a bit easier for your AC unit to keep up with maintaining a comfortable room temperature.
Liquid coolers are also much better at carrying heat away from your CPU very quickly. This is great for CPUs that get REALLY hot. The liquid allows you to take all that heat right off the IHS (Integrated Heat Spreader) and distribute it to a much larger surface area than a traditional air cooler via radiator(s). For this reason, liquid coolers offer a bit more cooling performance, yielding temps 4 to 8 degrees lower than their air cooled counterparts. This also means that you don't need to mount a big and bulky heatsink directly onto the CPU, which is great for avoiding damage during shipping. The water-block is typically lighter and has much less leverage on the motherboard's mount point, while the radiators can be securely mounted to the case.
Because of these benefits, liquid cooling looks like a viable performance option for high end users. However, what is often forgotten is maintenance and failure rates. Common sense indicates that electronics and liquid generally don't mix, and spilling a quart of dyed liquid onto the back of your $1000 GPU is a great recipe for a bad day. For custom loops, leaks happen, especially when built by inexperienced users. However, AIO enjoyers generally need not worry because their loop comes pre-filled and assembled.
Pump failures occur somewhat often in AIOs. While the more reputable brands will warranty up to 5 years, the typical AIO warranty is only 2 years long and I wouldn't expect performance for much longer than that. I've seen a lot of anecdotal accounts of some pumps lasting nearly 8 years, but in my experience that is quite rare. Personally, I've had several pumps fail and each time it has been a big hassle that cost me more time and money than it should have.
Finally, maintenance: I like to clean my desktop every 6-12 months and with an air cooler, this is made considerably easier. The cooling fins on liquid cooler radiators have incredibly thin gaps compared to their air-cooled counterparts, so dust and hair can accumulate and clog up those gaps very quickly. Furthermore, the radiator/fan assembly on liquid coolers is often oriented such that the dust will get sandwiched between the fan and the radiator which means you need to take apart the fan/radiator/mount assembly in order to properly clean it. Most air coolers just have 4 clips holding the fan mount to the heatsink and removing the fan for cleaning is fairly straightforward.
At the end of the day, a custom cooling loop will get you better looks and a couple extra degrees of cooling in exchange for a headache of failure points, maintenance issues, and significant extra costs. From a user-experience standpoint, liquid cooling offers no practical performance gain at all in most situations. Liquid cooling certainly has its place in ultra high performance rigs, and even some higher end gaming prebuilts, but otherwise I highly recommend avoiding liquid cooled systems.
