I have written the following code in order to iterate a discount rate from a
present value and yet it fails to recognise the values in the Target,
Period_Range and Cashflow_Range. It works in another workbook but
unfortunately not the one in which I need it. Can someone please help?

Public Function Iterative_Solution(Target As Double, Guess1 As Double,
Guess2 As Double, Frequency As Double, Period_Range As Range, CashFlow_Range
As Range)

Cycle = 1
Limit = Period_Range.Rows.Count
DiscountFactor1 = 1
DiscountFactor2 = 1
Iteration_Target = Target * 10000
Freq = Frequency

'Initial Valuation at Guess1

For Each New_Period In Period_Range.Cells

DiscountRate1 = Guess1
DiscountRate2 = Guess2

CurrentPeriod = Period_Range.Cells(Cycle).Value

If CurrentPeriod 0.001 Then

DiscountFactor1 = DiscountFactor1 / ((1 + (DiscountRate1 / Freq)) ^
(CurrentPeriod))
DiscountFactor2 = DiscountFactor2 / ((1 + (DiscountRate2 / Freq)) ^
(CurrentPeriod))

Cashflow = CashFlow_Range.Cells(Cycle).Value

DCF1 = Cashflow * DiscountFactor1 + DCF1
DCF2 = Cashflow * DiscountFactor2 + DCF2

Else

End If

Cycle = 1 + Cycle

Next New_Period

Error1 = DCF1 - Iteration_Target
Error2 = DCF2 - Iteration_Target

DiscountRateLast = DiscountRate2
ErrorLast = Error2
DiscountRateThis = DiscountRate2 - ((Error2 * (DiscountRate2 -
DiscountRate1) / (Error2 - Error1)))

'Begin the Iterations runining through five times to hone in on correct answer

For Iteration = 1 To 5

Cycle = 1
DiscountFactor = 1
DCF = 0

For Each New_Period In Period_Range.Cells

CurrentPeriod = Period_Range.Cells(Cycle).Value

If CurrentPeriod 0.001 Then

DiscountFactor = DiscountFactor / ((1 + (DiscountRateThis / Freq)) ^
(CurrentPeriod))

Cashflow = CashFlow_Range.Cells(Cycle).Value

DCF = Cashflow * DiscountFactor + DCF

Else

End If

Cycle = 1 + Cycle

Next New_Period

ErrorThis = DCF - Iteration_Target

If WorksheetFunction.Max(ErrorThis, -ErrorThis) < 0.000001 Then

Exit For

Else

End If

DiscountRateTemp = DiscountRateThis

DiscountRateThis = DiscountRateThis - ((ErrorThis * (DiscountRateThis -
DiscountRateLast) / (ErrorThis - ErrorLast)))

ErrorLast = ErrorThis

DiscountRateLast = DiscountRateTemp

Next Iteration

Iterative_Solution = DiscountRateThis

End Function

Thank you
--
Matt Roberts

--
Matt Roberts

Is the function in a General module rather than a sheet module.

If the function works in one workbook and it is in the proper location and
you pass valid arguments to it, then from a argument standpoint, you
shouldn't have a problem. If you are getting errors in the function
because you are passing values not anticipated, I can't comment on that.

--
Regards,
Tom Ogilvy


"Matt Roberts" wrote in message
...
I have written the following code in order to iterate a discount rate from
a
present value and yet it fails to recognise the values in the Target,
Period_Range and Cashflow_Range. It works in another workbook but
unfortunately not the one in which I need it. Can someone please help?

Public Function Iterative_Solution(Target As Double, Guess1 As Double,
Guess2 As Double, Frequency As Double, Period_Range As Range,
CashFlow_Range
As Range)

Cycle = 1
Limit = Period_Range.Rows.Count
DiscountFactor1 = 1
DiscountFactor2 = 1
Iteration_Target = Target * 10000
Freq = Frequency

'Initial Valuation at Guess1

For Each New_Period In Period_Range.Cells

DiscountRate1 = Guess1
DiscountRate2 = Guess2

CurrentPeriod = Period_Range.Cells(Cycle).Value

If CurrentPeriod 0.001 Then

DiscountFactor1 = DiscountFactor1 / ((1 + (DiscountRate1 / Freq)) ^
(CurrentPeriod))
DiscountFactor2 = DiscountFactor2 / ((1 + (DiscountRate2 / Freq)) ^
(CurrentPeriod))

Cashflow = CashFlow_Range.Cells(Cycle).Value

DCF1 = Cashflow * DiscountFactor1 + DCF1
DCF2 = Cashflow * DiscountFactor2 + DCF2

Else

End If

Cycle = 1 + Cycle

Next New_Period

Error1 = DCF1 - Iteration_Target
Error2 = DCF2 - Iteration_Target

DiscountRateLast = DiscountRate2
ErrorLast = Error2
DiscountRateThis = DiscountRate2 - ((Error2 * (DiscountRate2 -
DiscountRate1) / (Error2 - Error1)))

'Begin the Iterations runining through five times to hone in on correct
answer

For Iteration = 1 To 5

Cycle = 1
DiscountFactor = 1
DCF = 0

For Each New_Period In Period_Range.Cells

CurrentPeriod = Period_Range.Cells(Cycle).Value

If CurrentPeriod 0.001 Then

DiscountFactor = DiscountFactor / ((1 + (DiscountRateThis / Freq)) ^
(CurrentPeriod))

Cashflow = CashFlow_Range.Cells(Cycle).Value

DCF = Cashflow * DiscountFactor + DCF

Else

End If

Cycle = 1 + Cycle

Next New_Period

ErrorThis = DCF - Iteration_Target

If WorksheetFunction.Max(ErrorThis, -ErrorThis) < 0.000001 Then

Exit For

Else

End If

DiscountRateTemp = DiscountRateThis

DiscountRateThis = DiscountRateThis - ((ErrorThis * (DiscountRateThis -
DiscountRateLast) / (ErrorThis - ErrorLast)))

ErrorLast = ErrorThis

DiscountRateLast = DiscountRateTemp

Next Iteration

Iterative_Solution = DiscountRateThis

End Function

Thank you
--
Matt Roberts

--
Matt Roberts