Money spent on the space program does stay on the Earth. However, money spent on the space program, or on anything else, leaves the hands or bank account of the one who spent it. If the goods and services bought with it and received in return aren't worth that money, it was wasted. And if going to space serves no useful or rational purpose, then the cost of it is a waste of money.
Even if, because the parts going in to the rocket and the capsule were made in the U.S.A., it's only really a waste of time and effort, and not of foreign exchange.