There is no "exact limit" where it will go from "awesome" to "trash", it's progressive and depends on many things. The actual quality of the photo (in focus? motion blur?), the printing support and quality, who the target audience is, the way people will view the result, the quality of their eyes

, the content of the photo and what the viewer considers as "acceptable" (yep - you'll more easily see technical defects on an uninteresting photo than on one that makes you go "wow!", and if I show a large print made from a 2MP shot of their grandkids to my parents they'll find it great just as they always would for any photo of their grandkids, the technical quality becomes totally irrelevant, but then try to show it to a pro who has no ties with the subject... expectations differ greatly).
I have a shot that after cropping/treament comes up at 15MP, it's actually pretty blurry too, but it was blown up to cover the side of a 50ft trailer and looked awesome
