Actually, where a soul goes upon death depends on the campaign in question. In Forgotten Realms, its all about what gods you serve. They've reserved the Wall of the Faithless for those who don't worship; even if you're 100% Lawful Good, if you don't worship the right gods, you won't go to Mount Celestia or either of its neighboring planes. You could be a LG paladin to a Chaotic god in FR.On death, he goes to the lower plane of Hell (or wherever) that corresponds to his actions and actual alignment, regardless of why he committed his evil deeds, or the justifications he used for them. He detects as evil, and so forth.
In Planescape, it was all about the alignment. In Forgotten Realms, its the gods. In Nentir Vale? Everyone went to the Shadowfell, then somewhere else. Dark Suns, which is cut off from the Outer Planes? Eberron which doesn't use the Great Wheel?
Eberron is especially curious, because it broke from the standard alignment restrictions in 3e, that we follow all now. There is no "plane of alignment" when you die in Eberron.
And there are no "detect evil" powers in the game anymore. Not in 5e. The closest is the pixie or sprite, who can read your personality summed up by an alignment, but that's not the same as "Detect Evil." After that, there's a handful of sentient magic items that care about meshing with your personality, using alignment as a short hand.
You really can't compare D&D to anything in real life, because of gods. In real life, generally speaking, many religions define their gods as the ultimate good. Following their teachings is Good. In D&D, we have Evil and Neutral gods. Follow an evil god, and you're rewarded upon death as a follower of a good god. So, if a follower of Bane dies in favor with their god, they are rewarded handsomely for being an evil jerk.
So, there's no shock, no surprise over being an "evil" person. I use quotes because what Evil means in D&D doesn't match to what it means in real life. Good in real life is subjective, defined by what a culture generally believes it to. In D&D, its an objective force with specific definitions. You not being good? Visit the local cleric who can run a divination spell and find out if you're bad or not. You a powerful villain who thinks he's doing the right thing, justifying their actions? When you get a direct proof in any number of ways that, no, you're not? Either you're literally insane and cannot see reality, or you deal with a god who supports what you want.
Bringing in questions of morally grey stuff in D&D doesn't work because, in the end, there's nothing to prove that Good is actually better than Evil. Certain races/cultures prefer certain philosophies or gods, but there's no proof one philosophy / god is actually better than other options. Indeed, Evil is often portrayed as a necessary part of the balance in D&D worlds. In a twisted way, the only real "good" in D&D is balancing Good and Evil.