There's an excellent tutorial on this subject called Understanding Digital Pixels: PPI, Dithering and Print Size (click the link to see it) at the CambridgeInColour site. It includes a handy table which suggests that a 5MP sensor can reasonably be used for 9.1" x 6.1" prints at 300dpi and 13.7" x 9.1" prints at 200dpi. If those print sizes are good for you then it looks like 5MP is too.

I agree many of us probably have more Megapixels than we need *most* of the time, but remember having more allows you to crop into the image and still be left with a reasonable amount to make a nice print.

If you rarely crop though, you can get by with relatively few. The 6 Mpixel sensor on the Nikon D40 for example produces really nice results...

And of course the other downside to having more megapixels is the corresponding increase in noise levels and reduction in dynamic range... although it's not always that clean-cut, as modern sensors often make more efficient use of their surface area than older models...

Seriously - I have been taking macromosaics of butterflies, and when you can zoom into your image right down to the individual scales on the wings - it's absolutely fantastic. You can then of course print these images out (at a fairly large size!) - and the resulting image is extraordinary. Stand some distance away and it looks just like a nice big image of a butterfly. But what's odd - is that you can walk right up to the image, get out a magnifying glass - and see the butterfly in incredible detail (like looking down a microscope).

So if you're interested in this sort of photography pixels (up to Gigapixels) matter.