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!

 

[Haneyg]

Here is an example of NPV usage with VBA for a static set of variables:

<code>Dim Fmt, Guess, RetRate, NetPVal, Msg
Static Values(5) As Double    ' Set up array.
Fmt = "###,##0.00"    ' Define money format.
Guess = .1    ' Guess starts at 10 percent.
RetRate = .0625    ' Set fixed internal rate.
Values(0) = -70000    ' Business start-up costs.

' Positive cash flows reflecting income 
' for four successive years.
Values(1) = 22000 : Values(2) = 25000
Values(3) = 28000 : Values(4) = 31000

' Calculate net present value.
NetPVal = [B]NPV([/B]RetRate[B],[/B] Values()[B])[/B]

Msg = "The net present value " & _
      "of these cash flows is "
Msg = Msg & Format(NetPVal, Fmt) & "."

' Display net present value.
MsgBox Msg 
</code>
</pre>

What I need is something that would pull all data from n number of inputs and calculate the total NPV, see above post for details.

Thanks again everyone.

 

[Haneyg]

If no one knows how to accomplish this here can anyone point me in the right direction to another site or forum?

Thanks.

 

[parry]

Hi, Excel has an NPV function - are you calculating in a different way?

If you click a button you need to indicate what range of cells the code needs to evaluate. If you want your own custom formula then you can create a user defined function (eg use Function instead of Sub when writing the code) and this can be used like any other formula. You can use worksheet events to monitor what is entered in cells and then perform an action accordingly.

In all cases you need to pass the procedure/function will need to know what cells to evaluate and what variable values need to be evaluated.

To me it sounds like the native Excel formula may do. Perhaps your user doesn't know this formula exists?

 

[Haneyg]

I am familiar with the NPV function and notified the client about its use prior to posting here on the forum. Unfortunately, the client replied with the message I copied into message 1 of this thread. Apparently he wants a macro and a button control.

Thanks for the replies.

 

[parry]

OK, well I guess the first step is to define what constitutes a cashflow so you can define what info to grab and process each accordingly. Such as whether the data is continuous, in different sheets, is entered at the time etc and potentially what data element/s tells you this cashflow is different from another.

Good luck.

 

[Haneyg]

So there is no way to modify the example I posted in message 2 to comply with n number of variables rather than static?

 

[parry]

You miscontrue. You havent indicated what the input will be & I dont know what you have done for the NetPVal function so not sure if it accepts a range or only an array but....

Sub YourData()
MyNPV Range("A1:A5")
End Sub

Sub MyNPV(Rng As Range)
Dim Fmt, Guess, RetRate, NetPVal, Msg
Fmt = "###,##0.00" ' Define money format.
Guess = 0.1 ' Guess starts at 10 percent.
RetRate = 0.0625 ' Set fixed internal rate.

' Calculate net present value.
NetPVal = NPV(RetRate, Rng)

Msg = "The net present value " & _
"of these cash flows is "
Msg = Msg & Format(NetPVal, Fmt) & "."

' Display net present value.
MsgBox Msg
End Sub

 

[Haneyg]

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.

 

[parry]

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.

Of course the range could be anything you want. Have you determined how the inputs will be captured - will you ask the users to populate data into column A, a form that asks the user to identify the range etc. You haven't indicated the input capture, so how do you expect people to help you? i.e. answer my question "the first step is to define what constitutes a cashflow so you can define what info to grab and process each accordingly."

With Excel you can look at any number of rows but you need to know that Column A means x input, column B means Y input etc unless you have built a form where users input the variables. In your words ... It's not really a hard concept.