One of my English teachers once told the class, primly, “Vulgarity has no use in language.”
I said, “The hell it doesn’t.”
One of only two times I ever saw a teacher get physically mad, like wanted to hit a student mad. She was seriously pissed. Mostly because I was fucking right.
Vulgarity has value in prose. Whether it turns the audience off or not is the same sort of generally unanswerable question as “will this story work for audiences.” Can’t know until audiences read the story. There are characters who are exceptionally vulgar with language, and others that are Puritan in their avoidance of it. Same goes for audiences; some won’t read anything with vulgarity, others like it because that’s how people talk. Same as some people only read romance, or won’t read scifi, or only read mystery.
The old line about “shit is the number one thing people say when in an accident?” Yeah, that’s not fiction; people do say shit when shit happens. An hour on Youtube, looking at car accidents or whatever where something shocking occurred in front of a camera bears it out.
Some people, very well spoken people, use vulgarity casually. Because it’s who they are. Cursing doesn’t mean someone isn’t intelligent or articulate. Sometimes the most succinct way to express something is to delve into Carlin’s famous 7 Words. Kevin Smith is an easy example of this. He’s very intelligent and can be quite formally eloquent, but when he’s talking casually without an enforced filter he curses quite regularly. It has no impact on the value or utility of what he’s saying.
Characters are people. Individuals. Unique. If they should be cursing, but don’t, that’s a problem.
Avoiding vulgarity can often be much more noticeable than if the dialogue simply went blue. Easy example: soldiers under fire. Who, honestly, believes someone in a war who’s being shot at, whose buddies are dying around them, will care about making sure their language is PG rated? We see this a lot on TV where shows have to work hard to work around the FCC’s restrictions (American example).
Battlestar Galactica and Sons of Anarchy were two good examples of how the dialogue got conspicuous in its needing to avoid cursing. Some of the ways they worked around it were more successful than others, but it had an impact (for better or worse) on the writing. Some situations in stories become a problem for the audience when the characters aren’t cursing.
Words are tools. Use them.