Looking Into NPV And NPV With Probability Based Cash Flows In Excel

Excel spreadsheets (.xls)

Today, I decided to do a deeper look into how corporate projects can be evaluated in excel quite easily. The reality is that there are probably 1 million different ways to do it but I will start with a few simple examples and provide the link to the excel file at the end of this post to help you play around with the data and hopefully be able to build your own spreadsheet or build on top of mine.

Net Present Value – NPV In Excel

NPV is a rather simple function that can be used in excel. Before getting started though if you’d like a quick refresher about what a net present value is, you can see an older post about it here. In short, the NPV should help you determine if a project will be profitable given a certain cost of capital. For example, imagine that company XYZ can spend $5000 now to launch a new product that will be sold for 5 years.

-The revenues in year 1 will be $7500 and will increase by $500 every year
-Costs will be of $5000 to start and then $5000 per year
-The company needs to pay 40% taxes on the profits from this product

In short, the new product’s financial impact can be seen here:

By using the NPV function as follows, I can get the expected value:

=NPV (rate, value1,value2,value3,etc)
=NPV(B3,C10,D10,E10,F10,G10)+B10

I am adding B10 at the end because the initial cost should not have any rate applied to it, it will be spent on the first day. As you can see, the NPV of this project is $2745 so it does look like a promising project.

NPV With Cash Flow Probabilities

What is more probable though is to have some uncertainty regarding the financial impact of a new activity. I will simplify the previous example by only using the cash flows as you can see here:

Then, let’s simply add a few more scenarios and odds that those will happen:

Then, I will simply use the sumproduct function to calculate the “expected cash flow” so for example:

=SUMPRODUCT(D6:D9,E6:E9)

You can now see the new NPV:

And of course, you can download my spreadsheet here!!

This example could be made much more complex but it gives you an idea! Let me know if you have any issues!

Thanks!

***************************************************

Look Good at Work and Become Indispensable Become an Excel Pro and Impress Your Boss


***************************************************

2 Feedbacks on "Looking Into NPV And NPV With Probability Based Cash Flows In Excel"

Benjamin

I don’t see whre the 10% cost of capital is factored in your spreadsheet to generate the NPV. Is it included in the NPV “function”?



Leslie

@Benjamin – Actually I had not specified a cost of capital in this case but it could be done within the function yes