- HOME |
- FAQ |
- SITEMAP
- 010 590 6152
- info@auditexcel.co.za

JHB 12-16 Sep , DBN 19-21 Sep CT 12-14 Oct

Not sure which Excel course you should do? Try out our free Which Excel Course Should you do" questionnaire. 20 questions and you will know exactly what course you should do!

How to use the NPV in Excel to determine the Net Present Value or a series of cash flows

This segment will show you how to determine the NET PRESENT VALUE (NPV) in Excel of a series of cash flows.

In this example we have a series of cash flows starting at the beginning of Period 1, which we called Period 0. An outflow of 20 000, and then the next five years we have inflows of cash.

We would like to determine what the NET PRESENT VALUE is of these series of cash flows. In order to do this we can make use of the NET PRESENT VALUE function.

- if we click on the cell
- and go the Function Wizard
- go into the Financial categories
- and find the NPV function
- This will appear, we are asked to fill it in.
- The first criteria is to give it a Rate, which is a rate of discount over the length of one period. In this case it’s this cell here,
- we now need to enter the Values and if you click on the first one you’ll see that it tells you here its Value 1, 2 all the way up to 29 of income, equally space in time and occurring at the end of each period.

Now this is very, very important because if you look at our numbers, Period 0 is actually the beginning of Period 1 and then Period 1 that’s the end, the end, the end.

If we assume the values of all these cells we’ll be incorrect because Excel will assume that this 20 000 investment actually occurs at the end of Period 1. So in order to do this is our Value 1

- we highlight all cash flows that happen at the end of the period
- and when we push Ok
- you’ll see we get a NET PRESENT VALUE

However we have to be very careful because what we’ve told it to do, is give us the NET PRESENT VALUE of all the cash flows at the end of the years. We have excluded this, now because it happens at the beginning of Period 1 we don’t have to worry about present valuing it because the number is already present values.

So in order to get an accurate NET PRESENT VALUE, you need to subtract the initial investment.

<ul

- so you’ll see we’ve got a cell here for Initial Investment,
- you make that, equal to the beginning period
- when we push enter
- you’ll see I’ve summed these two,

so the NET PRESENT VALUE of these series of cash flows is actually this number here.

Please fill in your details below to get regular Excel tips and tricks and a free Quick Reference Download!

A common requirement from Excel users is to merge multiple csv files into a single file for use in Excel. In this post we show a very effective way that makes use of the old DOS (you may not even … Continue reading

Excel users typically like to colour cells to highlight certain items. The problem is once you have all the cells highlighted, you will inevitably need to count or add them. There is currently no SUMIF, COUNTIF, AVERAGEIF that works on … Continue reading

I actually told Gavin this morning that I want to send you an e-mail. It was excellent. I have personally asked most of the people who were there what they thought about it, and everybody was really positive and confirmed that they learned a lot.

– Melco Conveyor Equipment (Pty) Ltd