I have my HDDs turn off after 10 minutes of inactivity. Note that this is 10 minutes of inactivity for that particular drive, and has nothing to do with whether or not the computer as a whole is inactive. On a system with multiple HDDs, the data drives will spend most of their time powered off as a result of this setting (the main system drive, with the registry, user profile, page file, etc., is never idle for anywhere near 10 minutes and so is never affected by the turn-off-the-drive setting). When there is an attempt to access something on a powered-down drive, it will spin right back up--there's only a delay of about a second or two.
And this definitely does make a difference. On one of my quiet HTPC-ish computers, you can clearly tell when its secondary drive is powered off because it's so much quieter (I'm not talking about the seeking sounds--this is just from the platters no longer spinning at 7200 RPM). And on another of my computers, one of the drives would often reach 55C from just spinning (no data access), which had me worried. But once I set it to turn off when idle, its idle temps are now just a little above the ambient case temp--much lower than before. Even if I'm hurting the drive by having it spin up and back down, I bet that not having it be at 55C all the time probably makes up for at least part of that.
Remember that keeping a bunch of platters moving at 7200 RPM is going to cost a lot of energy (and by the laws of physics, every single milliwatt used is going to eventually end up as heat), and no matter how new and fancy the drive is, it's always going to take roughly the same amount of energy to keep something physically moving that fast.