LCOE and Excel

Here's a funny thing that people might be interested to know. I was building a model in Excel to play around with LCOE calculation - a topic to which I'll be returning - and I found out some interesting things about the Excel NPV function. I found that if you're asking the NPV function to calculate the net present value of a sequence of cells, if there's an empty cell, the NPV function ignores the empty cells and moves on to the next occupied one. This can mean that late years are under-discounted if you've got empty cells. Filling the cells with zeros or a formula returning zero fixes the problem. I spent a while chasing this down, so hope this is useful. I'm sure readers who have got this far know that Excel effectively assumes payments at the end of each year (i.e. the first year is discounted by a full year's discount rate). You may wish to correct for mid year cashflows, or even more precisely if your cashflows are well constrained in time. The reason I've been doing this is that I'm looking hard at the LCOE function - which I'm not sure fairly evaluates long term projects, or projects with variable risk. More on this over the next few weeks.