Some of the above answers need a bit of modification. Length of a bullet is the most significant factor in how much twist you need. Mass is the next most significant factor, which is why the old Greenhill formula in its second most complex iteration has a square root of specific gravity ratio appended to it. When you add mass without changing the size and shape you decrease required rifling pitch (twist rate) and increase BC in proportion to the increase in mass (increased net density of jacket and core). For two identical shape bullets with the same jackets and core volume, but one with a lead core and the other with a gold or a tungsten core (gold and tungsten are the same density; 19.30 grams/cc while lead is just 11.34 grams/cc), the one with the gold or tungsten core would be both heavier and have a higher BC and require slower spin to be stable at a given velocity than its lead core counterpart. That is because gold and tungsten are 1.7 times more dense than lead, so they weigh that much more than lead an equal in an equal volume. It may seem backward that the heavier bullet needs less spin, but all the charts saying a heavier bullet needs more spin are assuming the bullet got heavier by being longer, not by changing density, and it is actually that added length that causes it to require a shorter rifling pitch.
How much energy from the powder goes into the spin? With conventional rifling, not a lot. I've used my CAD software to model a bullet and find its moment of inertia about its spin axis, and from that used the
formula for kinetic energy of rotation to see how much is in the spinning bullet. It then subtracted that amount from the muzzle energy as if it were directly lost from it and then I worked backward to the resulting new muzzle velocity at that lower muzzle energy. I found the difference was on the order of one foot per second. This was a while ago and I don't recall the bullet or the muzzle and spin velocities involved, but the bottom line is that it was less than shot-to-shot velocity standard deviation.
With very extreme rotation rates the above might change, but what are the limits of rotation? The British found long ago that you could not make a gun work with twist faster than about 6 calibers. Faster, and the bullets just strip in the rifling or blow the gun up. But that's very fast. It's a little faster than a two-inch twist in 30 caliber guns. Most guns have much less than that, so I don't think we have any practical need to be concerned with this limit today.
Another limit is that too much rotational acceleration will cause core stripping in a cup and core bullet. This is where the rotational acceleration at the pressure peak is so high that inertia pulls the core loose from the jacket and slips inside it. So the jacket gets full rotational velocity (angular velocity in physics) but the heavier core is turning more slowly when the two exit the muzzle. With the rifling behind them, the two equilibrate their angular momentum and you wind up with a bullet unbalanced by the gap developed between the jacket and core and spinning at a lower rate than intended. Harold Vaughn measured this happening in a 270 Winchester when a light bullet was driven too fast in it. Scattergun accuracy.
For solid bullets, more can be done with fast twists. I don't know where the limit will lie. One thing that has changed over the last 50 years is the average bullet manufacturer is doing a better job of making bullets with axially symmetric mass. That is, if you make your cartridge perfectly concentric so the bullet doesn't tilt any in the bore, you can spin them faster than you used to be able to do before groups open up due to the lateral center of mass drift or wobble in flight. This has increased the bullet lengths we can use because the more rapid spin required doesn't any longer impose as low a limit as it once did on bullet length.
I expect those limits to keep growing as bullet makers get still more precise in what they do, so I think you can expect heavier and longer projectiles to continue being introduced as time goes by. The 6.5 Creedmoor has a longer throat than the 260 Remington so it can accommodate extra long bullets. When bullets get even longer you will see new chamberings with still longer freebores.
So, where is the point of diminishing return? That is something you have to work out based on practical considerations. Every time you double the BC, you reduce bullet drop by a smaller factor as the time of flight gets closer to the vacuum time of flight. But wind drift cuts at least in half or it can be more, depending on where in the drag curve the average velocity lies. The changing slope of the drag curve with velocity makes this difference non-linear. So you just have to run a ballistic calculator with the possible new values and see how much difference it makes to how you shoot. Diminishing wind drift is certainly desirable as it reduces the accuracy requirement for your wind reading skills, one of the tricky parts of long range shooting.
And no diminishing returns either!