Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function Not Recognising Values in Populated Ranges
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel still not recognising equal values | Excel Worksheet Functions | |||
HELP! - Printing a sheet with values populated from dropdown box | Excel Programming | |||
Workbook not recognising named ranges | Excel Programming | |||
Recognising cell values | Excel Programming | |||
Copy Values to Below last Populated Cell Q | Excel Programming |