If you look on localllama you'll see most of the people there are really just trying to do NSFW or other questionable or unethical things with it.
The stuff you can run on reasonable home hardware (e.g. a single GPU) isn't going to blow your mind. You can get pretty close to GPT3.5, but it'll feel dated and clunky compared to what you're used to.
Unless you have already spent big $$ on a GPU for gaming, I really don't think buying GPUs for home makes sense, considering the hardware and running costs, when you can go to a site like vast.ai and borrow one for an insanely cheap amount to try it out. You'll probably get bored and be glad you didn't spend your kids' college fund on a rack of H100s.
There's some other reasons to run local LLMs. If it's on my PC, I can preload the context with, say, information about all the members of my family. Their birthdays, hobbies, favorite things. I can load in my schedule, businesses I frequent. I can connect it to local databases on my machine. All sorts of things that can make it a useful assistant, but that I would never upload into a cloud service.