Complex Annuity Calculator is being weird

[bgreeson]

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.

[Richard Buttrey]

Hi,

Can you explain what you mean by 'significantly short', and 'usable values' and what result you would expect to see and where?

I suspect the answer is going to be associated with the level of decimal precision at the margins when you get into several decimal places.

[bgreeson]

See the attached photo for outputs of Example 2 Inputs. In my inputs, I essentially said that I want a pay out period of 100 years with a delay of 5 years at the beginning.

As none of the RMDs are above the scheduled payout, I do not understand why my calculator does not take the calculations out to Period 105. Instead, it stops at Period 68, thus I am short 37 periods short, or "significantly short". Sorry if I caused confusion earlier.

[Richard Buttrey]

As none of the RMDs are above the scheduled payout, .

But is that true? post 2082 (row 91) there is no balance on which to earn interest or make a withdrawal. Hence any RMD would be above the zero balance.

[bgreeson]

The picture failed to attach. I'll try again here.
https://www.dropbox.com/s/mynd6d7c8c...utput.JPG?dl=0

[bgreeson]

Sorry for the delay, life has been pretty busy.

Regardless of the RMD (you can "turn that off" by Selecting #NA on the "Who's IRA is this?" input), there is still something weird going on when you get to the extremes of investment inputs. I want to figure out why, and if it's an error on my side, fix it, if it's an excel issue, then I want to put some limits in place.