We all are experimental by nature. At times, it is good that we don’t have optional or alternative ways of doing certain things. It prevents incomprehension of what has been studied or learnt already. But options and alternatives available to us force us to look back and cross-check. This cross-checking throws light up on some facts we would have otherwise ignored.
This article explores lesser known but useful facts about some of the excel functions and charts.
Example 1 – NPV and XNPV functions
1. NPV refers to the present net value of a series of cash flow. There are 3 ways to calculate the NPV:
a. Using NPV function built in excel: Excel definition of “NPV function” is – ‘Calculates the present net value of an investment by using a discount rate and a series of future payments that are periodic.’ Syntax is NPV(rate, value1, value2, …)
b. Using XNPV function built in excel: Excel definition of “XNPV function” is – ‘Returns the present net value for a schedule of cash flows that is not necessarily periodic.’ Syntax is XNPV (rate, values, dates).
c. Using the first principle here, we would traditionally discount the cash flows by modeling the formula 
and then calculating the algebraic sum of all the PVs.
2. What’s the thought now? Do you think all the three methods will yield the same result? Let’s look at the example below.
3. Check the outputs in the highlighted cells, methods used on its LHS and comments on the LHS. It won’t take you long to realize why the outputs are different either greatly or marginally.
4. That is precisely why analysts across the globe still use the first principle to arrive at NPVs instead of using the in-built functions of Excel. Following are words of caution while using in-built functions:
a. NPV function assumes that all cash flows appear in the future and at the end of the period. It necessarily assumes the first cash flow to occur at t = 1 and all the subsequent cash flows to be separated by a year. And that’s the reason why its application blindly throws the wrong output in cell C6. So, if your first cash flow occurs at the beginning of the first period (t = 0), the first value must be added to the NPV result separately and should not be included in the value arguments. Check the correct result in cell C7 after incorporating this change.
b. Alternatively, you can use XNPV function since dates corresponding to the cash flows are also available. See the output in C8. Also check the output in C11. Why is output of XNPV function different from that of NPV function and the first principle? This is because XNPV discounts all the cash flows on a 365-day-year basis. We have year 2016 in our example above, which is a leap year. Hence, there is a small variance.
c. Subsequently, we have cross-checked the calculation of XNPV using the first principle. Note the output in cell C15. We have used a 365-day-year to calculate the discount rate.
Exhibit 1: Application of NPV and XNPV Functions of Excel
I hope you will now be cautious while using the NPV and XNPV functions. Another calculation is demonstrated below to cement our realization and understanding.
Exhibit 2: Another Example of Application of NPV and XNPV Functions of Excel
Example 2 – Bar Chart in 3 D
Please examine the bar chart below. Can you guess the values corresponding to variables A, B, C, D & E from the chart below?
Exhibit 3: An Example of 3D Bar Chart
Example 3 – Plotting the data on x axis
Suppose some data in the following format is given to you. You are required to make a simple bar chart plotting years on x-axis and Sales on y-axis.
If you blindly select the data and plot the 2D bar chart in excel, this is what you will get:
Exhibit 4: An Attempt to Plot Data on x-axis directly
Surprisingly Excel has displayed the ‘year’ also by way of a bar. You can rectify the graph according to your needs. But what’s the problem with Excel?
We can fix this easily in a following manner:
X-axis should remain without a heading. Excel will automatically make it x-axis in the chart.
Example – 3: Usage of ROUNDUP Function
Quite often when you are financial modeling, you will come across raw data that needs to be grouped into various bins. Let’s say credit scores of individuals are given to you as below:
Now we need to convert these scores into 10 equally spaced bins. Let’s call the observations as xi. So bins should be obtained as per function below:
How are you going to plot this on Excel? Have you started thinking about it already?
Do you make use of the IF THEN function?
Think of how many nests you will have to build it in.
An AND function inside the IF function? It is definitely an improvement over the nested IF function but the effort required is still enormous.
Have you come across a function called “ROUNDUP”? Have you ever noticed how powerful this function is?
Excel definition of ROUNDUP – ‘Rounds a number up, away from 0 (zero).’
Syntax is: ROUNDUP (number, num_digits)
If we roundup a number to zero digit after decimal, the function will return the least integer greater than the number.
ROUNDUP(5/3,0) = 2
Does this ring bell in your mind?
I will make use of this function to do the job of bin classification.
Examine the power hidden in this function. It has simplified your life enormously.
Example – 4: A pie of pie
Suppose you have to plot the operating expenses as a pie chart as shown in the example below. If you simply select the data and apply a pie chart, this is what you will get:
Exhibit 5: An Ordinary Pie Chart
Slices corresponding to Small expenses like Rent, Electricity, Stationery, Telephone & Internet and Housekeeping are so thin that they are hard to distinguish.
When we encounter such a situation, we should try to enhance the quality of our graph by grouping the smaller expenses under a head called “Others” and then splitting them into another pie chart. A pie chart of the pie in Excel does exactly this. If we choose that graph, see the output below. Once you obtain the pie of the pie chart, right click on it and choose ‘format data series’. There you will see an option to choose as many values from the bottom to group in “Others”. Excel groups the values from bottom by default. Hence, if you intend to use this function, you need to arrange lower value heads towards the bottom in your Excel table.
Exhibit 6: A Pie of Pie Chart
Do you have any tips?
Want to share them with us?
Need any clarifications on the topics explained here?
Please feel free to start a discussion thread. We will be glad to hear from you and help you.