The problem isn't really modern CPUs but the lack of improvement in conventional hard drive speeds. With a Core i7 processor and a 160 GB X-25M Gen 2 Intel SSD, pretty much everything I run loads within 1-2 seconds, and there is little or no difference between loading apps from RAM or my hard drive. Even with a 1 TB WD Caviar Black 7200 RPM drive, my Core i7 machine was constantly limited by the hard drive.
With an SSD, I boot to a usable desktop and can load Firefox with Adblock, Pidgin, Skype, Foobar2000 and Word in around 2 seconds. Many programs like Chrome load so quickly that they are effectively instant-on. Even though quad-core processors are often derided for desktop use, I see a tremendous improvement with a Core i7 + high-performance SSD vs. a Core 2 Duo + mediocre laptop drive. Modern CPUs can make your desktop experience much more responsive. You just need a hard drive that can keep up.
Oh, and in video playback, the difference is incredibly obvious. My roommate is still using a 7 year old laptop which can barely playback a DVD (MPEG-2). In contrast, my Core i7 can simultaneously decode 5 1080p H.264 videos with ease (after this point, the hard drives can't keep up). While this might be considered useless, it definitely makes a difference when running background tasks such as backups. With my Core 2 Duo without hardware decoding, I would have to pause when scheduled backups started or video would skip. With my quad-core system, I can run any task in the background without fear of slowdown, and also use high-quality upscale filters and renderers that would have slowed my dual-core system to a crawl.
Too many people claim that modern processors and hardware do not provide meaningful improvements to the desktop experience. I just don't find this to be true. Multi-core processors have allowed users to run background tasks, install software etc. with no noticeable speed degradation. When I am working with old single-core machines, I miss this benefit.
In addition, today's software is more powerful. You may not need all the features in Word 2007 or the latest Firefox build, but that doesn't mean they aren't useful.
Adblock, Flashblock, Session Management, the ability to have dozens of tabs loaded without memory issues, the ability to stream high definition video in my browser with no or minimal buffering, the "Awesomebar" etc. are all features that didn't exist 5+ years ago.
Real-time indexing of system files and applications is relatively recent, and yet I find that it has fundamentally transformed how I access data.
There are many more examples. It may be popular to say that things haven't changed much in two decades, because word processors are superficially similar for example, but a great deal has changed.