There are many software skippers more knowledgeable here than me, but I'll give my two pennies. Keep in mind that the mods greatly affect torpedo performance and characteristics.
Mk 10: Light pre-war torpedo - Significantly worse characteristics than Mk 14 but more reliable. Can still do significant damage but requires you to get close to the enemy. I mix them with Mk 14 early in the war, when I want a few reliable shots.
Mk 14: your mainstay. A pain in the screws/rudder early in the war, but when it works it packs quite a punch.
Mk 23: Mk 14 w/o long range option. No advantage in game over Mk 14 - they could make the Mk 14 cost more than Mk 23 but who cares. Nice for the historical touch, but otherwise no difference. I like using them from time to time for the historical context and I hardly ever attack at long range anyway.
Mk 16: Anachronistic - it only became available after the war. High power, extreme range at high speed. This is what the Japanese torpedoes must have been like. Personally I am very hard pressed to be accurate enough to hit at long range. But if you hit, your target is history with that warhead.
Mk 18: Electric: Terribly slow short range torpedo with standard warhead. But you want this because it is wakeless and therefore the target will not evade. When available I mostly mix this one with Mk 14 as attack torpedoes. This one for concealed shots, Mk 14 for long range or fast targets. The combination will allow you attacking multiple targets hitting at the same time. One of the Old Guard posted a very clever video of how to do that. It is great to plan ahead, sneak up to the convoy and then see 3 Nagara Maru blow up simultaneously!!! What a sight.
Mk 27: Cutie: Defensive self-guiding handgrenade. Depending on your mod, this can be very weak (it was supposed to be anti-submarine torpedo for airplanes), but it is still useful as a defensive weapon against incoming DDs. Also it can run at very shallow depth allowing you to hit subchasers etc. in rough seas.
I usually equip half of my stern tubes with it for defense. Sometimes it will follow a fast enemy uselessly, sometimes it scares them off, sometimes it hits but then it usually (with TMO) does hardly any damage. That said I have had successes with them too: Once a DD tried to evade and the Cutie hit right next to the aft turret, blowing up the magazine and the waterbombs resulting in an instant destroyer kill (That felt good)!
The Germans had a huge variety with their torpedoes, but somehow I never got the hang of the German campaign, so I can't tell you anything about them. Maybe apart from the fact that I'd REALLY like to have the Japanese or German torpedoes rather than the American ones
I'm sure a lot of historical crews felt the same way.
Sorry for the long rambling. Hope there is some useful information in there for you.