Neverwinter Nights and its expansion packs not only offer solid singleplayer campaigns, excellent gameplay and a quality co-op experience. They're also supported by an enormous modding community that has created literally thousands of mods, ranging from full new modules to just new characters, that will guarantee many hundreds of hours of fun.
It'll take many dozens of hours to finish the official campaigns, which you'll likely want to replay a couple of times as differing characters. There are a lot of classes and alignments to choose from, as is standard in D&D. This ensures high replayability in spite of the game environments and progression being entirely linear.
The story is solid, but formulaic, fantasy fare. If you're an avid reader of fantasy, then you might be a little disappointed. It never rises beyond the level of mediocre fantasy novels, unlike more recent RPGs such as Dragon Age: Origins, The Witcher or even Neverwinter Nights 2. That being said, it still stands head and shoulders above the likes of Fable or Oblivion in this regard.
On the gameplay front it is a very interesting game. It plays much like Knights of the Old Republic, with a party at your disposal that you do not fully control. In the expansion you can change their equipment and talk to them to adjust their tactics, but that's the extent of your control over them. NWN doesn't offer much in the way of voicework either, but there is a lot of talking to be done so you'll be reading a lot of text. This sound archaic in comparison to modern RPGs, but a few hours in you get used to it and the presentation is such that you're not squinting the entire time trying to read lines.
Multiplayer functionality is included, which allows both co-op in the singleplayer campaign as well as user-made modules. Several creative individuals have even managed to create their own persistent worlds, which are basically free-to-play mini-MMORPGs. There are also countless of excellent singleplayer modules to download, many of which are considered to be superior to the official singleplayer campaign.
The only possible way you could go wrong with this purchase is if you're just not the kind of gamer that enjoys a classic fantasy RPG. Much of your time will be spent talking to NPCs and crawling through dungeons with typical BioWare/Obsidian-style RPG gameplay. It's not a button-mashing action-RPG (Diablo), nor a turn-based flashy game with little substance (Final Fantasy) and you're not a free man roaming a world with next-to-no direction (Oblivion).
It's top-down, linear, semi-real-time RPG gameplay with a party. If you've never experienced that sort of game, then this is the perfect start. It's the most accessible game of its kind, with a decent singleplayer tale to get you started and countless mods to keep you going.
You should be paying extra for this game, it's worth it.