Hi,
I'm trying to come up with a formula to evenly distribute funds across a number of years based on if there is an end date or not. Each line has a different number of years for distribution. Apologies if I'm not explaining it very well. Example, I have someone who has paid say $1,000. I have a start date of 01/01/2018 and no end date, the maximum number of years that this needs to be distributed is say 4 (this will vary). The equal distribution would be $250 per year. I can get that bit ok. My problem is when there is an end date. If I have distributed the amount for say two years, I need the remaining balance to be brought back. I can also get this but can't put them together. Attached is an example. The top line has a formula which is working fine, however when I bring it down it's not working when the end year is null. I've hard coded what the answer should be. Any help would be appreciated.
I'm trying to come up with a formula to evenly distribute funds across a number of years based on if there is an end date or not. Each line has a different number of years for distribution. Apologies if I'm not explaining it very well. Example, I have someone who has paid say $1,000. I have a start date of 01/01/2018 and no end date, the maximum number of years that this needs to be distributed is say 4 (this will vary). The equal distribution would be $250 per year. I can get that bit ok. My problem is when there is an end date. If I have distributed the amount for say two years, I need the remaining balance to be brought back. I can also get this but can't put them together. Attached is an example. The top line has a formula which is working fine, however when I bring it down it's not working when the end year is null. I've hard coded what the answer should be. Any help would be appreciated.