Compound interest is interest that's calculated both on the initial principal of a deposit or loan, and on all previously accumulated interest.
While calculating monthly compound interest you need to use basis as you have used in other time periods. You have to calculate the interest at the end of each month. And, in this method interest rate will divide by 12 for a monthly interest rate. To calculate the monthly compound interest in. How to calculate recurring deposit in monthly basis? M = ( R. (1+r)n - 1 ) / (1-(1+r)-1/3) M is Maturity value R is deposit amount r is rate of interest n is number of quarters if i take 'n' as 4(no of Quarters) for 1 year its showing yearly Maturity value.can anyone tel me how to do monthly calculation.Thanks.
For example, let's say you have a deposit of $100 that earns a 10% compounded interest rate. The $100 grows into $110 after the first year, then $121 after the second year. Each year the base increases by 10%. The reason the second year's gain is $11 instead of $10 is as a result of the same rate (10% in this example) being applied to a larger base ($110 compared to $100, our starting point).
Or let's say, $100 is the principal of a loan, and the compound interest rate is 10%. After one year you have $100 in principal and $10 in interest, for a total base of $110. In year two, the interest rate (10%) is applied to the principal ($100, resulting in $10 of interest) and the accumulated interest ($10, resulting in $1 of interest), for a total of $11 in interest gained that year, and $21 for both years.
It's similar to the Compounded Annual Growth Rate (CAGR). For CAGR, you are computing a rate that links the return over a number of periods. For compound interest, you most likely know the rate already; you are just calculating what the future value of the return might be.
WATCH: What is Compound Interest?
For the formula for compound interest, just 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 * (1 + (interest rate/number of compounding periods per year))^(years * 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 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 set this up in Excel. 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. Both are detailed below:
In Microsoft Excel 2010, the FV function calculates the future value of a deposit that earns compound interest at a constant rate. Depending on the variables assigned, the FV function can calculate the growth of a single deposit or a series of regular deposits. For example, if you regularly deposit $2,000 of business profits every month into a savings account that earns 5 percent annual interest, the FV function calculates the total sum your business would have after a specific amount of time.
Step 1
Enter the interest rate for the compounding period in cell A1. Add a percent sign after the figure to tell Excel to treat it as a percentage. Assuming an annual interest rate on your deposit, divide the rate by the number of compounding periods. In the example, you would enter '=5%/12' if the interest rate is 5 percent and compounds monthly.
Step 2
Enter the number of compounding periods in cell A2. If you know the number of years, multiply by the number of compounding periods in a year. Continuing with the example of five years, enter '=5*12.'
Step 3
Enter the amount you plan to deposit in each compounding period. This figure must be the same for all periods and should be entered as a negative number to signify a payment. If you're only making a single deposit, enter zero. Using $2,000 monthly deposits as an example, enter '-2,000.'
Step 4
Enter the amount of an immediate deposit in cell A4 and format it as a negative number. If you are not making a deposit now, enter '0.' If you are only calculating a single deposit, enter it here. In the example, you are not depositing money immediately, so enter '0.'
Step 5
Enter '=FV(A1,A2,A3,A4)' without quotes in cell A5 to calculate the future value of the deposits. In the example, this function returns $136,012.17.