Function NetPayment(currentYearCashPayment As Range, netTerms As
Integer, firstDeliveryDaysToGo As Integer, lastDeliveryDaystoGo As
Integer)

Application.Volatile

Dim previousYearCashPayment As Double
Dim nextYearCashPayment As Double
previousYearCashPayment = Cells(currentYearCashPayment.Row,
currentYearCashPayment.Column - 1)
nextYearCashPayment = Cells(currentYearCashPayment.Row,
currentYearCashPayment.Column + 1)
'Calculates the annual net cash payment
'Assumptions: 1) Cash flows are in chronological order - in a
continuous cell sequence

'Step one is to deduce whether it is a first year payment, _
a second year payment, a final year payment, or a final year + 1
payment

If currentYearCashPayment.Value = 0 And previousYearCashPayment < 0
Then 'This means current year is the year after last payment, _
and need to check to see if any payments fall into this range

If lastDeliveryDaystoGo < netTerms Then 'Means payments
received in year after last sale
NetPayment = previousYearCashPayment / (365 -
lastDeliveryDaystoGo) * (netTerms - lastDeliveryDaystoGo)
Else
NetPayment = 0
End If

ElseIf currentYearCashPayment.Value < 0 And nextYearCashPayment = 0
Then
'this is the last sales year

If lastDeliveryDaystoGo < netTerms Then 'Means payments received in
year after last sale
NetPayment = currentYearCashPayment -
(currentYearCashPayment / (365 - lastDeliveryDaystoGo) * (netTerms -
lastDeliveryDaystoGo)) + netTerms / 365 * previousYearCashPayment
Else
NetPayment = currentYearCashPayment + netTerms / 365 *
previousYearCashPayment
End If



ElseIf currentYearCashPayment.Value < 0 And previousYearCashPayment =
0 Then
' this is the first year of sales, check to see if any net
receivables are due in this year
NetPayment = currentYearCashPayment.Value *
(firstDeliveryDaysToGo - netTerms) / firstDeliveryDaysToGo

ElseIf currentYearCashPayment.Value < 0 And previousYearCashPayment <
0 Then
'this is a payment in the middle of the series
NetPayment = (currentYearCashPayment.Value * (365 - netTerms) /
365) + (netTerms / 365 * previousYearCashPayment)
Else
NetPayment = 0

End If

End Function


Jim Thomlinson wrote:
Post you code...
--
HTH...

Jim Thomlinson


" wrote:

I have a function which is having some issues recalculating on a cells
value change.

The function is dependent on a few variables, functionName(a,b,c)

When I first use the function, it works fine.

When I change variable a, the value of the function changes, but it
does not change to the proper value (it changes to some value that I
really do not know where it is coming from, still numeric). however, if
I press F9, then the formula will recalculate to the appropriate value.
I have tried adding application.Volatile to my function, but that
hasn't done anything to remedy the solution

Any Advice?

Thanks,

L