Prev: Grouping Years in a Pivot table
Next: Make a Template Available to Multiple Users
From: George on 26 Jan 2010 14:59
I am trying to determine a formula to work out an estimated projected date
in the future that an account will reach a certain balance. Because the
amounts credited are random in date and amount, I need to simplify things by
assuming that the next amount credited will be an average (mean) of all
credits to the account spread over the time since the first payment and will
be credited daily.

This will change over time, depending on the amount and frequency of
payment. In column A I have dates, in B a balance, which is calculated as
the sum of all credits so far and column C holds payments to the account.

Example

1 Jan 10------0------0
2 Jan 10------5------5
3 Jan 10------5------0
4 Jan 10-----15-----10
5 Jan 10-----15------0
6 Jan 10-----22------7
7 Jan 10-----28------6
......

Column D will hold the calculation for the projection, this will be graphed
and the resulting line will be used to calculate the projected date when it
intersects with the projected amount.

Currently my formula calculates the average, but as it hasn't (yet) got
figures for future dates, since the payments are irregular, the average
decreases going down the column.

Any ideas?




 | 
Pages: 1
Prev: Grouping Years in a Pivot table
Next: Make a Template Available to Multiple Users