Great engine sound is one of the key ingredients that make an impressive car. And many even see it as music, as the artist's picture shows here. The difference between what you can hear from a Porsche Carrera RS 2.7 (1973) and a Porsche 996 (2003) is mind boggling.
Here are a couple of hypotheses to be debated:
(1) The more fuel efficient an engine the more boring the sound
(2) Straight six cylinders sound better than V6
(3) Sound engineering helps
(4) Modern electronics will replace the "natural" sound to compensate for what's missing
To make an example for (1): When you replace carburetors by a fuel injection the sound becomes less attractive, as you can see with the Ferrari 308 GTSi versus the 308 GTS, or the 512 BBi versus the 512 BB. When you improve the engine efficiency by adding a direct fuel injection then it gets worse even. So, what has been won in efficiency is lost on sound all to often. Partially this was compensated by adding technology to the exhaust system, which proves (3). Look at all the Aston-Martins and most modern Ferraris. Ever listened to a Jaguar D-Type or a JWF Milano GT? Both have straight six engines and sound great. Same with BMW engines, neither Mercedes nor Audi have been able to compete with BMW in terms of sound quality. Modern technologies such as loudspeakers in exhaust systems (and the car interior) plus sound generation computers will more and more add what is missing from the mechanical department. Which is what I mean with point (4).
But, whatever the modern sound engineer will be able to do, it doesn't replace the sensation we have listening a vintage car or 60ies sportscars driving by. Noise/music coming from mechanical components and open exhaust systems will always sound better.