question about Net present value way of calculation

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Stig - 19 Jun 2006 15:44 GMT

hello,
i'm trying to reprogram the NPV function as in Excel but i can't seem
to understand how excel gets the result it is giving

for instance in the help i see this example:
annual discount rate: 10%
initial cost: 10000
return first year: 3000
return second year: 4200
return third year: 6800

Can someone explain me (step by step) how excel is giving me the result
of 1188.44?

i thought it was:
3000/1.1 + 4200/1.21 + 6800/1.331 but that gives me something else?

Any help would be appreciated,
Stig

Reply to this Message

lk - 19 Jun 2006 16:35 GMT

I'm getting 11,307.28. I am using .1 as the rate and 3,000, 4,200, 6,800 as
Values 1-3. Are you putting the 10,000 in?

> hello,
> i'm trying to reprogram the NPV function as in Excel but i can't seem
[quoted text clipped - 15 lines]
> Any help would be appreciated,
> Stig

Reply to this Message

kempo - 19 Jun 2006 17:01 GMT

don't bother using preset excel functions for financial calculations as
they are not accurate! they can employ some1 to write a billion lines
of code, but no1 who understands basic financial modelling. you are
best to set out the cashflow etc in an excel and map the individual
calculations

> I'm getting 11,307.28. I am using .1 as the rate and 3,000, 4,200, 6,800 as
> Values 1-3. Are you putting the 10,000 in?
[quoted text clipped - 18 lines]
> > Any help would be appreciated,
> > Stig

Reply to this Message

joeu2004@hotmail.com - 19 Jun 2006 18:22 GMT

> i'm trying to reprogram the NPV function as in Excel but i can't seem
> to understand how excel gets the result it is giving
[quoted text clipped - 8 lines]
> i thought it was:
> 3000/1.1 + 4200/1.21 + 6800/1.331 but that gives me something else?

It is neither. The NPV of the cash flows is -10000 + 3000/(1+10%) +
4200/(1+10%)^2 + 6800*(1+10%)^3, which is 1307.29.

What you describe is the NPV of the cash flows without the initial
cost. If that is what you want, your formula is correct.

It is always a good practice to explain how you use the function you
are asking about. I presume you wrote NPV(10%,-10000,3000,4200,6800).
You should have written
-10000+NPV(10%,3000,4200,6800), which results in 1307.29, or omit
-10000 if you want the cash flow without the initial cost and you get
11307.29, as your discrete formula does.

The "problem" is clear if you read the NPV help text. Excel's NPV()
assumes that the first "value" parameter should be discounted. In
normal NPV usage, that means the first "value" parameter is CF1, not
CF0. ("CFn" is the n-th cash flow.) If you include
-10000 as the first "value" parameter, you are computing the cash flow
0 - 10000/(1+10%) + 3000/(1+10%)^2 + 4200/(1+10%)^3 + 6800*(1+10%)^4,
which is probably not what you want.

Reply to this Message

Stig - 20 Jun 2006 07:25 GMT

thank you so much, joeu2004!
you've cleared the excel mysterie for me!

now it's up to my client to decide how to i should program that
function

thanks again for your effort,
Stig

joeu2004@hotmail.com schreef:

> > i'm trying to reprogram the NPV function as in Excel but i can't seem
> > to understand how excel gets the result it is giving
[quoted text clipped - 29 lines]
> 0 - 10000/(1+10%) + 3000/(1+10%)^2 + 4200/(1+10%)^3 + 6800*(1+10%)^4,
> which is probably not what you want.

Reply to this Message