XIRR Add-ins
 

I am looking to reference a named range in a spreadsheet
that has the following columns & rows. This range can
expand and contract. I want to have a button on a
separate sheet reference the range and display the IRR?

Date Deposit/Withdrawal
12/31/2002 -1000
02/08/2002 275
04/25/2002 425
05/21/2002 325
09/13/2002 275

XIRR Add-ins
 

I am looking to reference a named range in a spreadsheet
that has the following columns & rows. This range can
expand and contract. I want to have a button on a
separate sheet reference the range and display the IRR?

Date Deposit/Withdrawal
12/31/2002 -1000
02/08/2002 275
04/25/2002 425
05/21/2002 325
09/13/2002 275

I didn't get it to work using a named range, but maybe
the following will suffice. Suppose the data will remain
on Sheet2 in cells A1:B_ and the CommandButton on
Sheet1. Then the following code will put the XIRR
in B5 of Sheet1.

Private Sub CommandButton1_Click()
Dim iRow As Integer

iRow = Sheets("Sheet2").Range("B65536").End(xlUp).Row
Sheets("Sheet1").Range("B5").FormulaR1C1 = _
"=XIRR(Sheet2!R[-3]C:R[" & iRow - 5 & _
"]C,Sheet2!R[-3]C[-1]:R[" & iRow - 5 & "]C[-1],0.2)"
End Sub

By the way, I changed 12/31/02 to 12/31/01 to get XIRR to
work, and XIRR can't be called directly from VBA.

HTH,
Merjet