Replies

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.

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.

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.

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.

Quote, “OK, using your logic, what if Sales in ’02 is zero?” That’s undefined whether you’re using my logic or Steve’s.
Quote,”if you start having NEGATIVE to compare you get squirrelly numbers” It’s only squirrelly because most people are accustomed to dealing with positive numbers only (i.e. physical measurements where an absolute zero exists). I take back my earlier statement about business financials being in this physical world, because negative numbers happen, the most obvious case being profits. A negative percentage change only means that the sign of the change is different than the sign of the starting number. Because -300=-100*3, -300 is a +200% change from -100. Most people have learned to interpret that mathematical statement as -300 is 200% greater than -100 and that’s where the misunderstandings come in.

Quote, “OK, using your logic, what if Sales in ’02 is zero?” That’s undefined whether you’re using my logic or Steve’s.
Quote,”if you start having NEGATIVE to compare you get squirrelly numbers” It’s only squirrelly because most people are accustomed to dealing with positive numbers only (i.e. physical measurements where an absolute zero exists). I take back my earlier statement about business financials being in this physical world, because negative numbers happen, the most obvious case being profits. A negative percentage change only means that the sign of the change is different than the sign of the starting number. Because -300=-100*3, -300 is a +200% change from -100. Most people have learned to interpret that mathematical statement as -300 is 200% greater than -100 and that’s where the misunderstandings come in.

In Excel 97, there’s a function called NETWORKDAYS. It may be part of the Analysis ToolPak. I’m not sure if it’s different in Excel 2000, but if you go to ToolsAdd-Ins, you might see a check box for the ‘Analysis ToolPak’.

In Excel 97, there’s a function called NETWORKDAYS. It may be part of the Analysis ToolPak. I’m not sure if it’s different in Excel 2000, but if you go to ToolsAdd-Ins, you might see a check box for the ‘Analysis ToolPak’.

I would disagree with your disagreement.

In the mathematical world, there’s no such thing as absolute zero. That only exists in a physical world, which I suppose is where people who report sales numbers operate. In mathematical terms p=(x-y)/y is the percentage change from y to x. If y is negative and x>y, then p is going to be negative even though x>y. It’s correct math and I’m glad Excel works that way. I’m a math major who works in a business setting so I’ve had to explain this concept to people before and it’s always tough to explain to non numeric folks.

I would disagree with your disagreement.

In the mathematical world, there’s no such thing as absolute zero. That only exists in a physical world, which I suppose is where people who report sales numbers operate. In mathematical terms p=(x-y)/y is the percentage change from y to x. If y is negative and x>y, then p is going to be negative even though x>y. It’s correct math and I’m glad Excel works that way. I’m a math major who works in a business setting so I’ve had to explain this concept to people before and it’s always tough to explain to non numeric folks.

What do you want the answer to be? From a strict mathematical viewpoint, Excel’s answer is correct since a negative percentage change indicates that the change of +15,000 is a different sign from the base number of -3000. If you always want sales increases to be positive and decreases to be negative, you could do something like: =if(d17=0,0,if(c17<d17,-1,1)*abs((c17-d17)/d17)). In your example, this would give you an answer of +500%. The problem with this is that a change from +2000 to +12000 is also a change of +500%. This is not really an Excel problem. I think the problem is that the true mathematical answer is counterintuitive.

Also, I'm not sure why you have "0" in your if statement. Do you want a numeric zero or a text zero?

What do you want the answer to be? From a strict mathematical viewpoint, Excel’s answer is correct since a negative percentage change indicates that the change of +15,000 is a different sign from the base number of -3000. If you always want sales increases to be positive and decreases to be negative, you could do something like: =if(d17=0,0,if(c17<d17,-1,1)*abs((c17-d17)/d17)). In your example, this would give you an answer of +500%. The problem with this is that a change from +2000 to +12000 is also a change of +500%. This is not really an Excel problem. I think the problem is that the true mathematical answer is counterintuitive.

Also, I'm not sure why you have "0" in your if statement. Do you want a numeric zero or a text zero?