Complex Annuity Calculator is being weird

Hey guys,

To preface, you must understand finance to help with this.

My parents are about to retire so I volunteered to create a spreadsheet to help manage their finances. I googled and found a basic annuity "calculator" template and have since heavily modified it. You will have to look at the attached file in order to have a clue of what I'm about to ask.

To give a little background, I am trying to set up an annuity that has the ability to compound interest before starting payments. In addition, it must take into account an IRA's Required Minimum Distribution (google this if you are unfamiliar).


My question revolves around the "Payout (withdrawal)" column, the Annual Interest Rate, the Years to Pay Out.

Example 1 (see attached photo for inputs):
In this situation, and all "normal" situations, my calculator works fantastically, with no errors.

Example 2 (see attached photo for inputs):
For those unfamiliar with RMDs, they essentially require you to take a certain percentage of money out of your IRA every year. This percentage is based on your age. I have a chart that has these ages just to the right on Sheet1. My goal is to try to force the extremes of what this calculator can do so that I know its limits.

I have no idea why it just comes up significantly short in terms of my periods...Any help would be great!


In addition, sometimes at the end of the useable values, my conditional formatting to highlight if the RMD is greater than the pay out gets screwy and I don't know why.