Excel - NPV function

NPV function calculates the net present value of a future positive or negative cash flows. For example, suppose you will receive an income of $100 one year later, how much does it worth today?

$100 received one year later worth less than $100 received today. Because if you have $100 in hand at this moment and invest it, suppose the return rate is 5%, then it will grow into $105 one year later.

Next, we will show you how to calculate the net prevent value of $100 received one year later. Let the net prevent value of $100 received one year later be X, and suppose the investment return rate is 5%. Then

X*(1+0.05)=$100

So, X=$100/(1+0.05)=$95.24

It means that if you have $95.25 in hand right now and invest it with 5% investment return, it will grow into $100 one year later. Therefore, $100 received one year later is worth $95.24 today. That is to say, the net prevent value (NPV) of $100 received one year later is $95.24.

If you use NPV function, the formula is:

=NPV(0.05, 100)


What is the value of $100 received 3 years later? In the following figure it shows the NPV of $100 received 3 years later is $86.38. In math, it is calculated as 100/(1.05*1.05*1.05). The formula is:

=NPV(B1, B2:B4)



Now let's consider a slightly more complex scenario. Suppose the income is $20 in year 1, $50 in year 2, and $80 in year 3. What is the NPV of this series of cash flow? The formula is same. Only the numbers changed.

=NPV(B1, B2:B4)


Now suppose you need to invest $100 now to gain these future cash flows. What is the net present value (NPV) of this investment? We need to add the initial cost of investment into the calculation. Note the initial cost of investment is negative 100.


The NPV is $31.91. It means this investment brings a positive rate of return.

If the initial cost of investment is not $100, but $140. The NPV will be -$6.18. It means you will lose money on this investment.


Now suppose the initial cost of investment is still $100, but you need to make extra $28 investment in year 2. How to calculate the NPV? Since the original cash flow in year 2 is 50, but now you need to make extra investment in year 2, the net cash flow for year 2 is $50-$22=$28. Like below:



Comments

Post a Comment

Popular posts from this blog

Excel - Solver examples

Image
Excel Solver is used to solve optimization problems, which is like solving equations. In this tutorial, we will give a very simple example on how to use Excel Solver. Basic example Suppose your car is low on gasoline. The gasoline is $3/gallon. You have a budget of $50. How much gasoline can you buy? Of course, for a problem this simple, we do not even need a Solver to solve the problem. However, the purpose is to teach the users how to use Excel Solver, so we make the problem extremely simple. We have the following framework: Goal: maximize quantity of gasoline Relationship: cost=quantity * unit price Constraint: cost<=budget 1. In cell B1, we enter unit price "3", in cell B4, we enter the budget 50. In cell B2, we just enter an arbitrary number as the number of gallons. We entered 5. In cell B3, we enter the formula "=B1*B2" to let it be the cost, which equals to 15. 2. Click Solver under the Data menu. 3. A Solver dialog window pops up. In "Set objective...
Read more

Excel - Pivot Table

Image
Pivot table is a powerful tool to summarize data and examine data patterns in Excel. Suppose we have 200 rows of data from a farmer, which sells fruit (apple, banana, orange) and vegetable (carrot, spinach) to clients in the US or Canada market, like below: 1. Select Insert > PivotTable 2. Click on PivotTable, then select the data range and where to place the PivotTable. When selecting the data range, the header must be selected. 3. Drag "Category" to Row labels, "Item" to Column labels, "Amount" to Values, and at last, drag "Market" to Report Filter. Note: You can user different fields for filter, row label or column label, depending on your business logic. You do not have to fill all the 4 fields, even only dragging the "Amount" into the "Values" field still produces a nice PivotTable. Again, all depend on your business logic. 4. The result is below. Notice that some cells are blank in the PivotTable. The reason is obvio...
Read more

Excel - INDEX function

Image
Unlike most other Excel functions which deal with a single number, a row or a column, INDEX function deals with a data table. It answers questions such as "what is the number at row 3 and column 4?" In the data range highlighted yellow above, what is the number at row 3 and column 2? Remember we only talk about the data range, not including header row. So the data on row 3 is carrot, not banana. The formula is: =INDEX(A2:B5,3,2) It returns 1.5, which is the price of carrot.
Read more