Compound interest calculation in Excel using a formula or a macro function

Compound interest calculation in Excel is of great interest in personal and business situations.

Compound interest is the amount that a dollar invested now will be worth in a given number of periods at a given compounded interest rate per period.
Although Microsoft Excel does not include a function for determining compound interest, you can use the following formula for this calculation:
=PV*(1+R)^N
where PV is present value, R is the interest rate, and N is the number of investment periods. The '^' sign is known as 'to the power of'. If you have a value say, 20, and it is to be raised by the power of 5 then it just means that the value is '20x20x20x20x20' or 3200000 in simple language.
Suppose you have $200000.00 in an investment account. The account pays 10.5 percent interest and this interest is compounded annually. How much will the investment be worth at the end of five years? What will be the total compounded interest?
There are two ways to find the amount:
• Use a Fixed Formula
• Create a Function Macro to Determine Compound Interest

Use a Fixed Formula
The following formula typed into a cell on a worksheet, returns the correct value of 329489.3532:
=200000*(1+.0.105)^5
This means your investment of 200000 units will be worth 329489.3532 units after 5 years and the total compunded interest will be equal to 329489.3532-200000= 129489.35 (rounded off to 2 decimal places). However, all of the information is 'hard-coded' into the formula and you must manually change the formula any time the figures change.

Create a Function Macro to Determine Compound Interest:
A custom function is more flexible because none of the actual raw data is 'hard-coded' into the function; the user just types the data for the calculation instead of the actual calculation.
To create this custom function, follow these steps:
1. Start Microsoft Excel.
2. Click on 'Tools' in the menu bar, select Macro and click finally on 'Visual Basic Editor' or just press 'Alt+F11' to start the Visual Basic Editor
3. On the Insert menu, click Module.
4. Type the following code in the new module:
Function Compound_Interest(PV As Double, R As Double, N As Double) As Double
Compound_Interest = PV*(1+R)^N 'Performs computation
End Function

To use the custom function, follow these steps:
1. Type the following values in your worksheet:

Cell		Value
A3		200000.00
B3		.10.5
C3		5

These values represent the following:
• A3: Present value of the investment
• B3: Interest rate
• C3: Number of investment periods
2. In any blank cell, type the following formula
=Compound_Interest(A3,B3,C3)
where A3, B3, and C3 are the cells that contain the present value, interest rate, and number of investment periods respectively.
The cell in which you typed the formula displays 329489.3532. This is the amount your original investment of 200000.00 is worth after 5 investment periods at 10.5 percent compound interest.

Compound interest calculation in Excel