VBA Excel Functions
 

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

VBA Excel Functions
 

Sheet.cells(j, i).Value
Application.WorksheetFunction.Sum(Range("e2:e6")

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements

VBA Excel Functions
 

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

"Forrest" wrote in message
...
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").Value

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

Thanks, in advance

Forrest

VBA Excel Functions
 

Thanks for the reference.

VBA Excel Functions
 

It's all about Syntax. Thanks again, it worked like a champ.