# Thread: Net Present Value help

1. Registered User
Join Date
Aug 2005
Posts
20

## Unanswered: Net Present Value help

I know there is a way to calculate NPV using a module, but can anyone help me with the code?

I have a field that has the discount rate and then payments by month up to 80 something months, so the discount rate would need to be divided by 12 and I would need to be sure to include all months in order.

I've never done a calculation using VBA, so I'm not exactly sure how it works. Any advice would help.

Thanks

2. Registered User
Join Date
Feb 2004
Location
Chicago, IL
Posts
1,312
Before I can help you with a function for NPV, tell me a little bit more about the calculation. I am not familiar with NPV. Also, where is the data you are doing the calculation on? In a table? Provided by the user?

3. Registered User
Join Date
Aug 2005
Posts
20
Net Present Value, briefly speaking, is discounting future payments back to the present in order to obtain its current value. The way it is in the database right now is that it is that all the future payments are in the database as month1, month2, month3, etc. and the discount rate is in its own field. This calculation in Excel looks like =NPV(Rate/12,month1:month85), of course using cell references instead of names. I need to divide the rate by 12 because they are monthly payments rather than annual payments.

If you can help, I would greatly appreciate it.

4. Registered User
Join Date
Mar 2006
Location
Essex
Posts
5

## My Version of PV Calculation..

This may help? I needed a similar calculation to PV for working on equal fixed to calculate provisions against monthly rental payments for an office equipment portfolio. Since I was evaluating mid-term I had to specify a start and end point (e.g from payment 21 to payment 60)

Function CalcNPV(Rate As Single, Start As Integer,_
Endper As Integer, Paymnt As Currency) As Currency

Dim counter As Integer, c_rate As Double, calctmp As Double

calctmp = 0

counter = Start
c_rate = Rate / 1200

Do While counter < Endper
calctmp = calctmp + (Paymnt * (1 / ((1 + c_rate) ^ counter)))
counter = counter + 1
Loop

CalcNPV = calctmp

End Function

This works reasonably well compared to the Excel function and my pocket financial calculator!

Example of use here would be =calcnpv(10.5,0,59,21.32) which would calculate the present value of a 60 month cashflow from month 0 (the start) of £21.32 assuming an interest rate of 10.5% per annum. The result is 1000.5885 from this calculation, compared with Excel's answer of 1000.1192

You should be able to modify this for a true NPV type calculation if you have irregular cash flows - i.e. you will need to read the next payment from an input table each time you increment the counter.

Hoping this is of use
Kind regards