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.