PV function in microsoft excel

Question

Hi, 

 How does PV function work if nper is not an integer? Say my rate is an annual number (1.75%), nper is 2.5, pmt =-1 and payments are made end of period (or in other words say my formula = PV(1.75%, 2.5, -1). How does microsoft come up with a present value of 2.454 for this problem. I am not able to reproduce the results using the following formula 1/(1+.0175)^1+ 1/(1+.0175)^2 +.5/(1+.0175)^2.5. Thanks.

Answer 1

The math formula is in the PV help page, shown below together with a demonstration of the calculation.

The formula in A5 is based on the math formula above, solving for PV.  Note that "type" and "fv" are zero.

I did not try to figure out your mistake.

[EDIT] I don't know what happen to the image. But you can find the mathematical in PV support page at

https://support.office.com/en-us/article/PV-function-23879d31-0e02-4321-be01-da16e8168cbd . And I just "restored" the image. We'll see how long it lasts.  ;-)

Answer 2

SulsiCewl wrote:

[....] my formula = PV(1.75%, 2.5, -1). How does microsoft come up with a present value of 2.454 for this problem. I am not able to reproduce the results using the following formula 1/(1+.0175)^1+ 1/(1+.0175)^2 +.5/(1+.0175)^2.5.

---

Okay. I think I see what you are asking:  Why isn't the NPV of the cash flows equal to the PV function result?

In part, it is because you misrerpresent the cash flows and the discount factors, at least in terms of the PV function model.

The PV function assumes equal cash flows at equal intervals. So if you assume there are 3 cash flows, their frequency must be 2.5/3, not 1, 1 and 0.5, and not 1, 1 and 1.

So ostensibly (but incorrectly), the NPV of a payment of -1 at 1.75% for each of 3 payments might be the negative of:

1/(1+1.75%)^(2.5/3) + 1/(1+1.75%)^(2.5*2/3) + 1/(1+1.75%)^2.5 = 2.91470192916266

But that still differs from PV(1.75%, 2.5, -1) = 2.42539998226368 . Why?

The fallacy is in dividing 2.5 into 3 cash flows, in the first place.

The fact is: in order to calculate NPV, we would need 2.5 equal cash flows, not 3. And that is inconsistent with the NPV model, which has a cardinal number of cash flows.

So the reason why the PV and NPV functions do not return the same amounts is because they have very different models.

It is analogous to comparing Excel XIRR for equal payments at the end of each month with Excel IRR. The two will never be the same because of different assumptions.

Answer 3

Thank you Joeu2004 for all your reaponses! I found them very useful!