It depends completely on your own tastes and, of course, your budget.
The best places I have been;
Maui, but only if you are staying at the Royal Lahaina resort as they are the only ones with a fireworks permit. This may very well have changed by now.
If you REALLY love fireworks try a major Chinese city on the lunar new year, pure insanity!!! Imagine the "grand finally" at a major US fireworks display. Now imagine it going on 360* 24 hrs a day for 2 weeks. On the actual New Years Eve go to one of the really big temples with about a million or so Chinese. They place candles on an alter with incense. Pretty calm yeah? Except that there are so many that by nights end the fire is the size of a 3 story house and everyone young and old has to light of a string of huge firecrackers 4' in diameter.
Medellin, Colombia can be crazy fun.
I really tend to go to private parties on New Years. They are short on insanity but I think I have the best time with friends.