The first time you put on the Fierce Deity Mask is probably the best moment in the series.
Allow the First 4 Figures, who made a statue of Fierce Deity Link, to explain: "Powerful and evil, could [the Fierce Deity] mask's dark powers be as bad as Majora?" When young Link dons the Fierce Deity Mask, he manages to harness its power without being corrupted and is transformed into Fierce Deity Link for the ultimate showdown with Majora!
It would be an ultimate showdown, except that with the Fierce Deity's Mask you can literally take out all 3 of Majora's forms in a little over a minute... which is exactly the symbolic point.
How easy it made the last battle made it even more awesome. You've worked so hard to get here, you've played these 3 days over and over, and you got all of the masks, and the game rewards you for it. You're so awesome not even the final boss is a challenge for you. It's a fantastically climactic fight because of it.
Game after game, the bosses have tossed Link around like a rag doll, making him suffer before the victory. But Fierce Deity Link doesn't just beat the bosses - he flat-out annihilates them. And Twinmold gets a CMOA in that he is not completely destroyed by the Fierce Deity Mask.
The Giant's Mask is no slouch either. Tiny kid Link turning into a 100-ft colossus and shattering house-sized stone columns with his sword?
In the Manga version of the game, the Happy Mask Salesman takes Majora's Mask after the battle, but looks up at Fierce Deity Link and is visibly terrified. Then Link scares him away with a slash of his sword! "Begone, trouble maker!" indeed.
The Big Bad's Moment of Awesome occurs right after Link plays the Oath to Order. The Four Giants have stopped the moon from falling, Skull Kid is beaten, and the world is safe. Or so you think, until Majora's Mask pulls itself off of Skull Kid, possesses the moon itself, and starts forcing it downward.
"Certainly he had far too many weaknesses to use my power."
The Oath to Order itself. The four giants, guardians of the land, slowly take their stances beneath the moon and stop the end of the world. This is made all the sweeter because throughout the game Link has been fighting on his own against insurmountable odds and now has the help of the giants to stop something even he can't beat
Fighting Twinmold with nothing but your sword. No giant's mask (or no magic meter left to operate it), no arrows, just your sword. It's possible but very time consuming and very painful for Link.
Five words from Cremia: "Boy, get out your bow!" Cue awesome mini-game.
The sheer difference between the first and last times you go up to the top of the clocktower. The first time, you're trapped in an unfamiliar form. You don't know what you can do, and so you're just desperately ascending because anything else is certain death. But at that final time, things are different. As you go up, you're hung all over with weapons, bombs, and resources. You're bearing dozens of magical masks and all the songs. And this time, you're going to stop that Moon once and for all.
Rolling in Goron form chasing after Goht. Completely unlike any Zelda boss battle before or since. It also helps that by killing Goht, you're avenging Darmani.
Five little words: "Dawn of a New Day".
Some Fridge Awesomeness: If the transformation masks are imbued with the souls of dead entities, then Majora must have kicked the ass of something pretty strong to make the Fierce Deity's Mask. And then the awesomness that this knowledge implies in turn: It's not the Fierce Deity's power that defeats Majora...it's the Fierce Deity's power amplified by the pure Badass-ness that Link has acquired during his quest!
That accomplished feeling after successfully protecting Romani's barn from Them is pretty awesome. Heck, the whole mission- riding around on horseback shooting aliens with arrows- speaks for itself.