Spreadsheet software like MS Excel is extensively used for accounting purposes. It makes the calculation easier and saves time as compared to manual calculations which on the other hand is time-consuming and prone to human error. In Excel, NPV (Net Present Value) and XNPV are very common functions used for calculating cash flow.
NPV vs XNPV
The main difference between NPV and XNPV is that in NPV the calculations are made considering that the future payments that will be made are going to be based on an equal time interval. On the other hand, XNPV is the modified and more precise version of NPV. XNPV assumes that the cash flow does not arrive in an equal time interval which is more practical.
NPV stands for net present value. It is a very important part of accounting as it deals with investment returns and shows all the values of future cash flows. The cash flows include both positive and negative values which are calculated considering that the cash payment is regulated and periodic.
XNPV is used in determining net present value when the time interval is not regular or periodic. It can be calculated when the period between two payments is unknown. This is the reason, why the formula for XNPV does not contain a fixed period in it and it is useful for irregular payment processes.
Comparison Table Between NPV and XNPV
| Parameters of Comparison | NPV | XNPV |
| Time | NPV assumes that future cash payments take place at regular intervals. | In the case of XNPV, the calculation is done considering the time interval between cash payments is irregular. |
| General Formula | The total time count is required in the formula of NPV. | Instead of the time, different dates for expense are required. |
| Formula in Excel | =NPV(rate, value1, [value2],…) where rate is the discount rate and values are cash inflows/outflows. | =XNPV(rate, values, dates) where the rate is the discount rate, values are cash inflows/outflows, and date is the date of the expense. |
| Application | NPV is used in the calculator when payments are made in equal intervals. | XNPV is used when the dates of expense are given but the time interval is not equal. |
| Precision | It is less precise as compared to XNPV as the assumption of equal time is made. | XNPV is more practical and precise. |
What is NPV?
NPV is a very common term used in the accounting sector while preparing spreadsheets for period cash flow. It can be defined as the net difference between the existing value of net cash arrival and the existing value of the cash expenditure.
NPV is used for the calculation of net cash flow when the cash payment takes place between regular time intervals. In accounting, to see the potential that lies in investing in a new project or an investment, NPV is used. The value of NPV is used during the preparation of the capital budget to understand the risks and opportunities of new investments.
When the inflow/outflow of cash takes place periodically, the formula used for the calculation of NPV is given below:
NPVt=1 to T = ∑ Xt/(1 + R)t – Xo
Here, Xt is the total cash inflow for a time period t
Xo is the expenditure due to net initial investment
R denotes is the discount rate, and
t is the total time period
A company chooses a project when the NPV value is positive because it means the return after the investment will be more than the total project expenditure. A negative value of NPV incurs loss while zero value requires addressing other factors.
What is XNPV?
The concept of XNPV is similar to that of NPV only with the main difference of time interval. While calculating XNPV, the cash payments are not considered to be taking place at an equal time period. This seems like a better and more practical option and it also increases the precision.
The formula used for the calculation of XNPV is given below:
XNPVt=1 to N = ∑ Ci/[(1 + R)^((dx-do)/365)]
Here, dx denotes the date of the x’th expense
do is the date of 0’th expenditure
Ci is the i’th expense, and
R is the discount rate
In MS-Excel, while calculating the value of XNPV, discount rate, cash inflow/outflow, and dates are required. In the case of selecting the range of values (cash inflow/outflow), positive terms indicate income and negative terms indicate payments.
Main Differences Between NPV and XNPV
- Both NPV and XNPV show the present value of all the future cash flows (positive and negative) by using the discount rate but NPV uses the total time period to calculate it while XNPV uses the specific dates of expenses.
- While NPV calculates the net present value when the payments are in equal time intervals, XNPV is used when payments are irregular.
- In Excel, the discount rate and values are required for the calculation of NPV whereas the additional range of dates needs to be selected for calculating XNPV.
- In XNPV, error messages are common since the dates are not given in the right format in excel sometimes which doesn’t occur in NPV.
- The value returned by using the XNPV formula is more precise at it doesn’t assume equal time internals like in the case of NPV.
Conclusion
The use of NPV and XNPV in creating a capital budget is very essential. For people who are new to accounting, these two terms may seem to perform the same job. But, the use of NPV and XNPV are done in different situations.
Though XNPV is more precise as it considers payment at equal time intervals, in some cases NPV makes the calculation simpler and gives us a transparent result to make the right decision.
If the value of NPV is negative, investing in that project will result in loss and if the value is positive, it seems to be a lucrative opportunity. NPV and XNPV both give different results even with the same discount rate and value of cash inflow/outflow. This is because they consider different time intervals.
References
- https://www.tandfonline.com/doi/abs/10.1080/00137910108967561
- https://www.taylorfrancis.com/books/mono/10.1201/9781315171524/foundations-real-estate-financial-modelling-roger-staiger