The cost savings can come from a lot of places. I speak more to AWS rather than Azure
-Don't have to invest in additional hardware to handle a burst of volume. For example, a retailer that gets increased traffic for Black Friday. You can pay for the extra capacity just when you need it, not for the year
-Only run dev/test environments when needed. My dev and qa teams don't need their environments up at night, so we can shut them down from 5pm to 7am. That's 14 hours of time that I don't have to pay for
-If you use the hosted services, you can save money on personnel needed. As an example, I can pay 4 DBAs to manage a MongoDB cluster, or I can use DynamoDB service on AWS instead. I'll probably only need some time from the devs now, instead of requiring a full DBA team
-The ability to stand up an environment in minutes, try something out, and tear it down again is tremendous. I can, very easily, instantiate an environment with multiple services, all running multiple instances, all with load balancing, including code deployments in about 15 minutes. I can tear it down in about 5. Even better, my developers can do this as well. That code (more scripting than coding) they used to provision the environment is an asset that can be promoted to PRD as well. This flexibility is available from your own VMware environment, but only with the purchase of pricey additions, and that doesn't translate to cost savings.
There can be a lot of cost savings, only if you really embrace the methodologies and practices. You example of SQL server isn't a good one. SQL server isn't very cloud friendly, primarily due to licensing, and would really be an archaic service to try and push to the cloud.