+ Reply to Thread
Results 1 to 9 of 9

PV of uneven stream of cash flows

  1. #1
    PJF
    Guest

    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




  2. #2
    Niek Otten
    Guest

    Re: PV of uneven stream of cash flows

    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" <pjfnospam@earthlink.net> wrote in message
    news:I1Q8f.1762$2y.22@newsread2.news.atl.earthlink.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
    >
    >
    >




  3. #3
    PJF
    Guest

    Attn:Niek Otten; Re: PV of uneven stream of cash flows --

    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" <nicolaus@xs4all.nl> wrote in message
    news:%23aAAJBM3FHA.3944@TK2MSFTNGP10.phx.gbl...
    > 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" <pjfnospam@earthlink.net> wrote in message
    > news:I1Q8f.1762$2y.22@newsread2.news.atl.earthlink.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
    > >
    > >
    > >

    >
    >




  4. #4
    joeu2004@hotmail.com
    Guest

    Re: Attn:Niek Otten; Re: PV of uneven stream of cash flows --

    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.


  5. #5
    Bruno Campanini
    Guest

    Re: Attn:Niek Otten; Re: PV of uneven stream of cash flows --

    "PJF" <pjfnospam@earthlink.net> wrote in message
    news:StR8f.1788$2y.422@newsread2.news.atl.earthlink.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})
    >
    > 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" <nicolaus@xs4all.nl> wrote in message
    > news:%23aAAJBM3FHA.3944@TK2MSFTNGP10.phx.gbl...
    >> 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" <pjfnospam@earthlink.net> wrote in message
    >> news:I1Q8f.1762$2y.22@newsread2.news.atl.earthlink.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
    >> >
    >> >
    >> >

    >>
    >>

    >
    >




  6. #6
    joeu2004@hotmail.com
    Guest

    Re: Attn:Niek Otten; Re: PV of uneven stream of cash flows --

    "Bruno Campanini" wrote:
    > "PJF" <pjfnospam@earthlink.net> 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.)


  7. #7
    PJF
    Guest

    Many thanks! Re: PV of uneven stream of cash flows --

    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



    "joeu2004@hotmail.com" <joeu2004hotmailcom@discussions.microsoft.com> wrote
    in message news:F7D9C2AC-7F10-4213-A140-AFE0BBF3BA26@microsoft.com...
    > "Bruno Campanini" wrote:
    > > "PJF" <pjfnospam@earthlink.net> 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.)
    >




  8. #8
    Bruno Campanini
    Guest

    Re: Attn:Niek Otten; Re: PV of uneven stream of cash flows --

    "joeu2004@hotmail.com" <joeu2004hotmailcom@discussions.microsoft.com> wrote
    in message news:F7D9C2AC-7F10-4213-A140-AFE0BBF3BA26@microsoft.com...
    > "Bruno Campanini" wrote:
    >> "PJF" <pjfnospam@earthlink.net> 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



  9. #9
    Bruno Campanini
    Guest

    Re: Many thanks! Re: PV of uneven stream of cash flows --

    "PJF" <pjfnospam@earthlink.net> wrote in message
    news:FHU8f.3061$AS6.2091@newsread3.news.atl.earthlink.net...
    > 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



+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1