The answer, as others have alluded to in the past, is "it depends." You've got to do some trial and error with your squad to see what works. For some squads, you're going to need to be passionate all the time. For mine, passionate talks make about 1/2 of my team switch off or play very poorly.
For them, calm is the way to go unless I've gotten a big victory. But generally calm for me is the way to go. I used to be very good with team talks, but I'm not entirely sure if the old rules still apply. It used to be that the "do it for the fans" talk would be neautral and not screw things up. But nowadays I tend to use "cam: impress me" or some equivalent if there is no clear cut "1-4/4-1" favorite. If I'm the favorite, I use "calm: good luck" unless the special "no pressure, no one expects us to win" talk comes up. Either are fine, you want your players loose or relaxed. And it really springboards the team towards an upset.
Generally, the reactions you want to see (and please correct me if your experience is different) motivated = fired up > delighted >>> happy. You tend to want to avoid "happy" unless it's at the end of the game because players who are happy tend to become complacent. The same applies to "relaxed" unless the player is looking nervous. How do you stop a player from being nervous?
Once again it's trial and error. Sometimes "No pressure" works. Sometimes "you have faith" works. Sometimes you've just got to yell at them to snap out of it -- but it will require individual player talks 90% of the time. Had a player play really well last game? Don't forget to give the individual talk asking for more of the same. A player struggle last time? Expect a performance generally works, although it can stress out some younger players or players who have not been with the team that long. Once again, the tone will depend on the player.
Half time talks: If the game is going well, calm encouragement is generally enough -- but I get wildly different results between "room for improvement" and "looking good out there." I save the former for ties or beating a far worse opponent by only one goal. Two goals or above, pleased is good because it makes my team delighted instead of happy. Losing? Don't be afraid to get angry if you are strongly favored over the other team, but if you're at home being assertive should be enough. If you're on the road and you're missing a lot of on target shots, sympathy might be warranted, but otherwise I tend to stay away from it. The most effective talk is the same from the last two years though: any equivalent of "I want to see more from you."
End game talks: Your goal here is only to boost morale. Once a player is delighted there's not much more you can do, although individual talks are great for making you one of a player's favored personnel. Don't be afraid to blow up at the team if you lose 0-3 to a team destined for relegation or well below your position. You'll get a lot of fired up/motivated. Save sympathy for road game/close losses/late ties. Be tougher on your team in home games. Be tougher on your team when you're favored. If you're not sure, stick with "well done" if you win and "sympathize" if you lose with individual disappointment talks for players who perform poorly.
And if you're having a bad run of games, do not forget about individual player talks and team meetings. Those can make a difference as well. But team talks are the difference between having a good squad and being able to shoot a less talented squad through the table straight to promotion after being preseason relegation candidates.