compound interest amortization schedule excel

I've received a lot of requests over the years to provide a formula for compound interest with monthly contributions. A = value of the accrued investment/loan.YouĬan see how this formula was worked out by reading this explanation on. How long it might take you to reach your savings target, based upon an initial balance and interest rate. This variation of the formula works for calculating time (t), by using natural logarithms. So, you would need to start off with $6712.10 to achieve your goal. This formula is useful if you want to work backwards and calculate how much your starting balance would need to be in order to achieve a future monetary value.Įxample: Let's say your goal is to end up with $10,000 in 5 years, and you can get an 8% interest rate on your savings, compounded monthly. This example shows monthly compounding (12 compounds per year) with a 5% interest rate.

If you're using Excel, Google Sheets or Numbers, you can copy and paste the following into your spreadsheet and adjust your figures for the first four The formula for calculating compound interest with monthly compounding is:Īdvertisements How to use the formula in Excel or Google Sheets Let's look at how we can use this formula for monthly compounding, and we can then go through an example calculation. Should you wish toĬalculate the compound interest only, you need to deduct the principal from the result. It's worth noting that this formula gives you the future value of an investment or loan, which is compound interest plus the principal. P = principal investment or loan amount.Subtract the initial balanceįrom the result if you want to see only the interest earned. Next, raise the result to the power of the number of compounds per year multiplied by the number of years. Start by multiply your initial balance by one plus the annual interest rate (expressed as a decimal) divided by the number of compounds per year.

How to calculate compound interest using the formula Let's take a look at how we put these into our formula. In order to use the compound interest formula you will require specific values for your initial balance (principal), annual interest rate (expressed as a decimal), the number of compounds per year and the number of

n = number of times interest is compounded per year.

A = future value of the investment/loan.

Annual compounding (1x compound per year)Ĭalculate principal ( P) based upon future value

Compound interest amortization schedule excel