Costco manager here. Feel free to ask any questions and I will answer.
The short answer is that it all depends how you use it.
Obviously my membership is free, but were we (wife and I) regular members, Costco would be paying us to shop thanks to the cash back.
It's very, very easy to overbuy and spend on things you don't need and I would say our business depends on that. My wife and I plan things out well and adhere to a pretty strict budget and don't like waste, so I'll share how we get the most out of it.
Gas - We both fill our cars up about twice a month. My store generally is about 10-20 cents per gallon cheaper than the local spots. Over the course of a year that alone pays for the $60 membership.
Travel - We typically take two vacations per year and at least one is done through Costco Travel. An example was our Hawaii trip where cost-wise it was comparable to other places, but we got 2% back through our membership and 3% back through the free Costco Visa card. They also gave us a $100 gift card for booking and we had $50 resort credit each day we were there.
We booked a trip this year through a different travel place that Costco didn't offer, but we still booked our rental car through Costco. We saved about $200 over the course of the week, plus the 2 and 3% back as mentioned above, free second driver, etc.
Groceries - We buy most of our groceries from Trader Joes since its just the two of us, but we plan things out well to get the most of Costco. Chicken breast at 2.99/lb for a pack of 6. I put two in the fridge and freeze the rest, pull out the night before I need it. Big bag of rice lasts us like 2 months, same with quinoa and pasta. Coffee I can buy a 2-3 lb bag for less than a 1 lb bag elsewhere and not lose quality. Each week we pick a few things that we want to eat that are pre-done and try those. Gigantic box of egg whites that's good for a month costs me $10.
Produce - Tried to do it but we wasted a lot. Now I buy a pack of bananas, blackberries, raspberries and strawberries each week plus a pineapple. Two pounds of berries costs the same as 1lb from TJ.
Snacks - big bags of skinny pop, granola bars, protein powder/drinks, etc. Only buy shelf stable items and I have a ton of storage space so it lasts forever.
Dry Goods - Bath tissue, paper towels, lawn bags, trash bags, etc. Lasts forever and only need to buy very rarely. Certain items like cat litter normally priced at $16 for a 42lb container goes on sale for $12.50 and I buy 3-4 at a time and it lasts until the next sale. Over the course of the year it saves me over $60 vs buying from the pet store.
Car Buying - I saved $1500 on my car when i bought it in 2019 for being an Executive member. Costco pre-negotiated the price so I just picked what I wanted and didn't have to haggle at the dealership.
Random Bigger Purchases - We try not to impulse buy and we do a good job overall. We plan out our big purchases and try to save money where we can. The TV we wanted we found at my store that was an open box return and we got it for $300 (normally $500) and has been great for almost 5 years. The Christmas tree we wanted went on sale and we got it for $400 vs a similar product for $700 elsewhere. Our pressure washer saved us about $100 last year. I waited for the Weed-n-Feed gigantic bag to go on sale (normally $90, but we got $50 off when I also bought some plant food for $20 which I also needed) and I know I can use that bag for at least 2 summers. We bought a bunch of Arborvite trees for our yard at $32.99 each that were at least $50 and closer to $75 most other places. I saved about $2000 on my wife's engagement ring!
We're not perfect and definitely buy stuff we wouldn't normally, but we practice self control for the most part. There's a ton of other stuff I didn't mention that saves us money like optical center, pharmacy, etc.
All in all, we get back usually about $150 from the membership 2% and around $500-700 from the credit card. So if we buy some stuff we otherwise wouldn't, I'm not that worried about it.