Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    May 2002
    Location
    Charlotte, North Carolina, USA
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Net Present Value Formula (XP)

    Calling all NPV formula gurus! I have an attorney who has a spreadsheet that is using the NPV formula. He is not getting the expected results. His main clue that he is not getting the correct result is that cell D45, he says, should be the same as B45. AFter reading the NPV formula information, I am not even sure that he is using this correctly. Can someone take a look at the attached spreadsheet and let me know what you think?
    Thank you!!!

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Net Present Value Formula (XP)

    I don't understand why the values in E7 and E8 do not use the NPV calc. If you continue the calc upward, you get different numbers than in these cells.

    The values in B45 and D45 should NOT be the same. If I am going to get $195,3000 a month from now and the interest rate is 8% annual, then the present value of my (future earnings) are 195300/(1+.08/12) = 194,006.62 which is what excel calcs using the NPV number. Essentially it says that the $195k you PLAN to get at March 31 (end of period) is NOW (on March 1) worth $194k.

    If he wants them to be the same he needs a different calc! (if you put =D45 in B45 they will be equal! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>)
    NPV uses value at end of period, so maybe his rows need to be offset.

    What is he trying to calculate?

    Steve

  3. #3
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    Louisville, Kentucky, USA
    Posts
    134
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Net Present Value Formula (XP)

    It looks like he's trying to calc the present value of remaining payments, but his use of the NPV function is careless. Like Steve said, NPV assumes the payment array starts one period after the calculation date. For example, D19 has 4,811,138 and he has it labeled 11/1/04. That $4.8 million is actually the 10/1/04 value of all of the 195,300 payments. So the 194,006 is the 2/1/07 value of the 3/1/07 payment. To move the calc date ahead one month, he can multiply his NPV function by (1+.08/12). So the 11/1/04 value of all of the 195,300 payments is $4.8 million * (1+.08/12) and the 3/1/07 value of the 3/1/07 payment is 194,006*(1+.08/12) = 195,300.

    I also agree that It is very fishy that cells D7 and D8 have hard coded numbers and not the formula. The formula gives considerably larger numbers than what's typed in there. Is the lawyer trying to understate the value of the payments?

    BTW, I hate merged cells.

  4. #4
    Star Lounger
    Join Date
    May 2002
    Location
    Charlotte, North Carolina, USA
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Net Present Value Formula (XP)

    Taking both of your comments and working with him, we have figured it out. The first two rows of data should be ignored. Take a look at this improved attached spreadsheet and look at the formula. Personally, I don't really like doing it this way, but it made him happy and gave him the result he wanted.

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Net Present Value Formula (XP)

    It looks fine.

    What his formula "says" to me is that the "prepayment" is the Royalty received for the month + the "net present value" assuming 8% ann interest of the monthly amounts to be received from next month to March 2007

    If the first 2 rows are to be ignored, why are they there?

    Steve

  6. #6
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    Louisville, Kentucky, USA
    Posts
    134
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Net Present Value Formula (XP)

    Don't sweat it. What you have now is another perfectly acceptable way to move the calc date forward one period.

    Those first two numbers in that column are very misleading. They really shouldn't be there.

Posting Permissions

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