Hello,

I have the following variable payment schedule, and associated dollar
amounts. What is the best way to determine the present value, using 5%?

November 1, 2007 100
November 1, 2008 100
May 1, 2009 100
November 1, 2009 100
May 1, 2010 100
November 1, 2010 100
May 1, 2011 100
November 1, 2011 100
May 1, 2012 100


TIA!

I think youre missing the CF out. You need to start with cash flowing out
(or some kind of borrowed funds) to finance a project. Try this function:
=XIRR(B1:B10,A1:A10,0.05)

A1:A10, contains the following:
1-Nov-07
1-Nov-07
1-Nov-08
1-May-09
1-Nov-09
1-May-10
1-Nov-10
1-May-11
1-Nov-11
1-May-12

B1:B10 contains the following:
-500
100
100
100
100
100
100
100
100
100

The result is: 30.87%



--
RyGuy


"JEFF" wrote:

Hello,

I have the following variable payment schedule, and associated dollar
amounts. What is the best way to determine the present value, using 5%?

November 1, 2007 100
November 1, 2008 100
May 1, 2009 100
November 1, 2009 100
May 1, 2010 100
November 1, 2010 100
May 1, 2011 100
November 1, 2011 100
May 1, 2012 100


TIA!

Actually, the nine $100 payments represent payments in a no-interest note.
If I use 5%, what is the present value given the uneven payments stream?

Thanks

"ryguy7272" wrote:

I think youre missing the CF out. You need to start with cash flowing out
(or some kind of borrowed funds) to finance a project. Try this function:
=XIRR(B1:B10,A1:A10,0.05)

A1:A10, contains the following:
1-Nov-07
1-Nov-07
1-Nov-08
1-May-09
1-Nov-09
1-May-10
1-Nov-10
1-May-11
1-Nov-11
1-May-12

B1:B10 contains the following:
-500
100
100
100
100
100
100
100
100
100

The result is: 30.87%



--
RyGuy


"JEFF" wrote:

Hello,

I have the following variable payment schedule, and associated dollar
amounts. What is the best way to determine the present value, using 5%?

November 1, 2007 100
November 1, 2008 100
May 1, 2009 100
November 1, 2009 100
May 1, 2010 100
November 1, 2010 100
May 1, 2011 100
November 1, 2011 100
May 1, 2012 100


TIA!

Tricky, because the first period runs for a year (Nov. to Nov.) and then
subsequent periods run for six months each.

I'd break it down as such:
1-Nov-07 100
1-Nov-08 100
2 pmts. at 5% (per year) = PV of $185.94

1-May-09 100
1-Nov-09 100
1-May-10 100
1-Nov-10 100
1-May-11 100
1-Nov-11 100
1-May-12 100
7 pmts. at 2.5% (per year) = PV of $634.94

Sum the two PVs together to get $820.88

Does that help?
Ryan---


--
RyGuy


"JEFF" wrote:

Actually, the nine $100 payments represent payments in a no-interest note.
If I use 5%, what is the present value given the uneven payments stream?

Thanks

"ryguy7272" wrote:

I think youre missing the CF out. You need to start with cash flowing out
(or some kind of borrowed funds) to finance a project. Try this function:
=XIRR(B1:B10,A1:A10,0.05)

A1:A10, contains the following:
1-Nov-07
1-Nov-07
1-Nov-08
1-May-09
1-Nov-09
1-May-10
1-Nov-10
1-May-11
1-Nov-11
1-May-12

B1:B10 contains the following:
-500
100
100
100
100
100
100
100
100
100

The result is: 30.87%



--
RyGuy


"JEFF" wrote:

Hello,

I have the following variable payment schedule, and associated dollar
amounts. What is the best way to determine the present value, using 5%?

November 1, 2007 100
November 1, 2008 100
May 1, 2009 100
November 1, 2009 100
May 1, 2010 100
November 1, 2010 100
May 1, 2011 100
November 1, 2011 100
May 1, 2012 100


TIA!

On Nov 1, 3:23 pm, JEFF wrote:
I have the following variable payment schedule, and associated dollar
amounts. What is the best way to determine the present value, using 5%?

"Best"? I always have a problem with questions like that because
"best" is a matter of opinion.

Here are a few ways. The first two view the cash flows as semi-annual
with a zero cash flow in May 2008. Assume the following structu

A1: November 1, 2007
A2: May 1, 2008
A3: November 1, 2009
(etc)
A10: May 1, 2012

B1: 100
B2: 0
B3: 100
(etc)
B10: 100

Then one of the following might work for you:

=npv(5%/2, B3:B10)/(1+5%/2) + 100

=sumproduct(pv(5%/2, row(B1:B10)-row($B$1), 0, -B1:B10))

Both return the same result, namely: 799.53. The yucky adjustment of
NPV is due to Excel's (Lotus's or Visicalc's?) dubious definition of
NPV. See the math formula in the NPV Help page.

Note: Some might say that the rate, 5%/2, should be (1+5%)^0.5-1 or
RATE(2,0,-1,1+5%). I would agree. But many/most academcian and
financial professionals would use 5%/2. Go figure!

Alternatively:

=xnpv(5%, B1:B10, A1:A10)

(With XNPV, you could omit the row for May 2008.) That results in
800.61.

The difference of $1.08 can be explained by looking at the math
formulas for NPV and XNPV on their respective Help pages. The largest
difference is due to using 5%/2 with NPV instead of the alternative
noted above. That still results in some small difference because XNPV
computes actual calendar days between dates, whereas for NPV, I
assumed equal periods of "every 6 months". Both are equally
"correct"; that is, consistent with what financial professionals do.

HTH.

PS....

On Nov 2, 11:44 am, I wrote:
=npv(5%/2, B3:B10)/(1+5%/2) + 100
=sumproduct(pv(5%/2, row(B1:B10)-row($B$1), 0, -B1:B10))

I finally figured out the correct syntax so that you could use
constants instead of putting the cash flows into a range of cells.

=npv(5%/2, {100;100;100;100;100;100;100;100})/(1+5%/2) + 100

=sumproduct(pv(5%/2, row(B1:B10)-row($B$1), 0, -
{100;0;100;100;100;100;100;100;100;100}))

Even though that refers to B1:B10, nothing useful or relevant needs to
be in those cells.

Note: You can use commas instead of semicolons for NPV. But for
SUMPRODUCT, you must use semicolons.

Also, I wrote:
The yucky adjustment of NPV is due to Excel's (Lotus's
or Visicalc's?) dubious definition of NPV. See the math
formula in the NPV Help page.

I was referring to dividing by (1+5%/2) instead of (1+5%), as you
might expect. But __some__ adjustment of NPV would still be needed in
order to account for the "missed" period (May 2008).

JEFF, use joeu2004's method. I discounted the very first CF. Now that I
think about it, it doesn't make since because it is an immediate CF, not a
future CF.
Sorry,
Ryan---


--
RyGuy


"joeu2004" wrote:

PS....

On Nov 2, 11:44 am, I wrote:
=npv(5%/2, B3:B10)/(1+5%/2) + 100
=sumproduct(pv(5%/2, row(B1:B10)-row($B$1), 0, -B1:B10))

I finally figured out the correct syntax so that you could use
constants instead of putting the cash flows into a range of cells.

=npv(5%/2, {100;100;100;100;100;100;100;100})/(1+5%/2) + 100

=sumproduct(pv(5%/2, row(B1:B10)-row($B$1), 0, -
{100;0;100;100;100;100;100;100;100;100}))

Even though that refers to B1:B10, nothing useful or relevant needs to
be in those cells.

Note: You can use commas instead of semicolons for NPV. But for
SUMPRODUCT, you must use semicolons.

Also, I wrote:
The yucky adjustment of NPV is due to Excel's (Lotus's
or Visicalc's?) dubious definition of NPV. See the math
formula in the NPV Help page.

I was referring to dividing by (1+5%/2) instead of (1+5%), as you
might expect. But __some__ adjustment of NPV would still be needed in
order to account for the "missed" period (May 2008).

THANKS!

"joeu2004" wrote:

On Nov 1, 3:23 pm, JEFF wrote:
I have the following variable payment schedule, and associated dollar
amounts. What is the best way to determine the present value, using 5%?

"Best"? I always have a problem with questions like that because
"best" is a matter of opinion.

Here are a few ways. The first two view the cash flows as semi-annual
with a zero cash flow in May 2008. Assume the following structu

A1: November 1, 2007
A2: May 1, 2008
A3: November 1, 2009
(etc)
A10: May 1, 2012

B1: 100
B2: 0
B3: 100
(etc)
B10: 100

Then one of the following might work for you:

=npv(5%/2, B3:B10)/(1+5%/2) + 100

=sumproduct(pv(5%/2, row(B1:B10)-row($B$1), 0, -B1:B10))

Both return the same result, namely: 799.53. The yucky adjustment of
NPV is due to Excel's (Lotus's or Visicalc's?) dubious definition of
NPV. See the math formula in the NPV Help page.

Note: Some might say that the rate, 5%/2, should be (1+5%)^0.5-1 or
RATE(2,0,-1,1+5%). I would agree. But many/most academcian and
financial professionals would use 5%/2. Go figure!

Alternatively:

=xnpv(5%, B1:B10, A1:A10)

(With XNPV, you could omit the row for May 2008.) That results in
800.61.

The difference of $1.08 can be explained by looking at the math
formulas for NPV and XNPV on their respective Help pages. The largest
difference is due to using 5%/2 with NPV instead of the alternative
noted above. That still results in some small difference because XNPV
computes actual calendar days between dates, whereas for NPV, I
assumed equal periods of "every 6 months". Both are equally
"correct"; that is, consistent with what financial professionals do.

HTH.