"PJF" wrote in message
k.net...
Niek,

First, thanks for your excellent suggestion.

I installed the Analysis ToolPak and printed out the XNPV help page. The
results were closer than anything I could previously achieve using other
Excel financial worksheet functions. However, I could not replicate the
results of a major accounting firm using the same data I was working with.
BTW, this data and the results are 20 years old but still relevant.

If it's not an inconvenience, I'd like to ask you to review what I did to
see if, perhaps, I made a procedural error which accounts for the current
discrepancy.

The data is as follows:

Principal Payments:

Date Undiscounted
Present Value as of 1/1/85 @ 10.5%/An.
1985-1986
1987 $4,725,000
$3,840,399 (Per the accounting firm)

I used the following formula:
=XNPV(10.5%,{0,0,4725000},{31048,31413,31778})

Whe
The first and second 0's represent no payments and the last payment is the
undiscounted principal due 1/1/87;

The three 5-digit numbers in the second set of brackets are the Excel
numbers representing 1/1/85, 1/1/86 and 1/1/87, respectively.

The result I got was: $3,869,699.64; the result the accounting firm got
was
$3,840,399, a difference of nearly $30,000.

Their calculation is definetely WRONG!!!
Mathematically:
4 725 000 / ((1 + 0.105)^2) = 3 869 990.64

The more appropriate formula in Excel is
=NPV(10.5%,0,4725000)

Ciao
Bruno

PS
joeu2004 speculated on appearing "that the accounting firm
assumed quarterly compounding at the nominal rate of
10.5%. Thus, =PV(10.5%/4,2*4,,-4725000) yields
$3,840,399.92."
He is correct!




Did I misunderstand the use of the XNPV function or fail to follow the
proper procedure to enter the data in it?

Any further assistance would be greatly appreciated.

Kindest regards,

Pete











"Niek Otten" wrote in message
...
You probably need the XNPV() function. Check Help. If the function is not
available: ToolsAdd-ins, check Analysis Toolpak

--
Kind regards,

Niek Otten

"PJF" wrote in message
.net...
I apologize for reposting this question but I may not have originally
stated
the problem adequately.

I have an application that makes a single payment 2 years after the
signing
of a contract. I need to calculate the PV of that payment asof the
date
the
contract is signed based on the discount rate that includes the two
years
during which no payments were made.

Example:

Contract signed 1/1/2005
no payments due 2005 or 2006
principal due in full 1/1/2007
discount rate 5%

Question: how do I calculate the discounted value of the principal from
1/1/2005 until principal payment due date on 1/1/2007, considering
there
are
no payments due either in 2005 or 2006? I know what the PV is but can't
get
to it in Excel.

Any suggestions would be appreciated.

PJF