XNPV Calc that Adjusts if the Dates and Amounts Change

[deli9680]

I have a stream of cash flows and corresponding dates that I am calculating an XNPV for... I have the model fully automated to a few inputs can change everything...

However, I can't get the XNPV calculation to automatically change it's parameters as the cash flows and dates change... Is there anyway to do this?

Basically, I'm trying to find a way to have the XNPV function search for the first and last date with a cash flow and reset itself to calculate for those new cash flows.

[Pauleyb]

I find the easiest way to handle dynamic ranges is to use tables. Put your cash flows in one column, and then the dates in the column next to the cash flows. Highlight one of the cells and hit Ctrl-T. This will generate a table.

For your xnpv calculation, you should use the table references for the ranges. You will get something like this:
=XNPV(0.05,Table1[Column1],Table1[Column2])

Now you can add or remove rows to the table and the xnpv formula should always adjust.

[deli9680]

Pauley,

thanks for the reply and that's a good tip...

but in this case, I need the XNPV to only start calculating at the first cash flow... in your example, the XNPV starts calculating at Month 1 regardless of whether or not there's a corresponding value.

I actually need it to only start calculating at the first corresponding cash flow.

Does that make sense?

[Pauleyb]

Yes, you do make sense, and maybe I did not explain how to use the table. In my example, you would delete the rows that do not have pairs of numbers. The XNPV calculation using the Table references would not need to be changed due to the deleted row, unlike other range referencing methods.

Based upon your statement:

I have a stream of cash flows and corresponding dates

it would see that you should always have a cash flow and a date. Your second post makes it sound like you may have some 'blank' cash flows. That is a different problem, so which one is it?