VBA Macros Help - NPV

[stm1855]

All,
I have been given the following task. For a given number of cash flows, a project cost of capital, and the cash flows themselves, calculate the NPV, IRR, and Payback period. I must create a code that will ask the user what these cash flows are, the project's cost of capital, and the project's life length (in years.) I need a code that will prompt the user to enter these values and calculate the NPV, IRR, and payback period. This code needs to be a "one size fits all" code that can work for any user regardless of how long the project's life is and regardless of the amount of these cash flows. The data NEEDS to be entered using an input box (not on the actual excel sheet.)


If anybody has any help, it would be MUCH appreciated.


Thanks

[SamT]

That's a dozen or so input boxes. That will be very painful for the End User. Why not use a VBA UserForm, very User Friendly?

[stm1855]

Could you explain UserForm a bit more? Realistically, I would like the Macro to be able to ask the user the number of cash flows, and then, for example if the user responds with "6" an additional form would come up with 6 different spaces to fill in the cash flows. Does something like this exist with UserForm?

[SamT]

In the VBA Editor, Press Ctrl+R to insure that the Project Explorer is open. Press F4 to open the Properties mini Window.

While in the VBA Editor, AKA, the VBIDE, in the Tools Menu, select Options >> Editor Tab, and check all the OptionButtons in the "Code Settings" Frame. On the 'General' Tab, check "Break on all errors" and "Show tool tips." Set the rest of the available Options as desired.

In the Project Explorer mini window, Right Click on the ThisWorkbook line of the Project or Workbook that you are working on, then select Insert >> UserForm.

Now that Tool Tips is turned on, mouse over the various icons on the Controls Toolbox floating next to the new blank UserForm to see the names of the Controls represented by the icons.

You will probably want to use TextBoxes for inputs and Labels to "name" the inputs for your Users.

To Preview the UserForm, click on it, then press F5. to close the preview, click the X in the upper Right corner.

The see the Code Page for the UserForm, Right Click it and select "View Code." You can insert certain Sub Stubs by double clicking the UserForm or any Control. You can insert any Event stub by selecting the Control in the left hand dropdown above the Code Page, then selecting the Event Sub in the right hand dropdown. Note: And Event Sub Procedure is ra when that Event happens to that Control. Ex: Click a CommandButton and that CommandButton's Click Event Sub runs.

In the Properties Window, you can select any property and press F1 to see the help on that Property for that Control. You can also select a control on the UserForm and press F1.

The UserForm has a Stub "Initialize." This particular Sub Procedure runs whenever the UserForm is first started (Loaded.)

Usually, I will add one TextBox and one Label to the UF, then set all the Properties and sizes for that pair. Then I will Left Mouse Drag select both, Copy them and paste as many pairs as I need. After positioning each pair, I will set their Tab Index Properties so they can be accessed in order with the Tab Key or the Enter Key.

The next step I take is to edit the Captions of all the Label Controls and rename the Controls they "name" to strongly resemble the Captions. For Example, you want a TextBox Control for the User to enter the number of Cash Flows. The Label Caption might be "Number of Cash Flows Desired." I would name the associated TextBox, "tbxNumCashFlows." I don't bother renaming Labels that are NOT referred to in the Code. On the other hand, suppose I had a Label Control to display the date. I would rename it to "lblDate."

Before writing any code, add all the Controls and insure they are laid out the way you want. Preview the Form. I would start with the Controls for just one set of Cash Flows. You can left mouse drag Select all of them, then Right Click and use the "Group " sub menu to Group them. Then they can be copied or moved as one unit. Note that you will have to rename the pasted Controls. I suggest keeping the same name fors and just adding a numerical suffix. Ex: "tbx_grp1_CashFlowValue_1," "tbx_grp1_CashFlowValue_2," "tbx_grp2_CashFlowValue_1," "tbx_grp2_CashFlowValue_2," or some other organizing scheme. This will Group and alpha sort the Control Names in the Properties Window dropdown.