Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA IRR and errors
I'm using IRR in a function, but sometimes IRR can't find a solution. I want to provide IRR a guess that, if IRR still doesn't find a solution, the guess will change and be provided to IRR again until IRR finds a solution or this is repeated a certain number of times. I think my error trapping isn't working properly, but I'm not really sure what's going on. It only loops once and I then get #Value! in the cell using my function. Any help would be greatly appreciated. Below is my program in its entirety. Code: -------------------- Function getIRR(BegVal As Double, CashFlows As Range, EndVal As Double, Optional Guess As Double = 0) As Double Dim AllFlows() As Double 'holds all flows in a single array (negative BegVal, negative CashFlows, and positive EndVal) Dim x As Integer 'increment counter Dim Periods As Integer 'number of periods used Dim i As Integer 'increment counter 'Get the number of periods used Periods = CashFlows.Cells.Count 'Enter all values into a single array On Error GoTo errorFlows: ReDim AllFlows(Periods + 1) AllFlows(1) = -1 * (BegVal + CashFlows.Cells(1)) For x = 2 To Periods AllFlows(x) = -1 * CashFlows.Cells(x) Next x AllFlows(Periods + 1) = EndVal TryAgain: 'Return here problem with getIRR 'Find the IRR (DWR) On Error GoTo errorIRR getIRR = (1 + IRR(AllFlows(), Guess)) ^ Periods - 1 GoTo endd: 'For when there is a problem getting the Flows errorFlows: getIRR = -0.8888 'For when there is a problem calculating getIRR errorIRR: i = i + 1 If i 20 Then getIRR = -0.9999 GoTo endd: Else Guess = -1.1 + i * 0.1 GoTo TryAgain End If endd: End Function -------------------- -- ericsh ------------------------------------------------------------------------ ericsh's Profile: http://www.excelforum.com/member.php...o&userid=26149 View this thread: http://www.excelforum.com/showthread...hreadid=511848 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Throwing Circular Errors When No Errors Exist | Excel Worksheet Functions | |||
Errors | Excel Programming | |||
Unresolved Errors in IF Statements - Errors do not show in results | Excel Worksheet Functions | |||
VB Errors | Excel Programming | |||
#N/A Errors to 0 | Excel Programming |