Check what's actually on there *right now* before you commit to a subscription - seriously, just browse the current catalogue on the Game Pass site for 20 minutes and see if half the stuff appeals to you. That's the real test, not what anyone tells you is "worth it."
The thing is, the rotation does keep things fresh enough that you're not staring at the same lineup forever, which matters if you're the type to burn through games. But yeah, tastes are all over the place - some months the story games hit different, other months it feels thin. Since you're into single-player stuff specifically, you might get lucky with their narrative-focused titles, but you also might find yourself scrolling endlessly without anything that grabs you. The older multiplayer games you mention are less likely to be the draw though, since Game Pass skews toward newer releases and their own published stuff.
Financially? If you're someone who'd normally drop $30-40 on a couple games per year anyway, the subscription probably pays for itself pretty quickly. But if you're picky and only want specific games, you might save money just buying the ones you actually want outright. No shame in that either - it's not like the subscription is cheap if you're not using it.