The main reason to avoid going too small, IMO, is because of the hand placing. The problem is not soldering the tiny parts, it's getting the part on the board in the first place. The smaller the part, the smaller the needle you need to pick it up. The easier it is for parts to stick in the tape (if you're picking them up from the tape), or to pick up a clump of 3-4 of them if out of a dish, and the tiny parts are more likely to cling to the pickup needle if it isn't meticulously clean, instead of immediately falling off. It is also harder to see if the pads are pasted/fluxed and blank or if you already put that spec of dirt on top until you get the board under magnification.
IME, the pickup tools and the tweezers are more important than the soldering iron. I have never had any trouble soldering tiny parts, even when I use a $10.00 iron to put 0201 resistors on protoboard.
For prototyping, another reason is that 0402 and smaller resistors almost never have markings on them. 0805 and larger are always marked, IME. 0603 are 50:50.
For these reasons, I tend to bottom out at 0603 for resistors, but I am ok with using 0402 caps if the specs and price are good. The caps are taller/heavier and are thusly not as annoying to handle; they still act like they have some mass rather than being a tiny flake of styrofoam that clings to the tiniest bit of magnetism or nanogram of finger oil. Plus they won't be marked with a value, anyway.