It's as tender as you want to make it. And the degree of tenderness goes way up if you can find some with good marbling. Most of the tri tips I see in my local stores are select grade, and I'd guess low select grade by the lack of marbling. I look for the ones that have more lines of fat in them, and never have a problem with tenderness.
Another thing that affects it a lot is the temp it's cooked to. Med rare is wonderful, med is barely edible to me. This is one cut that changes in texture and flavor a lot if overcooked. And of course you really need to pay attention to the grain when carving. Across the grain and on a bias will get you the best results every time.
I personally hate donut syndrome on a roast. This is when a roast is cooked at a high temp and the outside is well done, then there is a small center section of pink. This is why I like to to this type of cut with a reverse sear now. Low n slow till internal of 110ish, so the meat is pink from edge to edge, then toss it on a hot fire to quickly sear the outside. Remove no later than 130 (I prefer 125). Then rest before carving. This will get you great results every time.
Modded Brinkmann Gourmet. Basic UDS. Rescued 22.5" OTS. SJS Mini-WSM. Stubbs. B&B Oak Lump.
Cannondale 2009 F8 (for burning off all the great BBQ)