XNPV Discounted Cash Flow

Hi Guys/Girls

I have an issue calculating XNPV.

My model is a monthly cash flow running for 15 years (The attached excel spreadsheet is only an excerpt).

Excel will not calculate XNPV if the ranges in the formula include zero balance cells at the end, which is a problem because not all projects are 15 years in length. For instance, one project may only be 8 months long, which would leave the XNPV ranges with 172 blank cell balance periods (14*12+4). The Result is #NUM!. There is obviously a way around this.

My thought would be to individually discount each column, conditionally on a >0 net cash flow balance, then add up all the column totals to get your NPV. However I am not sure how to do this?

Can anyone help with the correct solution here?

Thanks in advance and Best Regards,
Mitchell