Decipher please

[cybhunter]

Can Someone help me understand how the statement below works?
Thanks in advance!

Me.txtproratedlumpsum = PV(Val(Me.txtmonthlyinterestrate), Val(Me.txttermleft) - 1, -Nz(Me.Monthly)) + Nz(Me.Monthly)

 

[Kiwiman]

Howzit

This is the PV (Present Value) calculation a - built in function.

Returns the present value of an investment: the total amount that a series of future payments is worth now

Search on help for what the calculation does, and the paramters required.

Your calculation is getting the parameters from the associated form.

 

[gemma-the-husky]

in principle you are dealing with compund interest (or inflation, if you like)

ie a sum now, compunds into a larger sum after a period, depending on the interest rate

an annuity is similar but takes a series of periodic payments, each compunded ( eg an endowment)

eg

you can calculate how much you need to invest monthly at an assumed interest rate, to provide a sum of a certain amount at a point in the future ...

or you can calculate the present value of a future lump sum (or series of payments) allowing for inflation. ie the equivalent of getting £500/month for 10 years (£6000 altogether) is less, if you were getting it all in one go, now

 

[cybhunter]

I get the concept of PV. I guess what I am looking for is more the syntax of what is happening. Wht is the "-1" for, what is the "-Nz" for. I realize there are 3 values there, the interest, how many payments are left, and the payment amount. I am kinda looking for the A + B + C = D help.
Thanks!

 

[cybhunter]

Anyone?

 

[WayneRyan]

cybhunter,

The Nz() function removes the possibility of Null values. If there is no entry
in the textbox Me.Monthly, the Nz function will provide a 0.

The minus one is just subtracting 1 from the value of the textbox
called Me.txttermleft. I don't know if that's days or whatever. But, it
is just subtracting 1.

Wayne

 

[georgedwilkinson]

Typically when you see "-1" in a financial calculation, you are calculating a percent remaining.

So 20% - 1 = 80%.

With regards to "-Nz", the programmer was trying to make the non-null value negative so the function had the correct sign going in. A negative value means cash is going "away", which is exactly what is happening when you are sending a payment to someone else.

Did you look up PV? The Excel help on PV is (or used to be in 1995) very detailed.