Here are two Functions that will work for simple cashflows.

The first one is simple, the second takes into consideration the time
value of money.

HTHs

Jon

'---------------------------------------------------------------------------------------
' Procedure : FAME_Payback
' DateTime : 08/04/2002 22:22
' Author : Jon 'Crash' Eggett
' Purpose :
' Notes :
'---------------------------------------------------------------------------------------
'
Function FAME_Payback(Cashflows)
'Calculate the payback period
'Note that the first cash flow must be negative
Dim PB As Single, UB As Integer
UB = Cashflows.Count 'Upper bound (i.e., number of cash flows)
CumSum = 0 'Cumulative sum of cash flows, stop when
greaterthan 0
i = 0 'Counter variable
Do While CumSum <= 0 And i < UB
i = i + 1
CumSum = CumSum + Cashflows(i)
Loop
If CumSum = 0 Then
CumSum = CumSum - Cashflows(i)
PB = (i - 2) - CumSum / Cashflows(i)
FAME_Payback = PB
Else: FAME_Payback = "Payback Life" 'Report error
End If
End Function
'---------------------------------------------------------------------------------------
' Procedure : NEW_Payback
' DateTime : 08/04/2002 22:12
' Author : Jon 'Crash' Eggett
' Purpose :
' Notes : Payback periods calculated with PV of Cashflows
'---------------------------------------------------------------------------------------
'
Function NEW_Payback(Cashflows, Rate)
'Calculate the payback period if Rate = 0, discounted payback if Rate < 0
'Note that the first cash flow must be negative
Dim PB As Single, UB As Integer
UB = Cashflows.Count 'Upper bound (i.e., number of cash flows)
CumSum = 0 'Cumulative sum of cash flows, stop when
greater than 0
i = 0
If Rate = 1 Then Rate = Rate / 100
Do While CumSum <= 0 And i < UB
i = i + 1
CumSum = CumSum + Cashflows(i) / (1 + Rate) ^ (i - 1)
Loop
If CumSum = 0 Then
CumSum = CumSum - Cashflows(i) / (1 + Rate) ^ (i - 1)
PB = (i - 2) - CumSum / (Cashflows(i) / (1 + Rate) ^ (i - 1))
NEW_Payback = PB
Else: NEW_Payback = "Payback Life" 'Report error
End If
End Function

Chad wrote:

Hey all,

Does anyone have a macro for calculating payback period??
I need some help thanks



--
42°57N 81°16W