This is a great question because it gets to the heart of the ambiguity of the story. When Brown leaves to go into the forest, he says "Poor little Faith!....What a wretch am I to leave her on such an errand." Because this is an allegory, where concrete items represent abstract concepts, we need to understand his farewell to his wife here also as his farewell to his "faith" in either God, the goodness of humanity, or other variations of faith that enable us to love each other in a community. As for despair, some aspects of Christianity (and puritanism is one) teach that despair is the greatest sin because it forsakes God, putting one's own sadness above his wisdom. I would argue, too, that Brown's despair signifies his loss of faith, which is the final comment Hawthorne makes about human nature and evil in this story.
Well, we have a problem, because I don't think he does abandon all faith at any point. He comes close, and he definitely feels despair, but I don't think he abandons all faith.
Here's why. This is his next to last speech in the story:
"Faith! Faith!" cried the husband, "look up to heaven, and resist the wicked one."
That's the moment of climax, and that's not a loss of faith.
If he does lose it, it would be after that. The last paragraphs show him shrinking from Faith (his wife, but also his faith), and so I'd say he feels doubt his entire life after his vision.
In my own opinion, I believe that through out the story Brown starts to give up on faith. There are many symbolic themes in this story involving religion, the inner self, and death. Each category expands itself in the story. Faith is a symbol in itself. Not only is Faith his wife, but faith is also his belief in good. His name in general young Goodman Brown is also a symbol. One is his innocence because of his youth, but also that he is good natured. Faith is the same. One symbol that must not be overlooked is Faiths pink ribbon. I quote, "My Faith is gone!" cried he, after one stupefied moment. "There is no good on earth; and sin is but a name." This quote shows that in giving up what he loved or desired most, he had lost all faith in himself, while caring only for his love. Thus, his dying hour is truly gloom, because he was never himself again. He had given up on himself, and realized he couldn't change the world and its dangers. He couldn't save his love, and he lost himself. It wouldn't matter what happened after this point, because he had changed. There was no going back to faith, because he had lost his will to resist urges, the devil, and his sanity. This is a sad but powerful realization, that dooms him.