The mods folder is indeed located under the user folder; the location is different in XP and Vista. Also, some of the folders are hidden by windows, so if you don't have the option on in explorer to show them, you won't find them. The location and directions for enabling hidden folders are as follows:

Vista:
C:\Users\your username\AppData\Local\Ironclad Games\Sins of a Solar Empire\

XP:
C:\Documents and Settings\your username\Local Settings\Application Data\Ironclad Games\Sins of a Solar Empire\

Please note that some of these directories are hidden by default. To unhide them, go to Control Panel -> Folder Options -> View and set yourself to view hidden files and folders.