EXCELlent Ones Consulting Skip to Content Skip to Search

Print  |   | Download PDF

CompoundInt

Have you ever heard of the function CompoundInt, no. That’s because I made it up, well not really. Microsoft Excel gives people the ability to create their own functions\formulas. This is called UDF (User Defined Functions). UDF’s are used to miniaturize and distribute complex calculations. In order to create a UDF knowledge of VBA (Visual Basic for Application) and the VBE (Visual Basic Editor) is needed. Below is an example of a custom function created using VBA.

Very often when people try to determine compound interest it is a protracted process of adding interest upon the last result. . A better approach would be to create a function which automatically does this right away. The custom function has been called CompoundInt short for Compound Interest.

In order to use the function the following four variables are needed:

1. Principal – beginning amount

2. Rate – interest rate

3. CompountFQY – number of times interest accumulates per period

4. Term - the number of periods (ex. Years)

5. The syntax for the CompoundInt function is as follows

a. CompoundInt(Principal, Rate,CompoundFQY, Term)

6. Above the result for a $2,000 investment compounding once annually at 3% for five years will yield a result of $2,319.

7. To double check let’s use the most common method of arriving at compound interest.

b. A small table has been created showing the principal amount and column for each year

8. Multiply the principal amount by 1.03 (Multiplying by 1 will get the principal amount and .03 will get the interest earned)

9. Next the formula has been copied to the future years

a. Ex. The formula in cell D9 is C9*1.03

10. Notice the result in the 5th year is the same as our CompoundInt function


To Download the CompoundInt Add-In

1. Click the Download link to receive the Excel Add-In. Save it in a location where it will not be moved or deleted. Download

2. Click on the Office Button

3. Click Excel Options

4. Click Add-Ins

5. Click on Go once the Excel Add-ins has been selected in the Manage area

6. An Add-Ins window appears, Click the Browse button to locate the Add-In just downloaded

7. Navigate to the location where the previously downloaded Add-in was saved.

8. Select the Add-In file CompoundInt

9. Click Open

10. Notice the CompoundInt add-in has been added to your list and is checked off to denote usage

11. Click Ok, the function is now ready to be used on that computer.

Please keep in mind this method of including an Add-in to Excel restricts its use to the computer where the Add-In is saved. In future installments we will share more about UDF’s.


Footer

Email: info@excellentones.com | P.O. Box 160493 | Brooklyn, NY 11216 | Phone: (347) 763-1035 | Fax: (718) 228-7859

All rights reserved. Copyright 2014 Excellent Ones Consulting LLC.