Of course it doesn't pay for itself. Cities with their GDP growth pay for it (financially), and exurbs with their cheap sprawling subdivisions and giant SUVs benefit from it.
People are most needed where they would create the most value, regardless of cost of living.
Let's say your programming labor would be worth $120k/year ($40k in taxes) in San Francisco, but you're stuck making $50k/year ($10k in taxes) in Indianapolis because San Francisco would still be a net less for you.
Your not being in San Francisco costs the government $30k/year. Any subsidy below $30k/year that gets you to that more productive job is profitable for the taxpayer.
Now imagine someone who costs taxpayers $20k/year in Appalachia, but could generate $30k/year ($5k in taxes) as a barista at the Googleplex. It's still worth any price below $25k to get her to Mountain View.
Of course, these subsidies might just arm you (and the barista) to displace other, also productive Bay Area residents, resulting in no net change. There would also have to be a zoning policy intervention to create new units.
I do agree that high and low density regions are codependent, and subsidies are appropriate in both directions. I simply argue that subsidies to get people to high-productivity cities are much less costly (they can even be profitable!) than subsidies to help people stay in low-productivity situations.