Having bought SWMBO a camcorder for Xmas, I've spent a bit of time looking at all the options.
I started out with Windows Movie Maker (free download if you have XP). It's very easy to use and you can easily add titles, credits, music and effects. There are a couple of downsides. Firstly, it won't burn a DVD. You would have to obtain some DVD-authoring software to supplement. Secondly, it won't do 'picture-in-picture' effects. And finally, I found that the picture quality wasn't as good as on the original tape.
The favourite proprietory software for home-users appears to be Pinnacle Studio Plus v9. That seems to do most things well except I read just too many reports about system crashes.
Adobe Elements appears to be another pretty useful software package - about £70 from PC World. I couldn't find any bad reports about it, but it doesn't seem to be a big seller.
I settled on Sony Vegas 5 - a semi-pro package. It's a bit hard to learn when you first start - the manual alone is nearly 400 pages! But now I've got into the swing of it, it's not too difficult and it is massively powerful (although I've not yet mastered 5.1 surround sound!). The picture quality is every bit as good as the original tape too.
Greg