Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2005

    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.


  2. #2
    Join Date
    Feb 2004
    Chicago, IL
    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. #3
    Join Date
    Aug 2005
    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. #4
    Join Date
    Mar 2006

    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

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts