He wanted to be Pope. He was supposed to be the cardinal who led the selection process when John Paul II died, but wound up as as the Pope (sort of like Cheney picking himself as Bush's VP).

I think he is resigning because he wants someone powerful to lead the church in his direction, but he no longer has the strength to do so.