Simple Formula for Converting Compound Interest Rates

This workbook has a simple formula to convert compound interest rates.

=IF(t>500,f*LN(1+g/f),IF(f>=500,t*(EXP(g/t)-1),(t*((1+g/f)^(f/t))-t)))

Where:

g = Current interest rate

f = Number of times the current rate compounds per year

t = Number of times the converted new rate compounds per year

This lookup table grabs two of the numbers for the formula

Continuous compounding in Excel is generally calculated as:

=ln(1+r)
The natural log of the annual rate

=ln(1+5.0%)
4.8790164169432%

Solving for the annual rate given the continuous rate

=EXP(r)-1
e raised to the power of the continuous rate

=EXP(0.048790164169432)-1

5.00%

e =2.71828182845904…

There is also a  is a simpler version of the formula that can be copy/pasted into another worksheet without a lookup table:

Don Pistulka

Retired Credit Union CFO - Finance
Background: over 40 years in investments, asset/Liability management, banking, securities trader.
Worked for: California Credit Union, WesCorp, CalFed S&L, Crocker Bank, Carroll McEntee, Federal Home Loan Bank Board (D.C.), Western Asset Management, Security Pacific National Bank.

1. If the compond interest is 15% per year what is the simple interest

1. Spence,
It depends. Over what period of time? A \$10,000 simple interest investment at 15% for two years, would earn \$1,500 per year or \$3,000. The same investment compounded annually, would earn \$3,225. If you are asking for an equvilent interest rate, again it depends on the term. For the example above, the interest rate for a \$10,000 investment worth \$13,000 in two years would be equivalent to a 14.0175% compound annual interest rate. Do you have a specific type of loan or investment in mind? Can you give me an example?

2. […] Simple Formula for Converting Compound Interest Rates December 21, 2014 […]

3. […] Simple Formula for Converting Compound Interest Rates December 21, 2014 […]

4. […] Simple Formula for Converting Compound Interest Rates December 21, 2014 […]