Well, at first you have to differentiate between Summer and Winter Olympic games, because the requirements are different and the outcome might be different. In Canada, the Olympics cost around 1.9 Billion $ and most of the winter venues can be used to attract skiers and snowboarders in the next years. It will most likely be a cost-effective investment in the long run (or at least not a total loss). Also Canada is in a good position to take the bump due to the Olympics. In Greece, the situation is vastly different. Here, we had the dreaded summer Olympics and we had one of the smallest countries ever to host the Summer Olympics. The costs to Athen were immense. After 6 years, we now know that the Olympics cost Athen 15 billion $. Most of the infrastructure built is no longer used, the stadiums are empty and the shops next to it closed because no business was available.
However, one could argue that the newly built metro and such would be a boon the city. Alas, as city planners around the globe know: public transportation via trains and light rail NEVER is a win. There are too many drawbacks and the costs are too high, even in densely populated areas like Athen or European Cities in general. So, even the most useful device built during the Summer Olympics comes around to haunt the city budget.
This is one of the reasons that Summer Olympics can be a crusher to cities and its financial budget. It is generally not a boon to the city, except as very very expensive marketing, but it doesn’t pay off. There are cheaper ways to promote the city than acquiring the Olympics.