IRR# May Give "Overflow" Error; Change the Guess Parameter (81452)

SUMMARY

An "Overflow" error may occur when calling the IRR# function provided with the Basic Professional Development System Financial add-on library.

This error can occur if the Guess argument given to the IRR# routine is not accurate enough. To work around this problem, you can trap the "Overflow" error using ON ERROR and change the Guess argument used by the IRR# routine. The Guess may need to be changed repeatedly until a correct answer is achieved.

This information applies to Microsoft Basic Professional Development System (PDS) versions 7.0 and 7.1 for MS-DOS and MS OS/2.

MORE INFORMATION

The following example program demonstrates the problem and the workaround:

'  To run this program in the environment start QBX
'  with the /L Switch and load the FINANCER.QLB as follows:
'  QBX /L FINANCER.QLB

REM $INCLUDE: 'FINANC.BI'
DEFDBL A-Z
CLS
valuecount% = 52   ' number of cash-flow values in valuearray()
DIM valuearray(valuecount%)

Guess = .2       ' Guess the IRR

'  ENTER VALUES FOR CALCULATION
valuearray(0) = -10000000
valuearray(1) = 33771.34
valuearray(2) = -2000000
valuearray(3) = 44773.76
valuearray(4) = -2000000
valuearray(5) = 57839.02
valuearray(6) = -2000000
valuearray(7) = 73604.54
valuearray(8) = -2000000
valuearray(9) = 92840.95
valuearray(10) = -2000000
valuearray(11) = 116759.99#
valuearray(12) = -2000000
valuearray(13) = 146460.78#
valuearray(14) = -2000000
valuearray(15) = 183441.21#
valuearray(16) = -2000000
valuearray(17) = 229860.47#
valuearray(18) = -2000000
valuearray(19) = 287183.27#
valuearray(20) = -2000000
valuearray(21) = 538356.64#
valuearray(22) = -2000000
valuearray(23) = 446012.64#
valuearray(24) = -2000000
valuearray(25) = 551600.2
valuearray(26) = -2000000
valuearray(27) = 677244.29#
valuearray(28) = -2000000
valuearray(29) = 825126.1
valuearray(30) = -2000000
valuearray(31) = 996543.63#
valuearray(32) = -2000000
valuearray(33) = 1193617.82#
valuearray(34) = -2000000
valuearray(35) = 1418298.91#
valuearray(36) = -2000000
valuearray(37) = 1672832.17#
valuearray(38) = -2000000
valuearray(39) = 1960652.51#
valuearray(40) = 0
valuearray(41) = 2257813.63#
valuearray(42) = 0
valuearray(43) = 2590572.3#
valuearray(44) = 0
valuearray(45) = 2969964.72#
valuearray(46) = 0
valuearray(47) = 3412002.63#
valuearray(48) = 0
valuearray(49) = 3936106.5#
valuearray(50) = 0
valuearray(51) = 4564131.04#
valuearray(52) = 292931808#

Status% = 0

'    Include Error Handler for Work around
On ERROR GOTO ErrorHandler

irreturn = IRR(valuearray(), valuecount%, guess, Status%)

'    Turn off error handler
ON ERROR GOTO 0

IF Status% THEN PRINT "IRR error occurred; try different guess";

discountrate = irreturn
PRINT "IRR (fractional return on investment per period) = ";
PRINT USING "##.####"; irreturn

END

ErrorHandler:
      'Check for Overflow Error
      IF Err = 6 THEN
         ' Change guess to something that may work
         Guess = Guess- .01
         IF Guess = -0.1 then
             Status% = -1
             RESUME NEXT
         ELSE
             RESUME
         ENDIF
      END IF
      '    Fail on other Errors
      ERROR ERR