• Net Present Value Formula (XP)

    Author
    Topic
    #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!!!

    Viewing 0 reply threads
    Author
    Replies
    • #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

      • #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.

      • #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.

      • #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.

        • #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

        • #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

        • #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.

        • #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.

      • #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.

    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: