Comments on: How to calculate compound interest in Excel: daily, monthly, yearly compounding

The tutorial explains the compound interest formula for Excel and provides examples of how to calculate the future value of the investment at annual, monthly or daily compounding interest rate. You will also find the detailed steps to create your own Excel compound interest calculator. Continue reading →

Comments page 2. Total comments: 109

  1. My request,
    I have deposited fixed deposits on cummulative basis(drawn on maturity) in various banks, various rate of interest, starting and ending dates. All these deposits in months i.e. 15,22,27, 37 months like this. Interest will be calculated quarterly basis. I want excel correct calculation or excel template, along with finanical year interest between 1st April year to 31st March next year(say 01-04-2018 to 31-03-2019) for income tax purpose. And if financial year interest exceeds INR 50000/- otherwise no tds deducted. Ex: start date/ end date/ rate / Deposits in months / invest amt / maturity amt / total interest/ financial year interest/ Tax deducted at source at 10% on FY Interest. I hope you understand my request and send an excel statement with formula incorporated to my email id.
    Thanks in advance.
    GOPINATH

  2. HI, need some help. I am creating an Excel spreadsheet that demonstrates the compound results on retirement investments over time. It compares several individuals, all invest funds annually but for different time periods. For example, one begins at age 20, contributes each year for 10 years then stops contributing but all funds compound annually for 45 years. Another contributes annually for 35 years but begins after the first individual stops making contributions. A third makes 45 years of contributions. All funds compound annually at 8%. Struggling with the formulas.

  3. Hello,
    I have a set amount of $1.75 million that compounds daily at a rate of 8% per annum. I am having difficulty showing each month's principal and interest accrued to the end of each month. I know there has to be a Excel formula to do this for each month on the calendar reflecting the different number of days in each month, but it just isn't coming to me. I sure could use your help. Thanks. Jim

  4. I am trying to calculate interest on back pay. The interest is compounded daily and there is new principle added every two weeks (not to mention the interest rate changed over the relevant period) is there a formula for this? For example, employee A was supposed to be paid $2000 every two weeks, he is due back pay with 4% interest compounded daily for say 6 months. How would I calculate this?

  5. I have a loan that I will make monthly payment,but interest compound daily. How can I show it in Excel?
    Borrow amount: 10,800.00
    Annually Rate: 6.75%
    Date taken 06/06/18
    First payment: 07/01/18

    Thanks,

  6. Dear all,

    I like to know the excel formula for fixed deposit which compounding in certain period when entry and exit dates are provided.

    Example: I have deposited $.2,000 on 11th Oct 2018 @ annual rate of 10% with quarterly compounding. What will be my future value on 8th Sept 2019.

    We can get the future value as $.2,188.75 at end of period as worked out below.
    But If I have series of deposit to hundreds of people how I can get in once coloum with future value

    Say : Start Date End Date Principal Amount Interest rate Result
    11/10/18 08/09/19 2000 10% Formula

    Start Period Start Amt. End Period Interest End Amt. Days
    11/10/18 2000.00000 10/01/19 50.4109589 2050.410959 92
    11/01/19 2050.410959 10/04/19 50.55807844 2100.969037 90
    11/04/19 2100.969037 10/07/19 52.38032394 2153.349361 91
    11/07/19 2153.349361 08/09/19 35.39752375 2188.746885 60

    Can any body help me in this task.

    Thanking you

  7. Hi Maria, I was just wondering if one can calculate compound interest at a certain interest rate, compounded quarterly, but with the additional payment being every month and not every quarter. Any help would be appreciated!

  8. In the line that begins:

    = $2,000 * (1 + 0.8/12)5x12

    I think you mean:

    = $2,000 * (1 + 0.08/12)5x12

    As 0.8 would be an interest rate of 80%

  9. I am trying to use the Excel FV to calculate the compound interest of a series of annual payments, compounded quarterly. I can divide the annual interest rate by 4 and multiply the terms to num of years * 4. This does not give the same as done long way in Excel. The long way is calculate quarterly compounded interest each year and then add annual payment. This is compounded the next year and so forth. Is this possible to be done using Excel functions?

  10. how to calculate between two dates compound interest
    Ex 25.02.2017 loan taken
    28.03.2018 load paid
    PV 100000 Int rate 12%

  11. I need to find out what is currently owed on a past due debt that is incurring interest. I have several debts owed to me that I need to track. For example, one debt from July 01, 2015 with a principle of $267.27 that is accruing interest of 12% per annum. from August 01, 2015 through today's date. A second debt from August 01, 2015 with a principle of $623.12 that is accruing interest of 12% per annum. through today's date. A third debt from January 01, 2017 with a principle of $6500 and an interest rate of 18% through today's date.

    I need an excel spreadsheet that can show the court the amount currently owed on each debt seperatly and how that amount has grown.

    I hope that is clear.

    Thank you for any help you can offer.

  12. I have a loan for 25,000 at 7% interest with a total payback of 32,250 this loan is paid back on a daily basis for 12 months except for holidays and weekends. The total number of payments are 252. The daily payment is 128.00. How do I do an amortization table?

    Thank you

  13. Hi there,

    I was wondering if you could please assist with this following question:

    What is the compound interest of any amount invested at 6%, if the interest is capitalized quarterly?

    Thank you in advance for your kind assistance.

    Best,

    Emmanuel.

  14. .please calculate the compounding interest of 4000 bd after 3 years of paying at 130 bd per month

  15. I have to create a spreadsheet of a principal amount owing, compounded daily, at interest rates that change every 3 months, with occasional payments to reduce principal, over a period of 8 years. Can you help me with my formula? I have a list of the various interest rates.

  16. Hi

    How do you write excel formula to give below dates in a column
    In this example I've chose 7 days
    Each cell down is +7 days from the previous cell, except when it gets less then 7 days then it is the last day of the month and next cell after the the end of month is +7 days from the previous 2 cells

    14/7/17 (start date, can be any date of the month)
    21/7/17
    28/7/17
    31/7/17 (Here needs to be end of month)
    4/8/17 (Here needs to be 28/7/17 + 7days)
    11/8/17
    18/8/17
    ...and so on

    Thank you

    1. Hi, Eddie,

      The best, quicker and easier, option would be a macro or VBA code. You can ask around Mr. Excel forum for them.

      If you'd like to use a formula, here's an example:

      1. Format column A as a date (Home tab > Number format)
      2. Input start date to A1 (14/7/17)
      3. Put the formula below to A2 cell:
        =IF($A1=EOMONTH($A1,0),IFERROR(IF((OFFSET($A1,-1,0)+7)=EOMONTH($A1,0),($A1+7),(OFFSET($A1,-1,0)+7)),($A1+7)),IF(MONTH($A1)=MONTH($A1+7),($A1+7),EOMONTH($A1,0)))
      4. Copy it down the column by drag-and-dropping

      Hope this helps!

      1. Hi Natalia.

        Fantastic! Thank u so much for the formula and tip.
        Wishising u all the best.
        Regards
        Eddie

        1. Hi Natalia,

          Can you help me with the next stage of the formulas...

          How can your formula

          =IF($A1=EOMONTH($A1,0),IFERROR(IF((OFFSET($A1,-1,0)+7)=EOMONTH($A1,0),($A1+7),(OFFSET($A1,-1,0)+7)),($A1+7)),IF(MONTH($A1)=MONTH($A1+7),($A1+7),EOMONTH($A1,0)))

          be changed so that if the end of the month falls on a weekend then it gives the date of the last working business day. Then rule continues as before.

          Current formula gives
          15/9/17
          22/9/17
          29/9/17
          30/9/17 since this is Saturday (weekend)
          6/10/17

          Then result should be
          15/9/17
          22/9/17
          29/9/17
          29/9/17 repeat same date as previous cell only if it falls on a weekend
          6/10/17 continue with the standard rule (29/9/17+7 days)
          13/10/17
          .... and so on

          Your new formula ......(weekend rule)

          Next stage formulas:
          Also how can your 1st formula and your 2nd formula above be rewritten so rather than 7 day increments, how can I change the interval date to any number so that it still follows the above two formula rules.

          Your new formula ......(based on any incremental number)
          Your new formula ......(based on any incremental number and weekend rule)

          Thank you so much for your help

          1. Eddie,

            we've adjusted the formula for you that includes the weekend rule:
            =IF($A1=EOMONTH($A1,0),IFERROR(IF((OFFSET($A1,-1,0)+7)=EOMONTH($A1,0),($A1+7),(OFFSET($A1,-1,0)+7)),($A1+7)),IF(MONTH($A1)=MONTH($A1+7),($A1+7),IFERROR(IF(OFFSET($A1,-1,0)=$A1,($A1+7),IF(WEEKDAY(EOMONTH($A1,0),2)=6,EOMONTH($A1,0)-1,IF(WEEKDAY(EOMONTH($A1,0),2)=7,EOMONTH($A1,0)-2,EOMONTH($A1,0)))),($A1+7))))

            Also, for the case when you want to use some incremental number (let's say it's placed to B1 cell), we have the following:
            =IF($A1=EOMONTH($A1,0),IFERROR(IF((OFFSET($A1,-1,0)+$B$1)=EOMONTH($A1,0),($A1+$B$1),(OFFSET($A1,-1,0)+$B$1)),($A1+$B$1)),IF(MONTH($A1)=MONTH($A1+$B$1),($A1+$B$1),EOMONTH($A1,0)))

            But when you want to use both, incremental number and weekend rule, the formula becomes too complex and long. You need to use a VBA code or a macro.

            1. can explain the same

            2. Thank u soo much Natalia

  17. sir please send me DDA calculator daily deposit scheme interest calculator suppose any per giveing 100/ rs daily for 180 days @ 7% per annum for 180 days. how we calculate daily interest and total amount i have to pay

    thanks with regard

  18. sir please send me DDA calculator daily deposit scheme interest calculator suppose any per giveing 50/ rs daily for 90 days @ 7% per annum for 90 days. how we calculate daily interest and total amount i have to pay

    thanks with regard

  19. Brilliant. Thanks a lot, exactly what I needed.

  20. I have 2 situations.

    1) I have 200,000 and it will be double in 10 years time. What compound interest rate would be required? Solution in excel please.

    2) PV of cash out flow for 48 months @ 1500 per month paid at the beginning of each month. Solution in excel please.

  21. What if I need to find the future value when it has a monthly investment? EX: Inital Investment $8000 , Monthly Investment $125, Rate 8.75, and 20 years.

  22. How do I begin to solve this problem or set up a formula in exel

    5.Suppose John could change investment plans at E-Trade and earn an additional 1% per annum beginning in year 2 and an additional 1% beginning in year 4. How much money do they need to save each month to reach their goal?

  23. it is very help full formula please send more formula

  24. Suppose you invest Rs.10000 in a business each December 31 (at the end of each year) for the next 10 years. If you are getting interest of 12%, compounded semiannually, how much will you have after 10 years?

    Please solve this question
    Question Hint
    invest Rs.10000 in a business each December 31 (at the end of each year) for the next 10 years?

  25. Hi Svetlana, can you help with a formula (for Excel) to "reverse engineer" compound interest. I have an investment that compounds weekly, but at no set rate (trading currencies, some weeks are better than others). I want to work out each week, what the effective compound interest rate has been since starting the investment, and then use that rate to work out a "what if" scenario..... what if this investment continues to perform at the current level till the end of the financial year?

    So, I want to enter the Initial Value, Current Value, Number of Periods since start, and it should give me the "effective weekly compound interest rate".

    Thank you.

  26. Hi,

    I found this site very helpful until I found an error in the way you work with the FV function.

    I cross referenced the ASIC calculator with your formula and your excell formula was only working when the number of compounding periods equals exactly 12. As soon as you change this figure it becomes very inaccurate.

    This is because in your formula the contributions are only made the same number of times that interest is compounded. Ie. when you set pmt to $250 and compounding periods as 12, then it adds the $250 12 times, when you set pmt to $250 and number of compounding periods to 1 then it is mistakenly only adding the $250 once instead of 12 times.

    You can fix this by modifying your formula;
    =FV(B4/B5, B6*B5,-B8,-B3,B9)

    and instead using

    =FV(B4/B5, B6*B5,-B8*12/B5,-B3,B9)

  27. sir plz send daily deposit scheme (dds) formula

  28. Excel Spreadsheet
    1) Joint Venture

    $24,000 investment
    $10,000 gain in 45 days
    Gaining the same interest every 45 days effective February 18, 2015 to current date.

    2) 1) Joint Venture

    $24,000 investment
    $10,000 gain in 45 days
    Gaining the same interest every 90 days effective February 18, 2015 to current date.

  29. Excel is one of those tool-case programs that is indeed useful for everyone - in the same way that 1-2-3 originally was. It's not that Office (as a whole) isn't useful - it is; it's just that it gets taken for granted. How many of you use Office (not just Word or even Excel) for non-work-related things at home on a weekly basis?

  30. dears,

    if the initial investment is 1,000,000 for ten years with 200% accumulated interest ( 2,000,000 )

    how can calculate the compound interest rate ???

    for this case the compound interest is 11.6123174 %

    thanks

  31. Hi,

    Thank you for this post. Can you please tell me the formula in excel to calculate n (years) if the amount, rate and principal is known.

  32. Do large financial institutions/banks use 365.25 for annual rates (do to leap years)?

  33. Never saw an answer to Richards question (Ref. #9) Please respond to it, email me the answer. I am also trying to see the answer to help my Son!

  34. I'm trying to find the annual return for a string of 12 monthly returns, any chance someone knows what the formula would be?

    For example:

    -2.75% -1.92% 1.91% 1.13% 0.96% 1.82% 1.22% 0.85% 0.39% -1.27% 2.81% 1.08%

    Jan E5 -2.75%
    Feb F5 -1.92%
    Mar G5 1.91%
    Apr H5 1.13%
    May I5 0.96%
    Jun J5 1.82%
    Jul K5 1.22%
    Aug L5 0.85%
    Sep M5 0.39%
    Oct N5 -1.27%
    Nov O5 2.81%
    Dec P5 1.08%

    When adding them up it ends up being 6.21% but I know there's a more accurate way. Can someone please help?

  35. I am trying to work out what the monthly payments would be on a loan with compound interest.

    i.e. PV 1,000,000 - annual rate 5% - for 12 months

    I am able work out the compound value at the end of the term using FVSCHEDULE, but how do I work out what how the monthly payments will impact this?

    Thank you

  36. Hi
    I am trying to calculate the amount owing on a loan of $6,600 taken out 1/01/1990 which went for 2.5 years (no interest was paid), and then $4,150 was paid back but the remaining $2,450 still outstanding. Interest calculated daily (as per bank loan).
    I have looked up the bank interest rates for the period so can use a pivot table but would like the formula to calculate what is still owed.

    thanks

  37. Investment analysis:
    Initial Investment: 300,000,000
    Cost of Capital: 10%
    Assume: (yearly gross revenue): 32,964,000 will not increase over the 20-year period.
    Assume: 360,000 expense for 1st year and will increase with annually compounded rise of expense at 7.25%
    Questions: 1)compute the payout(payback) point for undiscounted cash. 2) compute the payout(payback) point for discounted (time value) cash.

  38. My question is
    loan 500000
    Loan percent 10%
    time 60 month
    Interest calculation-daily

    After 365 days the accrued interest should automatically capitalized.(if interest not paid)
    it means if interest not paid, the accrued interest of 48767 must capitalized
    Now the total principal is 548767. the interest must calculate on this figure.
    and if repay the the loan first settled capitalized interest, then remaining interest and then principal.

    Now it's 400 days running.
    our statement shows:
    Total loan - 548767
    Accrued interest (after 365 Days again)- say 21000

    If The loan amount partially with 100000.

    First settle capitalized interest i.e 48767
    then settled accrued interest i.e. 21000, then remaining amount to be deducted on Principal.
    after that principal amount will be 469676 and interest will be calculated on it.

  39. Hi,

    I just wanted to say a BIG Thank You for creating this "learn-and-do-it-yourself" website.

    Luv yu all

  40. can u explain how to find out rate of compounded
    interest? Example. The interest earned by a principal invested 5% compound anually for 3years is computed using the relation c=Prt, where c is the interest, P is the principal, r is the rate, and t is the time.

  41. Can anyone tell me how to calculate the interest that i am paying if: I have an amount of principal that i am paying interest on and the amount increases every period and i know what the total amount of interest i am paying at the end is?

  42. Hi

    I want to build a tool to calculate required monthly instalments, when interest rate is fixed, future value is known/changeable, duration is known/ changeable and interest is compound daily.

    Anyone any ideas?

  43. The following formula expresses interest compounded at 4.5% quarterly on a debt of £1,000.00:
    SUM=(£1,000*(1+0.045)^(U9*4))
    where U is the fraction of the year the invoices was paid late (days late/365)

    How do I amend the formula such that interest is calculated not with reference to the quarters BUT with reference to the annual quarter days found in many leases namely: 25 March, 24 June, 29 September and 25 December annually?

  44. I have a client that experienced embezzlement from a former employee. How can I calculate interest on the money he embezzled? Can I continue to add new amounts as they happen and continue to calculate interest on the entire amount.

    Time period of theft 2011-2014. Money has not been recovered so I want to continue to calculate interest.

    The loss of money did impact the business. Business could not complete planned renovations that cost the business additional sales.

    Thank you

  45. re above query
    $1,000 loaned November 9, 2015
    interest is 5.2%
    calculated Monthly
    end date of loan is March 10, 2016
    no payment have been made on this loan

  46. Maria, great website. Thank you
    I need to calculate the accumulated interest on a loan between two dates. Interest rate is 5.2% calculated monthly but there have been no payments.

    Amount Start End Int Calculated Total
    $1,000 9-Nov-15 10-Mar-16 5.20% Monthly ?
    $5,000 26-Nov-15 10-Mar-16 5.20% Monthly ?
    $6,700 1-Dec-15 10-Mar-16 5.20% Monthly ?

  47. Hi,

    In my case, I would like to know the compound interest.

    For example, every month I am paying a particular amount for 20 months.
    I have the future value. So is there a way to know the compound the interest?

    Future value = 2,250
    period = 20 months
    monthly amount = 75

    What will be the Compound interest?

    Thanks,
    Anand

  48. I was wondering if anyone could help me with this problem. I'm trying to calculate the future value of a 1 year investment using excel but I am having issues because I have two differing semiannual interest rates.
    Here's what I have:
    Time period= 1 year
    Principal=$15000
    1st Semiannual rate=4.80%
    2nd Semiannual rate=6.93%
    monthly payments =$100

  49. I have 135 after 3 years on an initial capital of 100
    What would be my compound interest per anum

  50. Hi Maria,

    I want to know the formula for the following calculation.

    Monthly deposit = 1000
    Monthly interest= 1%
    Compounding frequency = once a year till 5 years

    Ex: first month interest = 1000*1% , second month interest = (1000+1000)*1% so total interest for that year = 780. so need to calculate simple interest till 12 months and total principal+simple interest will become principal for the next year(need to compound annually)

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)