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

Dana wrote:

Sub Demo()

    Const R = 0.0175    

    With WorksheetFunction

        Debug.Print 1 / (1 + R) ^ 1 + 1 / (1 + R) ^ 2 + 1 / (1 + R) ^ 2.5

        Debug.Print .NPV(R, Array(1, 1, 1*Sqr(1 + R)))

    End With

End Sub

Returns:

 2.90625425490595

 2.90625425490595

Of course, that's tautological.  The first formula is simply how the NPV function is calculated.  Note that the last term of the NPV function evaluation is (1+R)^0.5 / (1+R)^3 = (1+R)^0.5 * (1+R)^-3 = (1+R)^-2.5 = 1 / (1+R)^2.5.

Moreover, 2.90625425490595 is not the same as 2.42539998226368 = PV(1.75%, 2.5, -1).

SulsiCewl was asking why not, and how the latter is evaluated differently.

[EDIT] The answer is:  your first formula does not correctly model the __PV__ calculation for non-integer nper. Specifically, it does not model __2.5__ equally-spaced payments of -1. We count cardinally (read: integers). We can transform the problem into __3__ equally-spaced payments with a total duration of 2.5 periods, as I did in a previous response; but not __2.5__ equally-spaced payments.