course bg
EduPristine>Blog>Financial Modeling: Vital Facts about Excel Functions & Charts

Financial Modeling: Vital Facts about Excel Functions & Charts

January 31, 2014

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

Discounting the Cash Flows

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

Application of NPV and XNPV in 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 of Application of NPV and XNPV Functions in 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

Reading and Creating Bar Chart in 3D

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

Plotting Data in Graph

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.

Plotting Data on X and Y axis

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:

Steps to use Roundup function
Steps to use Roundup Function in excel

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.

About Author

avatar EduPristine

Trusted by Fortune 500 Companies and 10,000 Students from 40+ countries across the globe, it is one of the leading International Training providers for Finance Certifications like FRM®, CFA®, PRM®, Business Analytics, HR Analytics, Financial Modeling, and Operational Risk Modeling. EduPristine has conducted more than 500,000 man-hours of quality training in finance.


Interested in this topic?

Our counsellors will get in touch with you with more information about this topic.

* Mandatory Field

`````````````````````````````````````````````````` Post ID = 38895