XL: How to Calculate Compound Interest
Suppose you have $1,000.00 in an investment account. The account pays 8 percent interest and this interest is compounded annually. How much will the investment be worth at the end of three years? There are two ways to find the amount:
PV*(1+R)^N
where PV is present value, R is the interest rate, and N is the number of investment periods.
Use a Fixed Formula
The following formula typed into a cell on a worksheet, returns the correct value of $1,259.71:
=1000*(1+.08)^3
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
Function Yearly_Rate(PV As Double, R As Double, N As Double) As Double
Yearly_Rate = PV*(1+R)^N ‘Performs computation
End Function