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
will be worth in a given number of periods at a given compounded
interest rate per period.
Use a Fixed Formula
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
|To use the custom function, follow these steps:
1. Type the following values in your worksheet:
• A3: Present value of the investment
• B3: Interest rate
• C3: Number of investment periods
2. In any blank cell, type the following formula
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.
Further reading on compound interest calculations
|Compound interest calculation in Excel|