Excel Productivity Guide

Get instant access to this free e-book fully loaded with Useful Excel Tips, 80+ Keyboard Shortcuts, and VBA Codes.

How To Calculate Compound Interest In Excel

    step by step guide to calculate compound interest in excelToday, I will show you how to calculate compound interest in excel.

    Compound interest is one of the most important financial calculations.

    And, you can calculate it on a yearly, quarterly, monthly, and daily basis.

    In this post, you will learn all these methods.

    End of this post, you can download a simple template to calculate compound interest.

    How to Calculate Compound Interest In Excel

    Here are the steps.

    Yearly Compound Interest

    For calculating yearly compound interest, you just have to add interest of the one year into next year’s principal amount to calculate the interest of the next year. Calculate Compound Interest In Excel - Formula To Calculate Year Compound Interest In ExcelAnd, the formula in excel for yearly compound interest will be.

    =Principal Amount*((1+Annual Interest Rate/1)^(Total Years of Investment*1)))

    Let me show you an example.

    Calculate Compound Interest in Excel - Calculate Yearly Compound Interest Formula

    In above example, with $10000 of principal amount & 10% interest for 5 years, we will get $16105.

    In the first year, we get $10000*10% which is $1000 & in the second year, ($10000+$1000)*10% = $1100 & same is for 5 years.

    Quarterly Compound Interest

    Calculating quarterly compound interest is just like calculating yearly compound interest. But, here we have to calculate interest four times in a year.

    Interest amount for each quarter will add to the principal amount for the next quarter.Calculate Compound Interest In Excel - Formula To Calculate Quarter Compound Interest In ExcelTo calculate the quarterly compound interest you can use the below-mentioned formula.

    =Principal Amount*((1+Annual Interest Rate/4)^(Total Years of Investment*4)))

    Here is an example.
    Calculate Compound Interest in Excel - Calculate Quarterly Compound Interest Formula

    In above example, with $10000 of principal amount & 10% interest for 5 years, we will get $16386.

    In the first quarter, we get 10000*(10%/4) which is $250 & in the second quarter, ($10000+$250)*(10%/4) = $256 & same is for 20 Quarters (5 years).

    Monthly Compound Interest

    While calculating monthly compound interest we have the same basis as we have used in other time periods.

    Just we have to calculate the interest at the end of each month.  And, in this method interest will divide by 12 for a monthly interest rate.

    Calculate Compound Interest In Excel - Formula To Calculate Monthly Compound Interest In Excel

    To calculate the monthly compound interest you can use the below-mentioned formula in excel.

    =Principal Amount*((1+Annual Interest Rate/12)^(Total Years of Investment*12)))

    Calculate Compound Interest in Excel With Monthly Compound Interest Formula

    In above example, with $10000 of principal amount & 10% interest for 5 years, we will get $16453.

    In the first month, we get 10000*(10%/12) which is $83.33 & in the second month, ($10000+$83.33)*(10%/12) = $84.02 & same is for 60 months (5 years).

    Daily Compound Interest

    While calculating daily compound interest again we have to use the same method with below-mentioned calculation formula.

    We have to divide interest rate with 365 to get a daily interest rate.

    Calculate Compound Interest In Excel - Formula to Calculate Daily Compound Interest In Excel

    So, you can use below formula to calculate daily compound interest.

    =Principal Amount*((1+Annual Interest Rate/365)^(Total Years of Investment*365)))

    Calculate Compound Interest In Excel With Calculate Compound Interest On Daily Basis

    In above example, with $10000 of principal amount & 10% interest for 5 years, we will get $16486.

    In the first day, we get 10000*(10%/365) which is $4 & in the second day, ($10000+$4)*(10%/365) = $4 & same is for every day for 5 years.

    By using above methods, I have created a cumulative interest calculator to calculate all of the above calculations for interest in a single worksheet.

    Interest Calculator

    download sample file to learn how to calculate compound interest in excel

    Conclusion

    I hope now you know how to calculate compound interest in excel.

    This calculator will help you to calculate compound interest instantly.

    You can use this template to calculate yearly, half-yearly, quarterly, monthly & daily compound interest rate.

    Now, It’s your turn.

    Is there any other method to calculate compound interest in excel?

    Let me know in the comment box.



    • Brenda G

      Hello, Puneet! This is another wonderful article. However, I tried to download the sample file, but the link is not working. Can you check it? Thank you!

      • Puneet Gogia

        Thanks Brenda for pointing me out.

        Just updated the link. Now you can download the file.

        • Brenda G

          Excellent, thank you!

          • Puneet Gogia

            Welcome Brenda.