A function to get a variable row reference for range in XNPV funct

[Tex1960]

I want to use the formula below to calculate the NPV of a future stream of
payments based on the start date entered by the user in another cell on
another tab of the worksheet. I can't seem to get the # portions of the
formula below replaced with a function combination that does not result in a
text string with quotations embedded which then results in an error in the
NPV formula. Suggestions?

=XNPV(N3,Q#:Q190,N#:N190)

 

[Duke Carey]

Try

=XNPV(N3,indirect("Q"&cell_reference&":Q190,N"&cell_reference&":N190")

 

[Tex1960]

My original post was short on relevant information which threw you a curve.
Specifically, I have a table array of dates in column 1 (in sequential
order), corresponding principle in column 2, and interest in column 3, and
total P&I in column 4. I desire to write a function that will use an entered
date in an entry cell, perform a VLOOKUP to find the closest approximate row
of the table array and begin a XNPV calculation of Total P&I starting with
that future date through the end of the table. My experience with Excel is
not allowing me to use what you sent as a starting point. Could you or
others provide another suggestion? THX.

 

[Duke Carey]

Use the MATCH() function to figure out how far down the date column your
start date is. Match can require an exact match or, if there is now exact
match, give you the last date BEFORE the target, or the first date AFTER the
target - you choose. Read in the HELP file to find out how to use it
according to your wishes.

Once you've gotten the desired date, use the INdex() function to find out
how far down it is in your array. You'll have to adjust it if your array
doesn't start in row 1. In other words, if the data starts in row 3, Index
will return 1 for a match in row 3. Thus, you'd have to add 2 to the INDEX()
value to get the correct Excel row # to use in the INDIRECT() function I gave
you earlier.

HTH

 

[Harlan Grove]

Try

=XNPV(N3,indirect("Q"&cell_reference&":Q190,N"&cell_reference&":N190")

"Tex1960" wrote: ....

Testing is good. It often prevents posting huge mistakes.

First off, Duke's formula is a syntax error due to unmatched left and right
parentheses. Even if a second right parenthesis were added at the end, it'd
still be a syntax error since XNPV requires 3 arguments. Simply put, 2
separate INDIRECT calls would be needed.

An alternative:

=XNPV(N3,OFFSET(Q1:Q190,x,0,190-x,1),OFFSET(N1:N190,x,0,190-x,1))

OFFSET has the advantage of adapting its first argument when columns are
inserted/deleted between columns N and Q or rows inserted above row 1.

 

[Tex1960]

That all works except when I paste the embedded INDEX function (with the
embedded MATCH function) into the XNPV function to show the start of range
for each column, the INDEX function returns the value of the starting row
cell in each of those columns rather than the cell reference for each cell.
I've pasted the complete XNPV function below:

=XNPV(N3,INDEX(Table_1,(MATCH(VLOOKUP('Treas CM
Calculation'!C4+1,N5:Q190,1),N5:N190,1)),1):Q190,INDEX(Table_1,(MATCH(VLOOKUP('Treas CM Calculation'!C4+1,N5:Q190,4),N5:N190,1)),1):N190)

My Table_1 is range N5:Q190. The result is a #Num! error as the XNPV
function is seeing non-sensical cell ranges. Thanks for your help so far but
I'm not quite there.

 

[Tex1960]

Works like a charm now. Thanks!

Testing is good. It often prevents posting huge mistakes.

First off, Duke's formula is a syntax error due to unmatched left and right
parentheses. Even if a second right parenthesis were added at the end, it'd
still be a syntax error since XNPV requires 3 arguments. Simply put, 2
separate INDIRECT calls would be needed.

An alternative:

=XNPV(N3,OFFSET(Q1:Q190,x,0,190-x,1),OFFSET(N1:N190,x,0,190-x,1))

OFFSET has the advantage of adapting its first argument when columns are
inserted/deleted between columns N and Q or rows inserted above row 1.