Simple Formula for Converting Compound Interest Rates

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

General Formula = (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

 

I call the formula above the “General Formula” because of the continuous compounding option:

* Continuous compounding uses an infinite number of periods per year. The general formula above uses 1,000,000 periods to approximate continuous compounding. It is generally accurate out to 5 or more decimal places, but is not accurate enough for use in more sophisticated financial models. Continuous compounding is included in the above model to show there is a limit to compounding. Google “continuous compounding” for more information.

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…

On the second sheet of the workbook is a simpler version of the formula that can be copy/pasted into another worksheet without a lookup table:

Download workbook “Convert_Compound

5 thoughts on “Simple Formula for Converting Compound Interest Rates

    1. Don PistulkaDon Pistulka Post author

      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?

      Reply
  1. Pingback: When Cash Flows Don’t Match Compounding Periods – Excel@CFO

  2. Pingback: Relationships – XIRR(), Amortization, Present & Future Value | Excel@CFO

  3. Pingback: Rule of 78 | Excel@CFO

Leave a Reply

Your email address will not be published. Required fields are marked *

WordPress spam blocked by CleanTalk.