TLDR: Great cards do not significantly drop in price when they rotate out of standard, as great cards are usually playable and viable in other formats.
When a card is great, and used across many formats, there is no reason for someone to reduce the card's price when they sell it. This includes when it rotates out.
Though, there are some things that DO cause card prices to drop. If you are looking to buy a card at a drop, try and look for one of the Three Rs.
Remember however, that predicting when these can happen is very hard to do. It is very similar to the stock market, for predicting both upwards and downwards price spikes.
Reprints, removals, and realizations. This is because a great card in standard can be used across multiple formats, and thus has a cross format value. So unless something impacts it greatly (like one of the Three Rs) it will not drop in price. That being said, if a card is only seeing play in Standard and Standard alone, then the Three Rs are pointless, and the card will probably drop when it leaves (but if that is the case, the card is probably not a great card). This drop however is not sudden, and is usually over the course of the end of the lifespan of the sets.
Reprints: When a card is reprinted, no matter how good it is, it will go down in value. This is because there are now more of the card.
Even if it is still a valuable card, there are more of it, and thus it is
less valuable.
On the other hand, a card that has been confirmed that it will never be printed again will go up in price, such cards like Black Lotus or Bayou. This applies to very old cards most of the time, but some rare versions of cards have their cost raise in a similar way (such as Kaladesh Masterpieces or Zendikar Expeditions). This may change if the lottery cards get reprinted eventually.
Example: Seedborn Muse is a card that has been worth a lot in recent time. When it was released, it was not worth much, but as time passed, it went from a card worth ~2.5 USD up to a card worth ~30 USD. But, it was reprinted as of recent in the set Battlebond. Since then, it's price has dropped excessively, going down towards ~13 USD (Specifically the Tenth Edition one, the Battlebond one is worth even less).
Removals: When a card gets removed from a format (through a banning), it instantly loses a lot of value it has. While this could be looked at similarly
to a card rotating out, the shock of an important card getting banned
has people rushing to sell it. This causes the price to drop
as a lot of them are pushed into the market at once.
Example: Emrakul, the Promised End was an extraordinarily powerful card in standard. But, come January 2017, it was hit with the ban hammer, and was removed from standard. It dropped by around 50%, going from ~20 USD to ~10 USD, and stayed rather low for a while. (Recently, it has began to climb back up however, but this is assumed to be because another format has began to use this version of Emrakul).
Another example: Smuggler's Copter was a card that was seeing an absurdly high amount of play in Standard. Along with Emrakul, the Promised End, it got banned in January of 2017 from Standard. Before the ban, it's price had been steadily declining from ~20 USD to around ~10 USD, but when the ban hit, it began to rapidly drop in price. Within a week, it dropped down to ~6 USD. From here, it slowly declined to ~2 USD, where it has stabilized and been for a long time.
The main reason for this type of downwards price spike is people rushing to sell their copies before the price reaches it's lower stabilized price.
Realizations: Very often, during spoiler season or when new cards are released, cards will get over hyped and over priced. Eventually,
they will go down once people realize they are not as good as they
thought.
This can work in the other way as well, where a card that was under the radar gets realized for hidden potential. Examples being cards that fit in a combo or cards that counter a popular deck type nicely.
Example: Karn, Scion of Urza, was very expensive shortly after his release (reaching around ~60 USD at his peak). But, once players began to realize he was not as good as people made him out to be, his price slowly fell back down to his original price (~40 USD), and eventually even lower (~30 USD).
As you can see, those are some major ways prices drop. If a card is good outside of Standard (such as EDH), do not expect it's price to drop until it receives a reprint, a banning (from a format it sees play in), or it is replace/realized to be not as good as it is.
An example of a good card not dropping after it rotated out is Thought-Knot Seer. Once the hype for it died down, it stabilized at ~6 USD, and is even climbing today. This climb has been happening even after it was rotated out of Standard (as it became a staple in a Modern deck).
Conclusion: If a card in standard is truly great, it will not lower significantly in price when it rotates out of a format. This is because a truly great card will see play across multiple formats, and thus keeps some level of value across them. It takes something much more ground shaking to have a card spike downwards in price, such as a reprint, a removal, or a realization.
A note: the opposite is true for upwards price spikes, as it seems those happen a lot more often in comparison. People are faster to sell high than to sell low, as it is logical when running a card selling business/selling cards for profit.
