• 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

GraH - Guido

Well-Known Member
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

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.
 

GraH - Guido

Well-Known Member
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

GraH - Guido

Well-Known Member
Hi Marc, then my formula won't do. As I was afraid, I somewhat misunderstood the point. I think it is more clear now.
 

Peter Bartholomew

Well-Known Member
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.
 

Peter Bartholomew

Well-Known Member
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

Top