Calculate the date out of funds given a known expenditure plan each month

[jdiegelmann]

I need help to calculate the date a project will run out of funds given a known expenditure plan for each month. We can assume that the daily expenditures are linear in any given month. Please see the attached example.

[ArdusPetus]

Here comes a solution, with a second worker row holding the date within the month.

HTH,
--
AP

[ThxAlot]

Hi, there

I did manage to construct a tricky formula addressing your issue.

First, out of habit and simplicity, I rearrange the dataset vertically in order to use table named "Project" for the formula.

Names are also come in handy for simplicity,

Please Login or Register  to view this content.

calculation of balance at the end of each month.

Please Login or Register  to view this content.

calculation of the last month with sufficient funds.

As you expect the day in the month in red, another calculation of date is based on "Sufficiency".

Please Login or Register  to view this content.

Because of fraction stemming from the calculation, the date is one day later than your mock-up answer.