In my acquisition work, I rely on Excel’s NPV formula. Although the function is user-friendly, it can lead to an unintended error if not used correctly. Like many junior financial analysts, I made a mistake when I first used it.
This article will provide a step-by-step explanation of how to calculate NPV using long-form math. Additionally, it will highlight the common incorrect approach many individuals employ when using the NPV formula, followed by the correct method.
The Right and Wrong Way to Use the Excel NPV Formula
I will cut to the chase. The biggest mistake we make with the NPV formula is including Year 0 in the array. Excel recognizes the first value as a period one inflow/outflow. Accordingly, we need to exclude the Year 0 value but add that number at the end of the function. Let me clarify in this video.
One More Time–Be Careful How You Handle Period 0
Below is the screenshot of the last formula presented in the video.
Remember not to include Period 0 in the second argument of the NPV function. However, add Period 0 either before or after the NPV function.