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
Bookmarks