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 3. Total comments: 109

  1. 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.

  2. 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?

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

  4. 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?

  5. 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.

  6. 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)

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

  8. 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.

  9. 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?

  10. 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

  11. 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.

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

  13. 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!

  14. 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?

  15. 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

  16. 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

  17. 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.

  18. 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.

  19. Hi,

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

    Luv yu all

  20. 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.

  21. 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?

  22. 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?

  23. 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?

  24. 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

  25. 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

  26. 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 ?

  27. 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

  28. 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

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

  30. 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)

  31. Hi,
    I am hoping that you can assist with the following:-

    I have a Loan with the following parameters:-
    Principal: $200,000, Annual Interest Rate: 5%, Loan Period: 25 years, Payments being made monthly. Using Excel's PPMT formula I calculated a Monthly Repayment of $1,169.18 =-PMT(5/12,25*12,200000).

    What I want to do now is to calculate the number of repayments if I increase the calculated monthly Repayment by a constant amount, say $50.00 per month.

    I tried using Excel's NPER and I tested it using the same inputs from above. NPER(rate,pmt,pv,[fv],[type])or NPER(5/12,1169.18,200000,0). This gave me a result of 129.41 whereas I was expecting 25years X 12 = 300months. So unless I have erred then NPER is not what I want. At this point I started trying to transpose the Compound Interest formula with little luck.

    So my question is:-
    "How do I calculate the Term of a loan when the Monthly Repayment Value, The Annual Interest Rate and the Original Loan Amount are all known?"

    1. Hello, Danny,

      Looks like minus is missing in your formula. Please make sure you use this one:
      =NPER(5%/12;-1169,18;200000)

  32. 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

  33. Hi! I do have a question, I don't know if this is complicated or not. How can I create a table that responds to the number of years as an input for the compound interest?
    Example: What is the New Principal Amount in 4 years where there should be 4 rows that would show what the new principal is going to be in that particular year.
    Thanks in advance!

  34. i could use some help.I am drowning in a on line fin 301 class. what is the formula on an excel spreadsheet for the following:
    compute the present value of an $1,850 payment made in 8 years when the discount rate is 10%.

  35. Hello, is there an excel function that will be able to predict values for 25 years with an escalation rate of X% and frequency of every n years.

    For example: Lease rate at the moment is $200 with an escalation of 3% every 3 years.

    Is is possible to have a formula that will auto-compute annual values?

    Thank you.

    1. Hello, Arrey,

      Please clarify what you mean by escalation of 3% every 3 years.

  36. Good day.
    Let's say i want to save 1000 000,i want it in 5 years time and the interest rate is 11%. What formula can i use to work out the monthly amount to save in order to get to 1000 000 in 5 years.

    1. Hello, Tanya,

      Your initial deposit can be found using this formula:
      =-PV(0,11;5;0;1000000)

      Here is one to perform a reverse check:
      =FVSCHEDULE(593451,33;{0,11;0,11;0,11;0,11;0,11})

  37. A doubt , suppose I bought a land 5 year ago at price 25000/- and sold this year at 35000/-, then what compound interest I am getting per year,when interest is compouding every day

  38. In line with Kailua's request above, I am seeking an excel formula for compounding interest with a STARTING monthly deposit of $100 invested at 7% per year (compounded annually), and increasing the monthly deposit by 3% per year (i.e. Year 2 would see a monthly deposit of $103.00; Year 3 would see a monthly deposit of $106.09; Year 4 would see a deposit of $109.27, etc.). Also, the variable of compounding intervals for daily, weekly, monthly, quarterly would be nice)

    I have been unable to find such a calculator on the Internet, as all the formulas do not allow for annual donation changes by donation percentage, as I have stated above. I suspect that Kailua was seeking a similar formula.

    In summary, I am planning to see what the final savings amount will be if I advise my grandchild to contribute $100 per month to his investment portfolio (i.e. $1200 in Year 1), and assume a 7% annual return; and increase his monthly contribution by 3% each successive year (i.e. $103 per month in Year 2) until he retires at age 65.

    Thank you............Richard

  39. how we calculate compund intrest for momthly.show me all the monthly calculation for this.
    term:18months
    min.deposit:200
    rate of int.11.5%p.a

  40. Hello

    You clearly love excel.
    Can you help me devise a formulae to reduced monthly interest charged against a loan as additional funds were added to Initial loan. It is complication of using some housing funds as a general account.

  41. 80000 amount per 7% interest how can find out
    Please reply me

    1. BC Guju tu rahnay de mc

      1. TMC tu bhi guju ke udhar he job kar ta hai and uski he roti khata hai. yad rakh guju nahi tere khane tak ke wandhe ho jaye ge MC.

  42. question. trying to calculate interest for 1 year assuming 27 periods where the first period earns the full rate of interest 10%, each period decreases the amount of interest by 2/27 the last period earns 1/27 of the full amount of interest. Trying to get an excel formula. I would appreciate ant help as soon as possible,.

  43. I'm looking for an excel spreadsheet that calculates the following:

    Initial Investment
    Interest Rate
    Monthly Deposit
    Increase Deposit Annually by (Percentage)
    Time Period
    Future Value

    Can this be done? Can there be a schedule too? I have been searching the internet high and low and cannot find anyone who has this.

    1. @ Kailua
      if you multiply the monthly contribution type by 12 that will give you additional monthly. (ie. from example above =FV(B4/B5, B6*B5, (-B8*12), -B3, B9).

      Not sure what you mean by 'Increase Deposit Annually by (Percentage) but if this is considered a rollover where 0 dividends are paid to you, the next year's starting principal will be the previous years Future value.

      You would have create your own schedule and every year after the FV has been determined consider this your next years principal balance.

  44. Nice Svetlana i appriciate your work i have no words for you but why not i want to say i adore you due to your work and your positive feeling & thinking

    1. Thank you so much for your kind words, Naveed!

      1. I love you

  45. can u explain how to find out rate of compounded interest ?....
    example.....principal
    amount $5000 invested for 1 year. Interest compounded quarterly.
    Interest amount $449.40. Principal amount + Interest amount is $5449.40.
    please explain this with formula.

    1. USE LOG

    2. Hi Ajay,

      And what is the interest rate (%)?

  46. NO WORDS ABOUT EXCEL IS USE FUL TO EVEYONE

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 :)