Where they go depends on the culmination of many lifetime beliefs. He may have had a belief in past lifetimes of an existence beyond death.
In some instances those who don't believe in an afterlife often go to a void. No pain involved. And there are volunteers who will occasionally come from the light to try to convince them that there is more. The non-believer will stay where he believes as long as he needs to before changing his perspective.
If you hit rock bottom, start picking up rocks.
You know nothing, Jon Snow.
Ygritte from Game of Thrones.