During our training sessions, we have realized that a large percentage of people working on MS Excel have an understanding about the **NPV function** but are not aware of the **XNPV** **function**.

In this blog, we will highlight the benefits of using the **XNPV **function over the **NPV function**.

Let’s first try and understand how the **NPV** function works.

**BASIC – NPV Function**

**1. The NPV function’s syntax and usage****:**

The **NPV** function calculates the net present value of an investment by using a discount rate and a series of future payments (negative values) and income (positive values).

**For example**: We want to find out the net present value of cash flow in the purchase/sale of a stock (in US$).

We must understand the syntax of the NPV function to find out the answer to our problem.

** ****Formula****: **= NPV (rate, value1, [value2],…)

**Rate:**The rate of discount over the length of one period**Value 1, Value 2,…**: Value 1 refers to the cash flow at the end of period 1 and so on…- The cash flows are assumed to have equal time intervals and occur at the end of each period
- It is important to note that the NPV function starts computation from period 1. So we must also adjust the cash outflow of period 0. This is done by adding the cash flow of period 0 to the value derived from the cash flows starting period 1.

Now, we will try and ascertain how to complete the **NPV** function in this case.

**Rate:**The discount rate is*1%***Value 1:**US$0,**Value 2**: US$20.0,**Value 3**: US$27.0, and**Value 4**: US$780.0- Additionally, we have to add the cash outflow of
**US$100.****0**to arrive at the overall NPV over the period 0 – period 4

As shown in the above figure, the net present value is: **US$570.1**

**BASIC – XNPV Function**

**2. The XNPV function’s syntax and usage****:**

The **XNPV **function returns the net present value for a schedule of cash flows that is **not necessarily periodic**. Additionally, it takes into consideration cash flows from **period 0.**

**For example**: We want to find out the net present value of cash flow in the purchase/sale of a stock (in US$).

We must understand the syntax of the XNPV function to find out the answer to our problem.

** ****Formula****: **= XNPV (rate, values, dates)

**Rate:**The range of cells**Values:**The first cash flow is generally negative (cash outflow of period 0). The next series of cash flows are discounted based on a 365-day year**Dates:**Schedule of payment dates (**not necessary in the same time interval)**

Now, we will try and ascertain how to complete the **XNPV** function in this case.

**Rate:**The discount rate is*1%***Values:**The first cash flow is negative – (US$100.0) and the last cash flow is US$780.0**Dates:**The first date is 16^{th}March 2015 and the last date is 30^{th}June 2018 (**Note: Time intervals are different between various dates**)

As shown in the above figure, the net present value is:** US$597.5**

**3. Reasons why XNPV is better than NPV****:**

**NPV doesn’t work in case of different time intervals****:**It is not possible to find the net present value if the gap between the dates of scheduled cash flows is not same.**NPV requires separate adjustment for period 0 cash flows**: While using the NPV function, we need to separately adjust for the period 0 cash flow to arrive at the composite NPV (Period 0 – Period 4 in our example).

[hr style=”1,2,3,4″ margin=”40px 0px 40px 0px”]

**About us: **Cians Academy is a division of Cians Analytics and provides training on financial topics to those interested in building or honing their financial skills. The courses are especially relevant for people who want to work in the financial services industry or become conversant with financial analysis. Cians Academy is based in Gurgaon, India.

The courses will be highly beneficial for people looking to make a career in finance (CA/CFA/Commerce Graduates/Working Professionals).

- Want to become an expert in Financial Modeling – Enroll in our Investment Banking Course
- Learn MS Excel Online for Free – Yes, I wish to learn

### 1 Comment

Comments are closed.

## Priya

Nice…. a new concept learned