Prospero reaches this conclusion in Act V scene 1, but only as all of his plans and machinations are about to come to fruition. He gives a speech where he recognises all of the amazing, wondrous and supernatural activities he has managed to achieve through his magic power before identifying that, because of his plans and his desire to return to his former home and take up his position in society again, he must necessarily sacrifice his magic:
...graves at my command
Have waked their sleepers, oped, and let 'em forth
By my so potent art. But this rough magic
I here abjure.
This is why Prospero determines to break his staff and "drown" his book. He implicitly recognises that it was his magical study that led to his exile in the first place. In the play, his books and staff become symbols of his desire to retreat from the world entirely. It was the zeal with which he devoted himself to unlocking magical arts that allowed him to be deposed in the first place by his brother. Now he wishes Miranda to return to society, he recognises that he cannot afford to isolate himself any further. Having brought about the reconciliation between himself and his brother and a romance between his daughter and Ferdinand, assuring her future, he must put aside his magic. The speech he makes does strongly suggest that this is something he does not do entirely willingly, however. Such might and magic would be hard to quit having savoured its full power.