The award of Knight Bachelor – conferred on him in the birthday honours list in 2004 for "services to banking" following a nomination by Jack McConnell's Scottish Executive – was "cancelled and annulled" by the monarch.
The Cabinet Office said Mr Goodwin's retention of his honour was unsustainable given his failed management at the Royal Bank of Scotland, which led it to the brink of collapse and resulted in a £45.5 billion taxpayer bail-out, the biggest in history.
Politicians led by the Prime Minister David Cameron insisted it was the right decision but others complained it had the "faint whiff of the lynch mob" about it.
Sir Jackie Stewart, the former motor racing champion who is a friend of Mr Goodwin, questioned the process, saying it was poor for the constitution and set a "very dangerous" precedent. Normally, honours are only revoked because of traitorous behaviour or a criminal conviction.
It almost certainly means Mr Goodwin will lose another honour – the fellowship of the Royal Society of Edinburgh, one of Scotland's most august institutions. Its governing body is to meet later this month to consider stripping him of his membership in light of his failed management at RBS.
It is thought Mr Goodwin was told about the removal of his knighthood in a pre-emptive phonecall. At around 4pm, an hour before the official announcement, the 53-year-old former banker was seen leaving his plush Edinburgh home and getting into a Jaguar car on his own. Last night, there was no response at his house.
In its statement, the Cabinet Office revealed the Forfeiture Committee, which is made up of Whitehall mandarins, met last week and concluded that Mr Goodwin had brought the honours system into disrepute.
"The scale and severity of the impact of his actions as CEO of RBS made this an exceptional case," it said.
The statement explained that in 2008 – following RBS's misjudged takeover of the Dutch bank ABN Amro – the Government had to provide £20bn of taxpayers' money to keep the institution afloat and "prevent the collapse of confidence in the British banking and payments system". Subsequently, the public bail-out reached a record £45.5bn.
It noted that the Financial Services Authority and the Commons Treasury Committee were clear the failure of RBS played an important role in the financial crisis of 2008/9, which helped trigger Britain's worst recession since the Second World War. It described Mr Goodwin as the "dominant decision-maker at RBS at the time".
The Cabinet Office added that, in reaching its recommendation, the committee recognised the widespread concern about Mr Goodwin's decisions at RBS meant retention of a knighthood for services to banking "could not be sustained". The Queen was passed the decision by Mr Cameron.
Last night, there was cross-party support for the move, which comes amid a political debate about so-called "moral capitalism" and the ongoing row over bankers' bonuses.
The Prime Minister said: "The proper process has been followed and we have ended up with the right decision."
Labour leader Ed Miliband agreed, but added: "We need to change the bonus culture and we need to change the rules so we see real responsibility across the board."
Alex Salmond also made clear the removal of Mr Goodwin's knighthood was the "correct decision" and that it should lead to "much-needed restraint over pay and bonuses on financial institutions within the public sector".
At the time of the ABN Amro takeover, the First Minister had written to Mr Goodwin to show his support for the move.
The SNP politician famously signed his note to the banker "Yours for Scotland", adding that he "would like to offer any assistance my office can provide".
Finance Secretary John Swinney had also hailed the deal at the time as "an enormous achievement for RBS" that helped make Scotland seem "an attractive place to do business".
However, Alistair Darling, the Chancellor at the time of the bank's near-collapse in 2008, told The Herald that while Mr Goodwin had certainly made mistakes, he felt "uneasy about the way the whole thing has been approached".
He said: "Up until now people have lost their knighthoods because they were convicted of serious crimes like treason. Whatever mistakes Fred Goodwin has made he has not been convicted of any crime.
"It is all extremely odd and, for a country that prides ourselves on due process, the procedure is very important."
Lord Digby Jones, former CBI director general, noted: "There is the faint whiff of the lynch mob on the village green about this but that isn't to say the end result isn't what is right."