According to Tom's CPU charts, there is a measurable difference between the fastest and slowest CPUs they tested in 2009. This is not surprising. But the difference is not that huge, either. For now, I'm considering AMD chips only, due to Intel's offensive use of patents and lawsuits over the years.
For instance, 3DS Max rendering can be sped up by a factor of 2.2 by upgrading from a AMD Athlon II X2 240 Regor 2.8GHz ($53) to a AMD Phenom II X4 965 Black Edition Deneb 3.4GHz ($186). Not all benchmarks benefit as much, though. MP3 encoding with LAME is only 1.2x faster. Adobe Premiere is 2.5x faster, and FarCry has 1.4x the FPS. The difference between scores has to due with whether the application is multi-threaded. Content creation, therefore, has the best chance of significant speed-up, but it's certainly less than linear, with respect to cost. Modern video games benefit as well, but will tend to depend much more on your video card.
This suggests that the cheap-ass approach would be to buy a good MB with a cheap CPU now, and then upgrade later. Unfortunately, the latest AM3 sockets don't support AM2 CPUs. So you'll have to buy the cheapest AM3 CPU you can find ($53, as quoted above), if you want the longest upgrade path (AM3 was released in Feb 2009, so it should be around for a while). AM3 supports DDR3, whereas AM2 only supports DDR2.
DDR3 costs $120 for 4GB (crucial.com). DDR2 costs $116-$134 (depending on speed) for the same GB. So it looks like the price has mostly equalized. Meanwhile DDR1 is $300 for 4GB, which goes to show: don't get stuck on an old technology - it's not going to be cheaper forever.