VBA Excel Functions

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

The following table was dropped into Excel using an Access module. I need to sum the entire amount column and run Excel's IRR function to determine the returns.

Date Transaction Amount
12/31/1998 Capital Contribution ($3,000,000.00)
12/31/1999 Distribution $1,036,000.00
12/31/2000 Distribution $1,200,000.00
12/31/2001 Distribution $925,000.00
12/31/2002 Distribution $646,000.00
9/30/2003 Distribution $4,700,000.00
$1,700,000.00
Desired total column $5,507,000.00 35.66

Current Code exerpt
' Sum range of cells
Sheet.cells(j, i).Value = Sheet.range("C20").Value +
Sheet.range("c25").Valu

Can anyone show me how to reference ranges and create the desired output

Thanks, in advanc

Forrest
 
From vbe Help
IRR Function
Returns a Double specifying the internal rate of return for a series of
periodic cash flows (payments and receipts).
Syntax

IRR(values()[, guess])

The IRR function has these named arguments:

Part Description
values() Required. Array of Double specifying cash flow values. The
array must contain at least one negative value (a payment) and one positive
value (a receipt).
guess Optional. Variant specifying value you estimate will be returned
by IRR. If omitted, guess is 0.1 (10 percent).



Remarks

The internal rate of return is the interest rate received for an investment
consisting of payments and receipts that occur at regular intervals.

The IRR function uses the order of values within the array to interpret the
order of payments and receipts. Be sure to enter your payment and receipt
values in the correct sequence. The cash flow for each period doesn't have
to be fixed, as it is for an annuity.

IRR is calculated by iteration. Starting with the value of guess, IRR cycles
through the calculation until the result is accurate to within 0.00001
percent. If IRR can't find a result after 20 tries, it fails.


--
Don Guillett
SalesAid Software
(e-mail address removed)
Forrest said:
The following table was dropped into Excel using an Access module. I need
Click to expand...
to sum the entire amount column and run Excel's IRR function to determine
the returns.
 
Back
Top