Introduction to VBA and creating user functions in Excel See

Introduction to VBA and creating user functions in Excel. See 2.4 for general steps. You can use ALT-F11 as a shortcut to get into VBA. Note the function structure on page 40 and the fundamental control structures in Figure 2.8. From HWCE-02, write a VBA function of the form: f = P - A((1 + i)^n - 1)/i(1 + i)^n Using the VBA function, Plot f as a function of A for P=$25,000, i = 8.56%, and n = 6 years. Use the plot to estimate the value of A when f = 0. State your estimated value. Determine more accurately the value of A to 2 decimal places for a series of steps evaluating f for different A. State your rationale for your steps.

Solution

Exxample VBA Function of f is of the form:

(1) VBA Function of A is:

In the excel table enter:

Cell Value

A1 25000

A2 8.56

A3 6

A4 0

In blank cell type the formula:

=A(A1,A4,A2,A3)

Expected value is 214000.

(2) We use the function f for different values of A

like A=214000, i=8.56,n=6,P=25000 we get f=0.0327

A=215000.00, i=8.56,n=6,P=25000 we get f=-0.0527

The rationale is dependent on double value precision.