Many of us just need a calculator to compute simple interest. You merely multiply the daily interest rate by the principal byꦰ the number of days that elapse between payments.

But calculations are trickier when it comes to interest that compounds—i.e. interest that accrues on 𒐪principal and the accumulated interest as well. An Excel spreadsheet can take care of this work for you, provided you set up the formula 𝄹accurately.

What Is Compound Interest?

Let's make sure we're clear on terminology. 澳洲幸运5官方开奖结果体彩网:Compound interest, also known as compounded interest, is interest that is calculated on the initial 澳洲幸运5官方开奖结果体彩网:principal of a deposit or loan, and on all previously accumulated interest.

For example, let's take a $100 loan which carries a 10% compounded interest. After one year, you have $100 in principal and $10 in interest, for a total base of $110.

In year two, the 10% interest rate is applied to the $100 principal, resulting in $10 of interest. It's also applied to the accumulated interest of $10, resulting in $1 of additional interest, for a total of $11 in interest gained that year.

The second year's increase is $11, instead of $10, because the interest is 澳洲幸运5官方开奖结果体彩网:compounding. It'꧅s being applied to a base that has grown to $110 compared to our starting point of $100. Each year, the base increases by 10%: $110 after the first year, then $121 after the second year.

What Is The Formula for Compound Interest?

The formula for compound interest is similar to the one for 澳洲幸运5官方开奖结果体彩网:Compounded Annual Growth Rate (CAGR). For CAGR, you compute a rate which links the return over a number of periods. For compound interes🃏t, you know the rate already. You are simply calcu🌸lating what the future value of the return might be.

To reach the formula for compound ♔interest, you algebraically rearrange the formula for CAGR. You need the beginning value, interest rate and number of periods in years. The interest rate and number of periods need to be expressed in annual terms, since the length is presumed to be in years. From there you can solve for the future value. The equation reads:

Beginning Value x [1 + (interest rate ÷ number of compounding periods per year)] ^ (years x number of compounding periods per year) = Future Value

This formula looks more complex than it really is, because of the requirement to express it in annual terms. Keep in mind, if it's an annual rate, then the number of compounding periods per year is one, which means you're dividing the interest rate by one and multiplying the years by one. If compounding occurs quarterly, you would divide the rate by four, and multiply the years by four.

Calculating Compound Interest in Excel

Financial modeling best practices require calculations to be transparent and easily auditable. The trouble with piling all of the calculations into a single formula is that you can't easily see what numbers go where, or what numbers are user inputs or hard-coded.

There are two ways to 澳洲幸运5官方开奖结果体彩网:set this up in Excel so as to minimize that problem. The most easy to audit and understand is to have all the ꩵdata in one table, then break out the calculations line by line. Conversely, you could calculate the whole equation in one cell to arrive at just the final value figure. We recommend the first approach, bu⛦t both are detailed below.

In the example below, you can in🐼put the data in yellow, and choose the compounding period.