Calculate the extended net present value for a series of irregular cashflows

What is extended net present value (XNPV)?

Money has time value associated with it. The value of income received (from investments, a business project etc) in the future has a lower value than the same amount received today. In other words, the value of money today is more than the value of money in the future. The extended net present value (XNPV) is the sum of the present value of the cash outflow and all expected cash inflows. It is calculated in a similar way to net present value (NPV) by discounting the cashflows with a discount rate. The larger the discount rate, the lower the value of money in the future is. However, XPNV relaxes the condition of cashflows in regular intervals and allows cashflows to take place at any point of time, e.g. we could have a series of cashflows on 01.01.24, 05.01.24, 05.06.24, 03.08.27. The interpretation of XNPV is the same as that for NPV. If the XNPV is positive, the series of cashflows (an investment with one or more cash inflows) is profitable. If a project or investment has a negative XNPV, it means that the investment has lost money. The XNPV is affected by the cashflows and the discount rate. If the XNPV of two projects/investments is equal, then the duration should be looked at as well since realistically, a project which is profitable in a shorter period of time is better.

​

When should I use XNPV?

In the financial sense, XNPV is used when determining the profitability of a financial investment where the cashflows take place irregularly such as an SIP investment or lumpsum investments at different points of time. Calculating the XNPV considers the time value of money and in some way it also considers risk if the discount factor is selected appropriately.

​

XNPV can also be used to compare different available investments in order to quickly identify all profitable or non-profitable investments.

​

How is XNPV estimated?

The formula that can be used to estimate the XNPV along with an explanation of the terms is provided above. This formula assumes at least one negative cashflow and allows for irregularly spaced cashflows (e.g. yearly cashflows). If the cashflows are regular, then this calculator could be used and would result in the same result as the NPV calculator. However, entering the date would be cumbersome and therefore it would be easier to use the NPV calculator in that case.

​

How should the discount rate be selected?

The discount rate is a user-defined input that is needed to calculate the XNPV. There are different ways to select the discount rate. This depends on the preferences of the decision maker. Changing the discount rate can influence decisions, .e.g.increasing the discount rate, requires investments to be more profitable in order to be selected. Let us look at 2 base cases

​

1. If risk is neglected and the discount rate is set to be equal to the expected inflation rate, then the investments are compared such that their returns need to match or beat inflation in order to be considered (An investment with an XNPV less than 0 would not be able to beat inflation)

2. If inflation is neglected and the discount rate is set equal to the risk-free investment rate, then the investments are evaluated to see if they can meet or beat the returns from a risk-free investment (An investment with an XNPV less than or equal to 0 would not be selected as the risk-free investment would offer equal or better returns at no risk)

​

Usually the discount rate is selected such that aspects such as risk or inflation is accounted for. A discount rate of 8% means that the project needs to make at least 8% in order for it to be viewed as profitable by the investor.

​

What is an example of XNPV with just one negative cashflow (investment)?

Assume an investment of ₹1,00,000 invested initially that pays out  ₹20,000, ₹50,000 and ₹1,00,000 on 01.01.24, 05.06.25 and 08.02.26. The XNPV for such an investment would be ₹40,845.21 assuming that the payouts are not reinvested in some way.

​

Should the payout in the last year be only ₹30,000 instead of ₹1,00,000, then the XNPV would reduce to minus ₹16,419.67 and would imply that the investment is not profitable although the net income was ₹1,00,000 on an investment of ₹1,00,000. This shows that the investment did not beat the benchmark set by the investor. (This benchmark would have probably considered risk and inflation to some degree) The investor would have technically not  made a loss, but on the whole the investment does not fulfil the XNPV condition as the XNPV is negative. If there would have existed another investment with a larger or positive XNPV, then that would be the better choice.

​

What is an example of XNPV for a fictitious SIP?

Assume a monthly SIP of of ₹1,00,000 with a discount factor of 10%. Let us assume that the SIP payments were done for 4 months and then eventually the investment was sold at  ₹3,75,000. Let us also assume that the SIP was started on 01.01.23. The cashflows would be -1,00,000, -1,00,000, -1,00,000, -1,00,000 and +3,75,000 on 01.01.23, 01.02.23, 01.03.23, 01.04.23 and 31.12.23 respectively.

​

After feeding in these values, the XNPV would work out to -54,344.12. As the XNPV is negative, this investment wasn't a good one as ₹4,00,000 was invested, but ₹3,75,000 was returned resulting in a loss of ₹25,000. The higher XNPV of ₹54,344.12 implied that besides the ₹25,000 real loss, there was an additional "virtual" impact owing to the time value of money when discounted at 10%.

​

If the investment was sold for ₹4,50,000, then this would translate to an XNPV of ₹13,855.5. As the XNPV is positive, this investment would be worth considering. If, ceterus paribus, there are others with a higher XNPV, then the one with the highest XNPV should be chosen. If each investment option has different characteristics, as would be realistically expected, then other constraints such as liquidity, risk, breakeven point etc would need to be considered.

​

Is fulfilling the XNPV criteria, i.e. XNPV >0, a sufficient consideration?

If an investment fulfills the XNPV criteria, it does not automatically mean it is a good investment. If we compare investment option A and B below, we see that the XNPV is equal but investment A starts paying back returns sooner and breaks even faster. If the risk of both investments is equal, then investors would usually prefer option B as the breakven is sooner and the investor can use the larger payout from investment B in year 1 to invest elesewhere. If an investor perceives the risk of B to be significantly larger than that of A, then selecting option A could make sense. If a third option, investment C is considered, then it has an even larger XNPV. If risk is equal to that of options A and B, then C would be preferable, However, should there be liquidity needs in years 1 or 2, then C would not be as suitable, although it it a better investment.Therefore, while comparing investments, it is important to look at other factors such as risk, the nature of the payouts, the breakeven point, liquidity constraints, liquidity requirements, investment objectives etc.

​

Why is this XNPV calculator useful?

This XNPV calculator is useful as it can help with project planning and/or investment planning. It's also a good way for beginners and professionals alike to estimate the profitability from a series of investments with irregular cashflows and to make better decisions.

​

How do I use this XNPV calculator?

Instructions to use this XNPV calculator are provided above.