Calculate amortization schedule for annuity loan – template [excel, example, explanation, loan] (october 2022)
Table of contents:
What is mixed interest?
Compound interest, also called compound interest, is interest calculated on both the initial principal of a deposit or loan and any interest previously accrued.
For example, let's assume that $100 represents the principal of a loan that contains a 10% interest rate. After one year, you have $100 in principal and $10 in interest, for a total base of $110. In the second year, the interest rate (10%) on the principal amount (100 USD, resulting in an interest rate of 10 USD) and the accumulated interest (10 USD, resulting in 1 USD interest) for a total of 11 USD. In interest gained this year. The second year increase is $11 instead of $10 because the interest rate is increasing – that is, it is applied on a larger basis ($110 compared to $100, our starting point). 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?
It is similar to the compounded annual growth rate (CAGR). For CAGR, they calculate a rate that links returns over multiple periods. For compound interest, you probably already know the rate; you're just calculating what the future value of the return might be.
For the compound interest formula, simply rearrange the formula algebraically for CAGR. You need the initial value, the interest rate and the number of periods in years. The interest rate and the number of periods must be expressed annually, since it is assumed that the length is in years. From there you can solve for the future value. The equation is:
Initial value * (1 + (interest rate / number of interest periods per year)) ^ (years * number of interest periods per year) = future value
This formula looks more complex than it really is, because of the requirement to express it in annual terms. Remember, if it is an annual rate, then the number of interest periods per year is one, which means you divide the interest rate by one and multiply the years by one. If compounding is done quarterly, divide the rate by four and multiply the years by four.
Calculation of compound interest in excel
Best practices for financial modeling require calculations that are transparent and easily verifiable. The problem with stacking all the calculations into a single formula is that you can't easily see which numbers go where, or which numbers are user input or hard-coded.
There are two ways to set this up in excel. The easiest way to check and understand is to have all the data in a table and then lift the calculations line by line.Conversely, you could calculate the entire equation in one cell to get only the final value figure. We recommend the first approach, but both are listed below.
In the following example you can enter the data in yellow and select the compounding period.