How to calculate XNPV with blank cells?

**Raker** · 12-06-2012, 05:09 PM

Ok, so I am having trouble calculating NPV based on cashflow dates and I am not sure how to solve this problem. It would be great if some excel Guru could help me out:

I have a massive file that contains daily dates across (eg from B3 to all the way across to for example XX3)
Then I have thousands of contracts listed in the rows below and their cash flows below a corresponding date.(the day on which the cash flow is expected)
Not one of the contracts have a cashflow corresponding to every date. (there are many blank cells in the rows containing the cash flows.

How can I calculate the XNPV for each of those contracts?

Any thoughts would be greatly appreciated.

Thanks!

**shg** · 12-06-2012, 05:30 PM

Select all the blank cells and fill with zeros?

**djapigo** · 12-06-2012, 05:35 PM

Hi Raker,

Can you fill in the blank cells with a 0?

A quick way to do it is using the Go To function.

Highlight the whole area of cash flows, press F5, click on Special..., choose Blanks...

This should now just highlight all the blank cells... type 0, then CTRL-ENTER... This should fill in all the blanks with a zero...

Then the XNPV formula should work now...

I'm not sure what XNPV does, I tried replicating it like the NPV or PV, but couldn't get it the same...

Anyway, let me know if this is what you need...

Dennis

**Raker** · 12-06-2012, 05:52 PM

Thanks Dennis, I think you hit the nail on the head. It seems like such a simple solution, haha. I really appreciate it.
Thanks again

**djapigo** · 12-06-2012, 05:53 PM

Hope it worked... let me know otherwise...

To mark your thread solved do the following:
- Go to the first post
- Click edit
- Click Advance
- Just below the word "Title:" you will see a dropdown with the word No prefix.
- Change to Solve
- Click Save