There's no doubt in my mind the mods are an essential for long term enjoyment of Elder Scrolls game
Morrowind would be pretty well impossible today without mods, and I'd certainly lose interest in Oblivion very quickly without a slew of mods to spice things up. For a start, I wouldn't play Oblivion for more than five minutes without something to deal with the horrible level scaling. And that's just the first consideration - before we talk about graphical upgrades, weather mods, quests, gameplay changes, a new UI and so on.
As for whether it is 'worth it', then you would have to assess a) how serious you are about spending time testing, installing and generally fiddling around with mods because - believe me - the process is far from straight forward, and B) whether you can afford it. Without trying to sound flash, I'm in a position to be able to afford a decent gaming rig, and so spending £1000 on a PC is not going to compromise me financially. But not everyone could do this. But if it comes down to buying a gaming rig versus putting food on the table, well, that's a different matter....