Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
Solved

# Excel NPV

Posted on 2003-03-12
Medium Priority
2,910 Views
I have been asked to calculate a mid-year NPV for my project. Apparently excel only calculates NPV at year end with the NPV formula. How can I calculate a mid-year NPV in excel?

Thanks

DC
0
Question by:jackdog
• 2

LVL 50

Expert Comment

ID: 8125957
Two ways

(1) Use XNPV which allows a date range

You need to enable the Analysis Toolpak add-in to use this formula

(2) Derive the NPV manually, cashflow by cashflow

ie if the discounting is at month 6, month 18, month 30 etc then at a 10% disocount rate the respective discount factors are 1/(1+10%)^(6/12) = 0.95 etc

Discounted Cashflow 1 = Cashflow 1 * 0.95
Discounted Cashflow 2 = Cashflow 2 * 0.87
Discounted Cashflow 2 = Cashflow 3 * 0.79

Are you familiar with NPV? Let me know if I need to explain more

Cheers

Dave
0

Author Comment

ID: 8126846
The XNPV looks complex, I'd prefer not to go down that path.

So if I discount each annual cashflow by the number of half years from today and sum the results then I have a mid-point NPV?

Thanks

DC

0

LVL 50

Accepted Solution

Dave Brett earned 1200 total points
ID: 8127140
Yes

As a check, you can compare the year ending excel NPV equation to the manual one

Discount factor  year 1 = 1/(1+10%)^1 = 0.91   (1 = 12/12 months)
Discount factor  year 2 = 1/(1+10%)^2 = 0.83

etc

I prefer the manual method as it is more transparent than a black box NPV formula

Cheers

Dave
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
Lost Word File? Eagerly, need it back? Read ahead; this File Recovery guide is for you.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…