Calculate Net Present Value with VBA

[Haneyg]

Hey guys, I need to calculate the NPV using a bit of VBA code. I think I could figure it out if there were a set amount of variables, but the trick is to have a macro ready to run and calculate for any "n" number of cashflows. Optimally I would just import the data into excel and run the macro. Here is the email I received from the client for reference:

I need a program that computes the NPV for n cashflows using VBA in an Excel spreadsheet. I'll input how many cashflows (time periods – in years) that will be entered and the interest rate. Once the data has been entered I want a button to calculate the NPV. Thanks.

I was thinking of attempting to count cells to find out how many total cashflows, but I'm not sure that would even work.

Thanks for the help guys!

 

[tusharm]

You need to invest some time and energy understanding, together with your client, how the inputs will be defined to the code. The state of software development today does not allow for code that will read the client's mind as to what cashflows and what interest rate to use!

Wouldn't that restrict the inputs to only cells A1:A5? The point of creating this via VBA is to solve for any number of inputs.

For example, if one annuity has 7 cashflows then cell range A1:A5 wouldn't work as it would only capture the first 5 cashflows.

Hasn't anyone done this before? It's not really a hard concept but I just don't know enough VBA to write it. Isn't there another piece of code for any other macro which accounts for dealing with "n" number of variables?

Thanks for the replies.

 

[tusharm]

FWIW, suppose the cash flows are in A (and the only numbers in A will be the c.fs.) and the rate is in B1, then the formula =NPV(B2,A:A) will give you the NPV irrespective of how many values there are in A -- and it will be immediate so your client will get the result instantenously w/o having to press any button.

 

[Haneyg]

Thank you both for your assistance. I'm still not quite sure how to handle the original intent for variable cashflows, but I was able to just throw this to the client. It's extremely simply and uses input boxes, but I haven't heard complaint so I guess it worked.

Thanks again.

Option Explicit
Sub NetPresentValue()

'clear any previous cell contents
Range("B4:XFD8").Select
Selection.ClearContents
Range("B11").Select

'interest rate
Dim irate As Double
'present value
Dim pv As Double
'future value
Dim fv As Double
'net present value
Dim npv As Double
'variable cashflows
Dim n As Integer
'variable i
Dim i As Integer

'user-defined inputs
irate = InputBox("Enter the Interest Rate (compounded annually) in decimal format")
fv = InputBox("Enter the Future Value")
n = InputBox("Enter the total number of cashflows")

'set the cell range for spreadsheet input & calculations
With Range("B4")
For i = 1 To n
pv = fv / (1 + irate) ^ i

'Insert values into spreadsheet
.Cells(1, i) = "Year " & i
.Cells(2, i) = irate
.Cells(3, i) = fv
.Cells(4, i) = (1 + irate) ^ i
.Cells(5, i) = pv
Next i

'display npv to user
MsgBox "The Net Present Value is $" & Range("B10")

End With
End Sub