PV of uneven stream of cash flows

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

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

--
Kind regards,

Niek Otten

"PJF" <[email protected]> wrote in message
news:[email protected]...
>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,

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











"Niek Otten" <[email protected]> wrote in message
news:%[email protected]...
> You probably need the XNPV() function. Check Help. If the function is not
> available: Tools>>Add-ins, check Analysis Toolpak
>
> --
> Kind regards,
>
> Niek Otten
>
> "PJF" <[email protected]> wrote in message
> news:[email protected]...
> >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
> >
> >
> >
>
>

PJF wrote:
> 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.

"PJF" <[email protected]> wrote in message
news:[email protected]...
> 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!



>
> 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" <[email protected]> wrote in message
> news:%[email protected]...
>> You probably need the XNPV() function. Check Help. If the function is not
>> available: Tools>>Add-ins, check Analysis Toolpak
>>
>> --
>> Kind regards,
>>
>> Niek Otten
>>
>> "PJF" <[email protected]> wrote in message
>> news:[email protected]...
>> >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
>> >
>> >
>> >
>>
>>
>
>

"Bruno Campanini" wrote:
> "PJF" <[email protected]> wrote:
> > 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.)

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



"[email protected]" <[email protected]> wrote
in message news:[email protected]...
> "Bruno Campanini" wrote:
> > "PJF" <[email protected]> wrote:
> > > 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.)
>

"[email protected]" <[email protected]> wrote
in message news:[email protected]...
> "Bruno Campanini" wrote:
>> "PJF" <[email protected]> wrote:
>> > 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

"PJF" <[email protected]> wrote in message
news:[email protected]...
> 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