Automation problem

[Ron Freudenheim]

The purpose of my Access 2002 application is to take
information concerning loans and their underlying
collateral (properties) and to determine what would be the
correct price to pay for the loan, given certain
discounting factors, the underlying cash flow from the
properties, and a desired investment yield.

I have automated the transfer of information from Access
2002 to Excel 2000 and for the most part it works
beautifully!

The key calculation is the Internal Rate of Return and
that is where the problem is.

The code correctly puts the various values that need to be
included in the IRR in the correct places on the
spreadsheet and correctly writes the IRR formula. One of
the data points to be considered in the IRR is the price
that one would pay to purchase the loan.

The code examines the initial resulting IRR, and if it is
less than a pre-set target yield amount, it subtracts
$1000 from the initial price of the loan and again checks
to see if the resulting IRR value is at or above the
target. The code keeps looping and subtracting $1000 with
each pass until the target is met. Once the target is
met, the resulting value is put on a screen form and a
bunch of other things happen.

The problem is that while it appears that the looping is
getting to the correct answer and stoping correctly (by
looking at the output from the Debug.Print lines in the
attached code), the IRR value in the spreadsheet does not
meet the target value. For example, if I am seeking a 10%
IRR, the code will execute through the loops and come up
with a calculated IRR value that is slightly above 10% and
then stop (as it is supposed to), but the value that shows
in the spreadsheet for the IRR is 4.96% and the
corresponding value for the price to pay for the loan is
correctly posted for that IRR(i.e. the IRR function in
Excel is working OK).

Here is what the code looks like:
'Sets the third sheet as the current sheet.
Set ExcelSheet = ExcelBook.Worksheets(3)
'Sets the range to the cell that calculates the IRR
Set rng1 = ExcelSheet.Range("e32")

'Set the cell reference to e19 which is the price to
pay for the loan
intRowNumber = 19
intColumnNumber = 5

'This variable comes from the form that is
loaded "frmCashFlowAssumptions"
varYieldRequirement = Forms!frmCashFlowAssumptions!
YieldRequirement
On Error Resume Next
Do While rng1 < varYieldRequirement
If rng1 < varYieldRequirement Then

'Subtracts 1000 from the value in e19 until
rng1 = > varYieldRequirement
ExcelSheet.Cells(intRowNumber,
intColumnNumber) = ExcelSheet.Cells(intRowNumber,
intColumnNumber) - 1000

Debug.Print "The purchase price is set to " &
ExcelSheet.Cells(intRowNumber, intColumnNumber)
Debug.Print "The IRR is " & rng1
Debug.Print "The cell says " & ExcelSheet.Range
("e32")
Else
End If
Loop
'Puts the derived price to pay (the DIV) which is
in e19 onto the form.
Forms!frmCashFlowAssumptions!DIV = ExcelSheet.Cells
(intRowNumber, intColumnNumber)


The last pass trough the loop produces the following lines
from the Debug.Print:

The purchase price is set to 51668
The IRR is 0.100963374951904
The cell says 0.100963374951904


BUT! the value on the spreadhseet for the IRR is 4.96%
which is the IRR if the loan were purchased for 51668.

The statement "On Error Resume Next" is there in case the
initial IRR value returned on the spreadsheet results in
#NUM! - which leads to a type mismatch error. I am
wondering if because in the initial few passes through the
loop the result is this error, that somehow the calculated
IRR gets out of whack somehow?

Is this possibly a timing problem? What can I do to get
to the correct IRR calculation?

Thanks VERY much in advance.
Ron Freudenheim
(e-mail address removed)

 

[TC]

Hi Ron

I believe it is a big mistake to execute blocks of code under the auspices
of an On Error Resume Next. You are effectively saying, "Hey VBA, I don't
care WHAT friggin' errors occur - just ignore them & keep going!"

If I were debugging this, I'd start by commenting-out that statement. Then,
if something fails, work out why, then fix it properly (by recoding that
part), or temporarily step over it using debugger commands.

Otherwise, you have no clue what might be going on inside that loop!

HTH,
TC

 

[Ron Freudenheim]

Dear TC et. al.
I worked on this problem all day yesterday and found that
the problem does not reside in the IRR worksheet function
or in the automation per se. I did take out the On Error
Resume Next and substituted an If statement that handled
cases where the IRR worksheet function resulted in #NUM!
error - but that wasn't the problem either.

I took a completely diferent approach, which was to use
the IRR function within VBA itself. I passed the values
for the IRR calculation from the spreadsheet and then ran
the same sort of loop that if the target IRR wasn't met,
would decrement the price of the loan by 1000 and then
test the IRR again. Excel is only involved in passing the
values, not the calculation of the IRR.

What I found was that the IRR function returned the wrong
answer using this approach also, and in fact, produced the
exact same wrong answer as doing it the original way which
relied on the IRR worksheet function. The code loops
through until the calculated IRR is greater than the
target value - BUT the derived value for the price if put
into the worksheet function for IRR ( or the VBA function)
results in an IRR that is less than the target.

I am baffled!

Thanks in advance for taking a look at this...
Ron

This is what the code looks like:

Dim Guess, Fmt, RetRate
Static Values(5) As Double ' Set up array.
Guess = 0.1 ' Guess starts at 10 percent.
Fmt = "#0.00" ' Define percentage format.
Dim nInflows As Double
Dim nOutflows As Double
Dim MyPrice As Double
Dim myVal1 As Double

'This variable comes from the form that is
loaded "frmCashFlowAssumptions"
varYieldRequirement = Forms!frmCashFlowAssumptions!
YieldRequirement

'This is the starting "Price" which is the current
loan amount.

MyPrice = Forms!frmCashFlowAssumptions!
UnpaidPrincipalBalance

'Gets values from the spreadhseet.
Set ExcelSheet = ExcelBook.Worksheets(3)
nInflows = ExcelSheet.Range("e16") +
ExcelSheet.Range("e17")
nOutflows = ExcelSheet.Range("e20") + ExcelSheet.Range
("e21") + ExcelSheet.Range("e22") + ExcelSheet.Range
("e23") + ExcelSheet.Range("e24") + ExcelSheet.Range
("e25") + ExcelSheet.Range("e26") + ExcelSheet.Range
("e27") + ExcelSheet.Range("e28")

'The first value has some other elements (inflows and
outflows) other than the price
myVal1 = nInflows - nOutflows - MyPrice
Values(0) = myVal1
' Positive cash flows reflecting income for the
successive years.
Values(1) = ExcelSheet.Range("f29")
Values(2) = ExcelSheet.Range("g29")
Values(3) = ExcelSheet.Range("h29")
Values(4) = ExcelSheet.Range("i29")

RetRate = IRR(Values(), Guess)

Do While RetRate < varYieldRequirement
MyPrice = MyPrice - 1000
myVal1 = nInflows - nOutflows - MyPrice
Values(0) = myVal1
Values(1) = ExcelSheet.Range("f29")
Values(2) = ExcelSheet.Range("g29")
Values(3) = ExcelSheet.Range("h29")
Values(4) = ExcelSheet.Range("i29")
RetRate = IRR(Values(), Guess)

Debug.Print "The IRR is " & RetRate
Loop

 

[Ron Freudenheim]

Dear TC et. al.
After much work, I found the problem, which wasn't in the
IRR calculation at all! The derivation of the IRR was
correct for the values in the spreadshet at the time the
IRR calculation was run. Subsequent code erroneously
deleted one of the addends in one of the yearly cash flow
totals, rendering that year's total different from the
total used in calculating the IRR.

Thanks again for taking time to respond.
Ron