This not only depends on what type of exit tip is used, but also on the internal construction features (like baffles or perforated plates) of the muffler.
The exhaust muffles are broadly categorized as Absorptive or Reflective type on the basis of these internal acoustics.
Talking specifically about the exhaust tip, by changing the shape or flow area of the tip, the pattern of sound (or pressure) waves associated with the exhaust gas flow changes.
However, the change will not be as big as when once replaces a stock muffler with free-flow headers, but you will have notable changes only when the length/ geometry/ exit area of tip are significantly modified.
But the exhaust pipe is more decorative, and the effect of changing the sound is not obvious.
Clearly the shape of the exhaust tip couples the pressure pulses in the exhaust to the ambient gases. This has a effect on the sound, just like the horn end of a musical instrument does.
However, the magnitude of the exhaust pulses is much more severe than the pressure pulses in a musical instrument. Because of the frequencies and volume of air, the size of the tip to cause a significant effect is quite large.
Asserting the tips have no effect is simply wrong; however, they do not generally have a significant or real perceived effect.