• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Help with a date and number list

I need help with making a numbered list correlated to a date. Sample worksheet attached. THANKS!
 

Attachments

  • List Question.xlsx
    9 KB · Views: 8
Since I was on it while making diner :rolleyes:,

Alternative in same logic
=IF(AND(B9>=$E$6;B9<($E$6+$E$7));N(D8)+1;"")
or adapting Fluff's
=IF(OR(B9<$E$6;B9>$E$6+$E$7-1),"",N(E8)+1)
 
I thought that I could finish my worksheet from the formula that you provided, but no luck. I am trying to make withdrawals over a period of 10 years (in this case) and end in a zero balance. Would you be so kind as to help me one more time? THANKS
 

Attachments

  • IRA Withdrawal Worksheet.xlsx
    13.8 KB · Views: 4
Since I was on it while making diner :rolleyes:,

Alternative in same logic
=IF(AND(B9>=$E$6;B9<($E$6+$E$7));N(D8)+1;"")
or adapting Fluff's
=IF(OR(B9<$E$6;B9>$E$6+$E$7-1),"",N(E8)+1)

Thanks. If you have a chance, could you look at the additional question on this same subject? I put another worksheet in my reply.
 
Not sure I understand completely, but I get you want a countdown, correct?
Try =IF(OR(F9<1,F9>$J$5),0,$J$5-F9)
 
Not sure I understand completely, but I get you want a countdown, correct?
Try =IF(OR(F9<1,F9>$J$5),0,$J$5-F9)
I will play with this formula. However, to be clear, what I want to do is take a relatively even withdrawal for each of the 10 years. (It is a tax issue.) So, for whichever year the withdrawals start, I want even amounts until the balance is depleted at year 10 (in this case). My dilemma is that for each year, the balance earns interest so I have to account for that as well. THANKS!
 
I will play with this formula. However, to be clear, what I want to do is take a relatively even withdrawal for each of the 10 years. (It is a tax issue.) So, for whichever year the withdrawals start, I want even amounts until the balance is depleted at year 10 (in this case). My dilemma is that for each year, the balance earns interest so I have to account for that as well. THANKS!
 

Attachments

  • IRA Withdrawal Worksheet.xlsx
    13.8 KB · Views: 3
Hi Marc, then my formula won't do. As I was afraid, I somewhat misunderstood the point. I think it is more clear now.
 
Thanks, at least, for taking a look. It has me flummoxed as well. I suppose it is a VBA solution and that's not my skill!
I did figure it out by looking on the web for TVM calcs and the help of another friend. For future reference here is the solution. Again, thanks!
 

Attachments

  • IRA Withdrawal Worksheet.xlsx
    12.7 KB · Views: 4
This is an array formula version; the formula cells are highlighted in 'Barbie' pink (sorry about that)
67608
The catch is that I have used a function ACCUMULATE written by Charles Williams (MVP) in his FastExcel addin.
Since dynamic arrays treat accumulations (finance corkscrews) as circular references it is a bit of a problem to do this without the ACCUMULATE function. The basic approach is to use SUMIFS to total the amounts to date but allowance also has to be made for the return Rate.
 
I decided that it would be better to do without the ACCUMULATE function until such time as we can badger Microsoft into implementing it. It is a major impediment to the adoption of dynamic arrays that accumulation/corkscrews do not work without it. The workaround is to use repeated SUMIFS to aggregate each value from the start. Since I know next to nothing about finance, I worked mainly from first principles.

Because I couldn't meaningfully add the 'Withdrawals' across different years, I based each withdrawal upon its value at 2022, Then I compounded the interest from that date to bring the 2022 value up to year under consideration. For the moment, I haven't bothered about Opening Balances or Returns because they are not needed for the calculation
67619
 

Attachments

  • IRA Withdrawal Worksheet (PB) (SUMIFS).xlsx
    15.6 KB · Views: 3
Back
Top