• Aman Jain

Basic Excel Functions you should know #3 (NPV)

Updated: Aug 9

Today, I was going through some of the Life insurance policies my Dad had taken up on my name, back when I didn't have much idea about how such investments make sense. I guess we were sold on the premise that we need to pay a specific amount for a few years and the company will return us a higher amount in the future. Plain simple math, we are making money and it seems like a good investment, right? Well, maybe. In order to be sure, we have a few financial/mathematical formulas to understand the results of investments.

Speaking from a different perspective, let’s say you are deciding between two investments which will give different returns in different years and you wish to know which one to pick?

To answer the above questions, we have something called Net Present Value, which can be very useful for financial analysis and financial modeling specially when we need to compare investments (a company, a project, a cost-saving initiative, etc.). In fact, I use it frequently in valuation using something called a DCF model (read: Discounted Cash Flow)

Let’s have a look at the mathematical formula first to know what’s going on:

NPV = F/[(1+i)^n]; where

F = Future cash flow

i = Discount rate

n = number of periods in the future cash flow is (eg maybe 4th year)

It will take a a good amount of time to calculate the NPV using normal calculator or by hand. Here’s where Excel comes in to help calculate in a jiffy.

Formula – “=NPV(discount rate, series of cash flow)

As usual, I have saved the file here, so you can work along as you learn :)

Let’s take an example:

  • Here, we have our discount rate in cell B2

  • The cash flows is present in range B5:F5

  • Now, in cell, B7, we will use the formula to calculate the present value

  • That’s it. You get a NPV of Rs. 606.41

Now, the interesting thing about NPV is that it helps us to look at the real picture. Let’s take another investment example. In the above example, you were getting a sum total of Rs. 716 over a period of 5 years. Now let’s say you have another investment opportunity which will give you Rs. 760 equally divided in Year 4 and Year 5. You may think that the second investment is better since it is giving a better return in absolute terms, but that’s an incorrect way to compare. Let’s see the what the NPV of the second investment looks like:

As you can see, even though the absolute number is higher than the first investment, the NPV is lower because the majority of cash flow is taking place in Year 4 and year 5, which will be worth lesser in today’s term (read: inflation, aka the discount rate)

So there you have it. You now know how to compare investments, and understand if you are actually making or losing money from your life insurance investments. Happy Trading :)


©2020 by IndexMatched. Proudly created with