PV of uneven stream of cash flows

[PJF]

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

 

[Niek Otten]

You probably need the XNPV() function. Check Help. If the function is not
available: Tools>>Add-ins, check Analysis Toolpak

 

[PJF]

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})

Where:
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.

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

 

[joeu2004]

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)

You do not need XNPV() for this. PV() will do just
fine. I cannot say why you are unable to access PV().
It is a standard Excel function, not an add-in.

Ostensibly, I would use =PV(10.5%,2,,-4725000) for
the problem above. That yields $3,869,699.64 -- the
same answer you got with XNPV(). It assumese that
10.5% is the APR.

For some reason, it appears 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.

 

[Bruno Campanini]

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})

Where:
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!

 

[joeu2004]

The result I got was: $3,869,699.64

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

I don't know how you got 3,869,990.64. Probably just a
recording error. But both Excel and an HP11C calculator
compute 4725000/(1.105^2) = 3869699.64, which also
matches the PV() function result for annual compouning.
(No surprise.)

 

[PJF]

Niek, Joeu and Bruno,

My sincerest thanks for your very timely, helpful and spirited replies! I
think I now have a pretty good handle on why I was getting so many varying
results which could not be reconciled with "the experts" results at the
accounting firm. Joeu's suggestion that perhaps they were compounding
quarterly most likely looks like what they in fact did since doing so equals
their results precisely. I may have been led astray by the fact that there
is nothing I can find in the contract documents that would have suggested
quarterly compounding.

Gentlemen, again, my sincerest thanks. I am very much in your collective
debt.

Pete

The result I got was: $3,869,699.64

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

I don't know how you got 3,869,990.64. Probably just a
recording error. But both Excel and an HP11C calculator
compute 4725000/(1.105^2) = 3869699.64, which also
matches the PV() function result for annual compouning.
(No surprise.)

 

[Bruno Campanini]

The result I got was: $3,869,699.64

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

I don't know how you got 3,869,990.64. Probably just a
recording error.

Just a typo! Sorry.

Exact value: 3 869 699.64

Bruno

 

[Bruno Campanini]

Niek, Joeu and Bruno,

My sincerest thanks for your very timely, helpful and spirited replies! I
think I now have a pretty good handle on why I was getting so many varying
results which could not be reconciled with "the experts" results at the
accounting firm. Joeu's suggestion that perhaps they were compounding
quarterly most likely looks like what they in fact did since doing so
equals
their results precisely. I may have been led astray by the fact that
there
is nothing I can find in the contract documents that would have suggested
quarterly compounding.

Their convenience?
Very usual with banks and alike.

Gentlemen, again, my sincerest thanks. I am very much in your collective
debt.

Ok Pete, don't worry.
We'll not compound it quarterly.

Ciao
Bruno