XNPV Discounted Cashflows

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 blank 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 zero balance cell 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