So how much of a difference is there between a 2 megapixel image and a 5 megapixel image? A lot less than you might guess. This is because the number of pixels grows as the square of the dimensions of the image. For instance, a 2 megapixel image is 1600x1200; if you double each dimension to 3200x2400 you would then have a 8 megapixel image. But do you really need an 8 megapixel image?
Keep in mind that your home LCD is unlikely to display even a 2 megapixel image at full resolution - a common LCD size is 1280x1024. A common widescreen form factor is 1680x1050, which is still too small to show a 2 megapixel image unless you crop or squash the image. If the image is any larger than your LCD's maximum resolution, the image will have to be re-sampled down to the LCD's native resolution before display.
That said, display technology is getting better. Today's high end LCD will likely be the common size in a few years. So to future proof your pictures in terms of monitor display, might you want to shoot higher than 2 megapixels? For instance, a $1500 LCD monitor usually can display 1920 x 1200. Again, unless you crop, that's still just big enough for a 2 megapixel image.
The only real argument for shooting higher than 2 mega pixels, then, is if you engage in one of two practices: cropping your photos after you take them, or printing your photos.
For printing, you could argue that the more pixels the better, but here again current technology doesn't take advantage of all those pixels. For instance, 4 megapixels is enough to print a 8.5 inch by 11 inch photo a 200 DPI. That's a pretty high resolution photo at a pretty large size. Only if you want a huge, wall mounted photo will you really benefit from more megapixels. For cropping, of course, it is true that the larger the resolution the tighter you can crop final image. Of course, many of us never even get around to sorting our images, let alone cropping them.
Meanwhile the benefits from using fewer megapixels are quite measurable. You'll be able to fit more picture's in your camera's memory, your camera will likely require less delay between taking pictures, and you'll be able to fit a lot more photos onto a DVD when you make backups of your photo albums.
That's why I only rarely shoot at higher than 4 megapixels.
Here's a visual diagram that shows the relationship between the different megapixel sizes.