Results 1 to 6 of 6
Thread: Net Present Value Formula (XP)

20030924, 14:40 #1
 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!!!

20030924, 15:36 #2
 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

20030924, 16:48 #3
 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.

20030924, 16:51 #4
 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.

20030924, 17:06 #5
 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

20030924, 19:29 #6
 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.