How to Calculate Compound Interest in Excel Future Value based on Compound Interest
FBTwitterReddit
video description
Rating: 4.0; Vote: 1
we will teach you how to calculate compound interest in Excel.
Compound interest is the addition of interest in the principal amount, meaning reinvesting interest rather than paying it out.
Lets create a savings plan and find out what the compound interest income will be in comparison to the actual amount invested.
For that, we will use the Future Value function.
Lets look at the saving plan in this sheet. Over here, we will be depositing Fifty dollars at the end of each month in the bank, thats why this value is negative. Our annual interest rate is 14. 5 percent, which we will divide by 12 to get the monthly rate. This is because the period over here is in months. We are making this savings plan for 40 years.
Over here, lets enter the equals to sign and type in FV, for future value, followed by brackets open.
First is the rate, which will be the interest rate against our investment. Since we are making monthly payments, lets select the Monthly Rate here.
Next, lets move to NPER, which is the total number of payments that are made. In this case, it will be one payment for each month. Therefore, lets select the cell with total months here.
Next is the PMT, which will be the annuity payment. For that, lets select the monthly savings value here.
If you already have money in the account that you want to include in your savings plan, you would add it here for the Present Value. If you dont, like in this example, you can just skip it all together.
For type, we will be depositing the money at the end of the month, so we can leave the type criteria as well.
Once you hit enter, the function will automatically calculate the future value of the investment made every month for 40 years.
To calculate compound interest, we will simply deduct the future value amount with the principal amount invested.
For the principal amount, we have simply multiplied the monthly savings with the total number of months.
Since the principal amount here is already negative, lets use the sum function to calculate the total compound interest. Lets select the future value and principal amount in the range, and once we hit enter, we will have the total compound interest earned over a period of 40 years.
And thats it!
Which Excel function would you like to know more about?
Ahmad: Hi thank u for this Suppose there is a calculation for rent After say 5 year, the 1000 annual rent will go up by 2% annually. The contract is for 30 years, how would u approach it? To the value of the full contract and what has been paid for a set number of years especially given that for the first 5 years the rent is 1000 Date: 2023-07-08
