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.
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?
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.
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
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.