• Net Present Value Formula (XP)

    Home » Forums » AskWoody support » Productivity software by function » MS Excel and spreadsheet help » Net Present Value Formula (XP)

    Author
    Topic
    WSmnehring
    AskWoody Lounger
    September 24, 2003 at 2:40 pm #394075

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

    Reply | Quote
    Viewing 0 reply threads
    Author
    Replies
    • WSsdckapr
      AskWoody Lounger
      September 24, 2003 at 3:36 pm #719389

      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! grin)
      NPV uses value at end of period, so maybe his rows need to be offset.

      What is he trying to calculate?

      Steve

      Reply | Quote
      • WSchipshot
        AskWoody Lounger
        September 24, 2003 at 4:48 pm #719429

        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.

        Reply | Quote
      • WSchipshot
        AskWoody Lounger
        September 24, 2003 at 4:48 pm #719430

        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.

        Reply | Quote
      • WSmnehring
        AskWoody Lounger
        September 24, 2003 at 4:51 pm #719433

        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.

        Reply | Quote
        • WSsdckapr
          AskWoody Lounger
          September 24, 2003 at 5:06 pm #719445

          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

          Reply | Quote
        • WSsdckapr
          AskWoody Lounger
          September 24, 2003 at 5:06 pm #719446

          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

          Reply | Quote
        • WSchipshot
          AskWoody Lounger
          September 24, 2003 at 7:29 pm #719533

          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.

          Reply | Quote
        • WSchipshot
          AskWoody Lounger
          September 24, 2003 at 7:29 pm #719534

          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.

          Reply | Quote
      • WSmnehring
        AskWoody Lounger
        September 24, 2003 at 4:51 pm #719434

        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.

        Reply | Quote
    Viewing 0 reply threads
    Reply To: Net Present Value Formula (XP)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information:




DON'T MISS OUT!
Subscribe to the Free Newsletter
We promise not to spam you. Unsubscribe at any time.
Invalid email address

View the Forum

  • Recent Replies
  • My Replies
  • My Active Topics
  • New Posts in the Last day
  • Private Messages
  • Knowledge Base
  • How to use the Forums
  • All Forums

    • Recent Topics

    My Profile

    May 2025
    S M T W T F S
     123
    45678910
    11121314151617
    18192021222324
    25262728293031

    Remembering Woody

     

    Want to Advertise in the free newsletter? How about a gift subscription in honor of a birthday? Send an email to sb@askwoody.com to ask how.

    Mastodon profile for DefConPatch
    Mastodon profile for AskWoody

     

    Home • About • FAQ • Posts & Privacy • Forums • My Account
    Register • Free Newsletter • Plus Membership • Gift Certificates • MS-DEFCON Alerts

    Copyright ©2004-2025 by AskWoody Tech LLC. All Rights Reserved.