• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

How to Calculate Interest on Savings in Excel

You are here: Home / Excel Formulas / How to Calculate Interest on Savings in Excel
How to Calculate Interest on Savings in Excel
April 1, 2011 by Mynda Treacy

Ever had a spare $10,000 to put in a term deposit? If you did you might wonder what it would be worth at the end of 2 years at an interest rate of 6% compounded monthly.

It’s not as simple as =$10,000 x (1+.06 x 2)…although this will get you close. But if you’re a stickler for accuracy or you do this type of calculation in your work you’ll want to know the correct way to calculate the interest.

We can use the Excel FV function and it works like this:

Excel FV Function

=FV(rate, N, [pmt], [pv], [type])

Rate = Interest Rate per compound period – in this case a monthly rate (6% per annum / 12 months)

N = the number of periods you will make payments (2 years x 12 months)

[pmt] = the amount of the payment (represented as a negative number)

[pv] = the amount we will start with (also a negative number)

[type] = when payments are deposited; 0 = end of each period, 1 = beginning of each period.

Note: Arguments in [square brackets] are optional in the FV function. For example if you're not making regular payments you can leave the pmt argument out.

Taking our example above

Rate = 6%/12

N = 12*2

[pmt] = we'll leave this blank as we're not making regular payments

[pv] = $10,000

[type] - 1

Now let's plug it into our formula:

=FV(6%/12,24,,-10000,1)

=11,271.60

So now you know if you go to the bank tomorrow and deposit $10,000 at 6% annual interest compounded monthly at the end of two years you’ll find $11,271.60 in your account.

If you leave it there for 20 years you’ll have $33,102.04!

excel fv function compound interest function

What if you save $100 per month for 20 years at 6%?

 

Our formula:

=FV(6%/12,240,-100,0,1)

=$46,435.11

So now you know how to calculate what your saving plan might be worth in the future you can feed your own figures into the formula.

Want More Excel Formulas

Why not visit our list of Excel formulas. You'll find a huge range all explained in plain English, plus PivotTables and other Excel tools and tricks. Enjoy 🙂

How to Calculate Interest on Savings in Excel

More Financial Posts

More Financial Functions Posts

Excel ACCRINT Function

Excel ACCRINT Function

The Excel ACCRINT function returns the accrued interest for a security that pays interest periodically. Download the Excel ACCRINT Function sample file.
Invest $x at 4.75% for 5 Years to Make $15,000

Invest $x at 4.75% for 5 Years to Make $15,000

Calculate How Long to Make $1m with NPER

Calculate How Long to Make $1m with NPER

The Excel NPER function calculates how long it will take to reach your goal based on periodic, constant payments and a constant interest rate.

More Interest Posts

More Excel Formulas Posts

top excel functions for data analysts

Top Excel Functions for Data Analysts

Must know Excel Functions for Data Analysts and what functions you don’t have to waste time learning and why.
excel advanced formula environment

Excel Advanced Formula Environment

Excel Advanced Formula Environment is a long awaited, new improved way to write, name and store Excel formulas.
Pro Excel Formula Writing Tips

Pro Excel Formula Writing Tips

Must know Excel formula writing tips, tricks and tools to make you an Excel formula ninja, including a new formula editor.
excel shaping arrays

New Array Shaping Excel Functions

The Excel Shaping Array Functions makes it easier than ever to reshape arrays and ranges using these purpose built functions
excel nested if functions what not to do

Excel IF Formulas and What Not To Do

Excel IF formulas can get out of hand when you nest too many IFs. Not only do they become unwieldy they’re difficult for anyone to understand
excel image function

Excel IMAGE Function

The Excel IMAGE Function enables you to embed images in a cell using a formula. It supports BMP, JPG/JPEG, GIF, TIFF, PNG, ICO, and WEBP files

Excel VSTACK and HSTACK Functions

New Excel VSTACK and HSTACK functions makes combining arrays of cells easy and with some clever tricks we can extend their capabilities.
identify overlapping dates and times in excel

Identify overlapping dates and times in Excel

How to identify overlapping dates and times in Excel with a formula that checks a range of cells. Works with Dates and Times.
New Excel Text Functions

TEXTSPLIT, TEXTBEFORE and TEXTAFTER Functions

TEXTAFTER, TEXTBEFORE and TEXTSPLIT are exciting new Excel Text functions. They’re fairly self-explanatory, however TEXTSPLIT has some cool features.

Top 10 Intermediate Excel Functions

Take your Excel skills to the next level with this top 10 intermediate Excel functions. These are must know functions for all Excel users.
Category: Excel FormulasTag: financial, Financial Functions, interest
Previous Post:Training Return on InvestmentTraining Return on Investment
Next Post:Excel RAND and RANDBETWEEN FunctionsExcel RAND and RANDBETWEEN Functions

Reader Interactions

Comments

  1. Bryce

    October 23, 2012 at 11:52 pm

    Hi Mynda,

    Please could you help me. I have a loan from a family member, we have come to an agreement that in 6 years i will pay back the full accumulated amount amount over a period of 3 years, in which interest is still being accumulated. However if i can start paying back the principal before 6 years time i would prefer that.

    please could you help me with a model on excel that i could update on a monthly basis with regerd to the balance and repayments if i decide to pay back the balance earlier. like a monthly tracking model.

    help on this would be appreciated as i am struggling to find an appropriate method.

    thanks

    • Mynda Treacy

      October 25, 2012 at 10:01 pm

      Hi Bryce,

      You need to have a row for each month and columns that contain the inputs for the formula. One of these columns will be the carried forward balance from the previous row.

      Kind regards,

      Mynda.

  2. Umesh NK

    October 20, 2012 at 4:53 pm

    Hai…!

  3. Morgan L

    October 16, 2012 at 6:33 am

    okay I am trying to find my intrest on my deposits for a retirement plan. My current savings has 1000, and I am depositing 200 dollars every month for 30 years. My annual Rate if return is 10%. I have tried to us the FV fuction to do this equation but I keep coming up with the wrong answer my teacher said the anwer should be 113.11

    • Mynda Treacy

      October 16, 2012 at 2:01 pm

      Hi Morgan,

      The answer can’t possibly be 113.11. Even without the additional deposits, 10% (not compounded) on $1000 is $100 and that’s just one year, over 30 years it’s $3000, again not compounded. Then you’re adding another $200 each month, that’s $200x12x30 = $72000, plus compounding the interest.

      This is the formula:

      =FV(10%/12,12*30,-200,-1000,1)

      =$475,702.46

      Kind regards,

      Mynda.

  4. Steve Colley

    October 13, 2012 at 6:49 pm

    Hi, can you explain the formula (preferably in excel) if you were to invest a monthly amount that has a 15% interest rate per year, you increase your contributions every year by 15% and you will contribute for the next 20 years.

    Most formulas have that you will invest a fixed amount every month for a fixed period. can you also explain how it will work if you have a lump sum to initially invest too.

    • Mynda Treacy

      October 15, 2012 at 10:53 pm

      Hi Steve,

      There isn’t one formula that I’m aware of. I suggest you use the FV function and set up your worksheet with a cell for each year and increase the pmt amount by 15%. If you have a lump sum then you enter it as the pv argument.

      Kind regards,

      Mynda.

  5. Mary Jo

    October 4, 2012 at 11:09 pm

    I need help compounding interest for earnings in a retirement plan. Deposits are made monthly, interest rate changes each month based on stock market.

    • Mynda Treacy

      October 5, 2012 at 8:21 am

      Hi Mary Jo,

      You will need a separate formula for each month since the interest rates changes monthly.

      Set up a table with all of your inputs and in one column insert your formula that picks up the relevant figure for that month.

      Kind regards,

      Mynda.

  6. Kieran

    October 3, 2012 at 9:54 am

    Hi Mynda,

    I have setup a intra-year compounding interest forecast spreadsheet however I am having trouble with accuracy.

    What I know:
    – Interest rate: 5.75% p.a
    – Compounding monthly
    – Calculated daily
    – Starting principle $xx

    I would like to figure out an efficient formula to calculate FV month to month; the difficulty is that my additional deposits are at random days during the month and I can only find formulas to calculate regular deposits either at the start or the end of the month. For example if I deposit during October on the 17th then the daily calculated interest will adjust for the remainder of the month to the increased principle, and therefore the interest will be higher for the remaining 13/14 days of the month. Is there a formula to avoid having to create a day by day spreadsheet? One where I may be able to enter into the formula the specific day of the month and the deposited amount to adjust interest daily interest calculations for the remainder of the month.

    Sorry for the essay! Any advice/help would be greatly appreciated!

    Thanks in advance.

    • Mynda Treacy

      October 3, 2012 at 12:03 pm

      Hi Kieran,

      Ugh, I don’t envy you. I don’t know of any one formula that will do what you want. The fact that your account calculates interest daily, but compounds monthly and your deposits are random means you will need some interim steps in calculating the interest forecast.

      I would set up a worksheet that tracks your opening balance, deposits, withdrawals, daily interest and compound interest by date in separate columns.

      Good luck!

      Mynda.

  7. Elizabeth

    September 23, 2012 at 1:06 pm

    Hi,
    Can you help me with this? Determine the effective annual yield for one invested year at 7.9 % compound semiannually.
    Thank you

    • Mynda Treacy

      September 24, 2012 at 9:07 pm

      Hi Elizabeth,

      You can use the EFFECT function for this:

      =EFFECT(7.9%,2)
      =8.06%

      Where ‘2’ is the number of compounding periods.

      Kind regards,

      Mynda.

  8. James Fagg

    August 31, 2012 at 4:32 pm

    I have question I am having a terrible time with inputting the formula into excel and getting the correct answer can you help me out?

    Here is the question:

    I just went to the car dealership, and they were willing to give me a 60 month loan for $450 a month on my $20,000 car. What is the interest rate on that? If the bank is willing to give me 6%, should I go with the bank or the dealership?

    I know the payments giving the interest of 6% from the bank is $386.66.

    I am trying to figure out the interest rate from the dealership though using a formula in excel and this what I am plugging in: =RATE(60,450,-20000,0)

    Where am I going wrong? I keep getting 1% which I know is not correct and online auto calculator helped to get 12.5% but that is not helping with my excel plug in.

    Thanks for your time.

    James

    • Mynda Treacy

      September 2, 2012 at 10:24 pm

      Hi James,

      The result your RATE formula returns is the monthly interest rate (remember you entered 60 monthly payments, which is 5 years). If you multiply it by 12 (months) you get 12.5%.

      Kind regards,

      Mynda.

  9. Cameron Luken

    August 29, 2012 at 12:45 am

    Hi Mynda,

    This is a question on determining the value of a day trading forex account after x number of years.

    I’m stuggling to find a formula this following question:

    Initial Investment: $50,000
    Weekley Contributions: $2200
    Investment account returns 5% per “month”.
    Compounded Weekley

    What will be the total value of the investment account after x number of years?

    • Mynda Treacy

      August 29, 2012 at 9:52 am

      Hi Cameron,

      =FV(0.05*12/52,52,-2200,50000,1)

      For one year/52 weeks.

      If you want to alter the time period change the ‘nper’ argument to reflect the number of weeks of your investment.

      This is assuming the investment is made at the beginning of the week. If not change the ‘type’ argument to 0.

      Kind regards,

      Mynda.

  10. Stuart

    August 14, 2012 at 8:24 am

    Hi Mynda. Great article thanks – I wonder if you could help me with this:

    I have an investment account into which I pay £X per month. I know the value of the portfolio now after Y months, but is there an easy way of calculating what the equivalent AER would be had I paid the same amount into a simple compound savings account each month instead? I’m trying to work out how well the investment is performing. Many thanks!

    • Mynda Treacy

      August 14, 2012 at 10:41 pm

      Hi Stuart,

      You can use the RATE function to calculate the APR (I think it’s calculating the APR, but I can’t find anything to say whether it does or not).

      e.g. Let’s say you paid $100 per month into an account and after 3 years it was worth $4000. Your formula would be:

      =RATE(3*12,-100,,4000,1)*12
      =6.74%

      You can then use the EFFECT function to calculate the AER.

      =EFFECT(6.74%,12)
      =6.95%

      I hope that helps.

      Kind regards,

      Mynda.

  11. Angel Nguyen

    July 24, 2012 at 5:41 am

    Hi Mynda,

    First I’d like to say, WOW, you are very smart. Knowing what formulas go with what situation has been a daunting task for me-so I ask you-I need additional income so…

    My saving account interest rate is 0.05%
    My starting balance is $29.56 (which the bank will match up to 100% for the first 3 months
    My monthly deposit will be $25.00 (again including interest from previous months)
    I need to know the formula to be able to know the present value that can be liquidated at any time (for emergencies) on monthly basis
    Thank you

    • Mynda Treacy

      July 25, 2012 at 8:27 pm

      Hi Angel,

      Thank you for your kind words.

      You need to do something similar to ‘George’ above. See my reply to him.

      BTW I hope your interest rate is at least 5% p.a. otherwise you’re not going to make any money 🙁

      Kind regards,

      Mynda.

  12. George

    July 20, 2012 at 3:16 am

    Hi Mynda,
    I would like help calculating and making excel table where I can see the total accumulated amount each month.
    Monthly net rental income is $5000 for first year. Each month this rental income is invested where it is giving 8% annual return. Monthly rental income will increase by 5% every year for 15 years, and continue to invest this monthly income gives 8% annual return.

    • Mynda Treacy

      July 20, 2012 at 10:40 pm

      Hi George,

      How about this:

      1. In column A enter your $5000 rental income starting on row 2 (we’ll assume row 1 has your headers). Each row represents one month. When you get to the 13th month increase the $5000 by 5% and so on for each year. You could make this quicker with a formula if you wish. e.g. in the 13th month your formula will reference the cell above and add 5%. The 14th, 15th, 16th…will equal the cell containing the 13th month figure. Then copy the second set of 12 months down 13 more times.

      2. In column B enter your FV function. The first cell will contain this formula =FV(8%/12,1,-A2,,1)

      3. In cell B3 enter this formula =FV(8%/12,1,-A3,-B2,1) and copy it down your column.

      Each row in column will show you the running balance of your rental income + interest.

      I hope that helps.

      Kind regards,

      Mynda.

  13. George R

    July 15, 2012 at 10:11 am

    Why doesn’t the PMTmatch the FV (Principal plus interest) on excell when you multipy the payment by the terms?

    • Mynda Treacy

      July 25, 2012 at 4:24 pm

      Hi George,

      The PMT function calculates the repayment on a loan, where as the FV function calculates the future value of an investment based on periodic, constant payments and a constant interest rate.

      E.g. I can use the PMT function to calculate the repayment on a $10000 loan at 8% interest over 12 months:

      =PMT(8%,12,-10000,,)
      =1,326.95

      If I plug the result of the above formula into the FV function like this I get 0:

      =FV(8%,12,-1326.95,10000,)

      Can you show me your example that is not doing as you’d expect.

      BTW there is an explanation of the Excel functions for personal financial decisions: the PV, FV, PMT, PPMT, and IPMT functions here.

      Kind regards,

      Mynda.

  14. Debra

    July 10, 2012 at 3:49 am

    I am trying to learn how to double my amount. How many years it will take.

    Example: if I put 100.00 in at 5% compounded monthy how long will it take me to turn my original amount of 100 into 200. Can you show my the steps to that?

    • Mynda Treacy

      July 10, 2012 at 9:54 pm

      Hi Debra,

      You can use the NPER function to calculate this.

      =NPER(5%/12,0,-100,200)/12

      =13.89 years

      Note: I assumed your interest rate is annual and divided it by 12.

      Read more about the NPER function here.

      Kind regards,

      Mynda.

  15. Lou

    July 5, 2012 at 12:37 am

    Hello Mynda,

    I am struggling to get the right formula in my spreadsheet. So here’s the scenario. We borrowed 250,000 @12% compounded monthly but no specific term. We started in 2005. We then made a lump sum payment last year so how do I know how much that we owe now.

    Thank you

    • Mynda Treacy

      July 6, 2012 at 3:08 pm

      Hi Lou,

      You will need to do a few calculations:

      1. calculate the compound interest up to the point in time where you made the lump sum payment.
      2. Deduct your lump sum.
      3. Then take the balance (i.e. the amount after the lump sum has been deducted) and work out the compound interest since you paid the lump sum, up to now.

      I hope that helps.

      Kind regards,

      Mynda.

  16. Doug

    June 22, 2012 at 5:04 am

    Good site….how do I created a spreadsheet by month using the formula on this page for compounding monthly interest. Got the total I wanted for the 5 years, but need to see it month by month….thanks.

    • Mynda Treacy

      June 22, 2012 at 7:30 pm

      Hi Doug,

      To see it month by month you would need to change the number of periods to reflect the month you want to see. e.g. for the first month the number of periods would be 1, the second month would be 2, then take away the result of the first month if you only want to see the next change, and so on.

      Hope that makes sense.

      Kind regards,

      Mynda.

  17. Kevin Swindells

    June 20, 2012 at 12:59 am

    great

    • Mynda Treacy

      June 20, 2012 at 8:29 am

      🙂 cheers, Kevin.

  18. Rudi Mostert

    June 16, 2012 at 10:53 pm

    Compound interest

    • Mynda Treacy

      June 17, 2012 at 1:18 pm

      Hi Rudi,

      Yes, this is the formula for compound interest.

      Kind regards,

      Mynda.

  19. Glen Liburd

    June 16, 2012 at 1:28 am

    This seems like an awsome website. I am eager to take advantage of the information that you provide.

    • Mynda Treacy

      June 16, 2012 at 8:23 pm

      Cheers, Glen 🙂

  20. David

    June 10, 2012 at 1:27 pm

    How to use excel to calculate real rate of return factor for 5 & 10 years?
    Let say:
    Inflation rate: 3%
    Return rate: 8%

    Ans:4.7171(5years) & 8.7861(10years)

    • Mynda Treacy

      June 10, 2012 at 9:06 pm

      Hi David,

      I know that to calculate the real interest rate after inflation you:

      =( 1 + Interest Rate ) / ( 1 + Inflation Rate ) – 1
      =(1+8%)/(1+3%)-1
      =4.854737%

      I then calculated the discount rate [calculated as 1/(1+r)^n and where r is the real interest rate] and I get 4.3469 for 5 years and 7.7765 for 10.

      Even just using 8%-3% = 5% and adding up the present value discount factors for 5 years gives you 4.3294 or for 10 years =7.7216.

      I’m not sure how you got your answers. Please let me know so we can figure it out.

      Kind regards,

      Mynda.

  21. Muhammad

    June 9, 2012 at 6:44 pm

    Good learning tool

    • Mynda Treacy

      June 10, 2012 at 5:05 pm

      Thanks Muhammad & Sylvia 🙂

  22. Sylvia

    June 8, 2012 at 9:45 pm

    Thanks for sharing! I love it when I find what I’m looking for. Clear en precise information.

  23. YOHANNANKUTTY

    June 5, 2012 at 4:37 am

    good material

    • Mynda Treacy

      June 5, 2012 at 7:57 pm

      Cheers 🙂

  24. Chris

    June 3, 2012 at 12:12 pm

    first of all great work with all the formulas

    here is my request

    i am using the formula for compound interest =FV(6%/12,240,-100,0,1)

    my question is if i am going to make a lump sum payment once a year with tax returns, ie. 3,000, what would i insert that into the formula

    • Mynda Treacy

      June 5, 2012 at 8:18 pm

      Hi Chris,

      I think this is what you’re after:

      =FV(6%/12,240,-100,0,1)+FV(6%,240/12,3-3000,0,0)
      =$156,681.53

      I presume your investment period was 20 years i.e. 240/12.

      Kind regards,

      Mynda.

  25. melvin

    May 25, 2012 at 8:22 am

    how do we calculate the compound interest for 25 years when the interest is 12% per annum and starting amount is 6000. what will be the interest after 25 years including principle interest and compound. what will be the amount please i need your help.

    • Mynda Treacy

      May 25, 2012 at 8:45 pm

      Hi Melvin,

      You’ve left out whether the interest rate is compounding daily, monthly or annually. I’ll assume annually:

      =FV(12%,25,0,-6000)

      =$102,000.39

      Made up of $6000 principal and $96,000.39 interest.

      If it’s compounding monthly then your formula will be:

      =FV(12%/12,25*12,0,-6000)

      =$118,730.80

      Made up of $6000 principal and $112,730.80 interest.

      There isn’t such a thing as ‘principal interest’. Just principal and interest. The interest can be compounded, or simple interest. Simple interest is where the interest is not added to the principal.

      I hope that helps.

      Kind regards,

      Mynda.

  26. Rex Deme

    May 18, 2012 at 7:57 am

    Excel tip and tricks are like magic

    • Mynda Treacy

      May 18, 2012 at 8:52 am

      🙂 Cheers, Rex.

  27. Sanat Jain

    May 7, 2012 at 6:11 pm

    I have multiple deposits & want to calculate interest on it in a single excel sheet with the year end date on 31st mar of every year. Kindly give me the formula for the same. (i.e. I need the compounding interest formula with the days calculation)

    • Mynda Treacy

      May 8, 2012 at 9:26 pm

      Hi Sanat,

      I’m having difficulty picturing your data. Can you please log a ticket on the help desk and attach your file, or an example of your data so that I can help you further.

      Thanks,

      Mynda.

  28. Jo

    April 22, 2012 at 5:58 pm

    You can use the FV formula in excell to calculate the future value of a lumpsum investment, so it works for both regular payment calculations as well as lumpsum calculations. Just mentioning as you indicate that excell does not have a formula for lumpsum calculation and hence you built your own formula.

    • Mynda Treacy

      April 23, 2012 at 7:35 pm

      Thanks Jo. Yes, I was having a ‘moment’ when I replied to Samir, as Petr pointed out above with his elegant solution 🙂

  29. Ian

    April 6, 2012 at 12:50 pm

    I am creating a spread sheet and I need to know the formula to add weekly additional payments
    start $300
    interest rate 6.01%
    additional payments per week $300

    so I think the formula will be needed to be broken down so i will need a column for starting balance, interest made for the 7 days
    then the additional payment added and the 7 days of interest and so on
    new total of all the interest made for the month

    or something like that

    if you could give me the formula for that or even a template that would be awesome,
    Ian

    • Mynda Treacy

      April 6, 2012 at 8:59 pm

      Hi Ian,

      You can download a free Excel savings calculator at Vertex42 that will take into account weekly deposits:

      http://www.vertex42.com/Calculators/savings-interest-calculator.html

      Alternatively in your case you can also use the FV function:

      =FV(6.01%/52,(52*5),-300,-300,0)

      Since your initial deposit and ongoing deposits are the same.

      This assumes your interest is compounded weekly and payments are made at the end of each week.

      I hope that helps.

      Kind regards,

      Mynda.

  30. Gavin Roberts

    March 12, 2012 at 1:09 pm

    Hi Mynda
    I have used the formula that you gave me for the Mortgage Duty. It will be out by $5 at times, which just makes a few cents difference to the overall calculation, no big problem.
    But my other issue is, how would I add the amount calculated (from your formula) to the final amount financed without me getting a circular reference error? Is there a way to put the calculated figure into a cell without the formula?
    I have 5 different finance options and I don’t want to have to put them in manually each time.
    Thanks for the help thus far.
    Gav

  31. Gavin Roberts

    March 9, 2012 at 1:39 pm

    How would I calculate the Mortgage duty on a asset laon.
    Eg. The car costs $19000.00, the finance fees are $991.49= $19991.49
    The total amount borrowed would be $20016.49, because of the $25.00 mortgage duty. The problem is that the duty on $19991.49 is $21.00, $25.00 is on the full amount borrowed, but the worksheet keeps giving me Cicular reference warning. How would I work out what the duty would be and add it to the amount borrowed?
    Thanks

    • Mynda Treacy

      March 9, 2012 at 2:27 pm

      Hi Gavin,

      I’m not following. In one sentence you say the duty is $25 but then you say it’s $21. Which one is it? Are you saying that the duty amount depends on the amount borrowed? Or is their duty on the car cost + fees, then duty again on the amount borrowed?

      Please tell me in a sentence how your calculation should work. e.g. If amt. borrowed is > $20000 then duty is $25 otherwise it’s $21. Or what ever it is.

      Hopefully then I’ll understand what you’re trying to do and I can be more helpful. Alternatively send me your workbook and I’ll take a look.

      Kind regards,

      Mynda.

      • Gavin Roberts

        March 9, 2012 at 4:10 pm

        Hi Mynda
        Sorry, I strugle to put things in words. The amount borrowed is $19991.49 + $25.00=$20016.49, but the $25 duty is worked out on the full amount of $20016.49. My problem is how would I work out the duty on the full amount if I dont have the duty amount.
        Here is the calculation I was given
        Mortgage Stamp Duty only applies on consumer loans when the total borrow exceeds $35000.

        once that threshold is exceeded the calculation is as follows

        0 – $16000 = $5
        >$16000 = $5 + $4 for every $1000 (or part thereof) over $16000

        example

        Loan amount $47565
        S/D = $5 + $4 x ((47565 – 16000)/1000)
        = $5 + $4 x (31565/1000) rounded UP to next $1000 = 32000/1000
        = $5 + ($4 x 32)
        = $133

        (The loan amount of $47565 above includes the $133.

        I hope this explains it a bit better. If not how would I send you my worksheet.
        Gav

        • Mynda Treacy

          March 9, 2012 at 7:58 pm

          Hi Gavin,

          I get it now 🙂

          How about:

          =IF(A1>35000,ROUNDUP((A1-16000)/1000,0)*4+5,0)

          Where cell A1 contains your loan amount.

          I hope that helps.

          Kind regards,

          Mynda.

          • Mynda Treacy

            March 11, 2012 at 10:13 pm

            Hi Gavin,

            I woke last night with an epiphany. The formula I suggested will work except where the gross mortgage amount (including stamp duty) doesn’t result in the wrong rounding. So I guess you could say it doesn’t work 🙁

            e.g. let’s say the gross amt is $47,001. Stamp duty is actually $129 but my formula will return $133.

            And that’s because when I take $16000 off the amount to get my amount ($47,001- $16,000 = $31,001 rounds up to 32. 32 x $4 + 5 = $133

            $47,001 – $133 gives me a net mortgage of $46,868

            But $46,868 – $16,000 = $30,868 / 1000 and rounded up = 31 not 32

            I hate to say this but I’m stumped. The rounding to the nearest 1000 makes it difficult to establish what the true net amount is…at least with my level of math ability.

            If you do find a solution I’d love you to come back and share it with us.

            Sorry I can’t help you this time.

            Kind regards,

            Mynda.

          • Mynda Treacy

            March 13, 2012 at 9:03 pm

            Hi Gav,

            As you know I came up with a solution to the rounding issue with you offline, but I’d like to share it with our readers so here it is:

            =IF(B16ROUNDUP((B16-(ROUNDUP((B16-16000)/1000,0)*4+5)-16000)/1000,0),ROUNDUP((B16-16000)/1000,0)*4+5-4,ROUNDUP((B16-16000)/1000,0)*4+5))

            Where B16 contains the Total Amount Financed.

            Also note: this formula results in a circular reference, which is actually necessary.

            To allow circular references or iterations:

            Excel 2007: Windows button > Excel Options > Formulas > Calculation Options > Check Enable iterative calculation box and make Maximum Iterations = 1

            Excel 2010: : File tab > Options > Formulas > Calculation Options > Check Enable iterative calculation box and make Maximum Iterations = 1

            BIG NOTE: you must press F9 to recalculate the stamp duty each time you change any of the figures that make up the Total Financed, otherwise the stamp duty amount could be wrong.

            Phew.

            Mynda.

          • Fred

            June 16, 2012 at 12:57 am

            Hi Mynda,
            I’m trying to calculate the following:
            Principles amount is 34,262,750 with 7.5% compunded daily.

          • Mynda Treacy

            June 16, 2012 at 8:23 pm

            Hi Fred,

            Over what time is the principle invested?

            Kind regards,

            Mynda.

          • Bob Jameson

            September 2, 2012 at 4:36 am

            Mynda

            I am trying to work out the accumulated interest on a loan.
            I have a formula from a long long time ago but cannot remember all the definitions. The basic one is Accu interest =(Loan Amount*(((1+(7/200))^(28/365))-1)).

            The 7/200 in the interest rate (N) and the 28/365 also in the (N) but the interest rate is compound monthly and i think the payments are bi weekly. That is the part i can not remember. Can you tell me the base formula for compound monthly interest rates but monthly, bi weekly and 24 payment per year. hope this makes sense.

          • Mynda Treacy

            September 2, 2012 at 10:54 pm

            Hi Bob,

            If the interest is compounded monthly then you just need to adjust your payments to reflect a monthly schedule. i.e. If the interest is calculated monthly then it doesn’t matter whether you make payments bi-weekly or monthly, the end result is the same, so I’d just adjust my payment figure to reflect the monthly amount.

            You may need to take into account that some months are longer than others, so if you want to be absolutely accurate I’d enter the amounts for each month into a table and calculate your FV formula month by month and then add them up.

            I hope that helps.

            Kind regards,

            Mynda.

          • Gavin Roberts

            March 12, 2012 at 8:25 am

            Hi Mynda
            Thank you for your help. I will come back to you if I do find a solution.
            Gav

  32. Stacey Gambrell

    March 4, 2012 at 11:31 am

    I need to figure out which formula in excel I would use to find the investment amount if my bank pays 4.75% interest compounded monthly in 5 years ending with 15,000. Thanks so much for your help.

    • Mynda Treacy

      March 5, 2012 at 9:29 pm

      Hi Stacey,

      Thanks for your question. You can use the PV function to calculate this:

      =PV(4.75%/12,60,0,-15000,0)

      =$11,834.50

      Kind regards,

      Mynda.

  33. James

    February 26, 2012 at 4:32 pm

    Mynda,

    Could you please produce a formula that involves stock trading? If I have a starting amount of $2,000 and want to, say, make an average of 4% per trade, how many trades would I need to make if I invested the full amount each time to make $1,000,000? Of course, this scenario would have to exist living in a dream world that there would only be gains on every trade. Thanks so much!

    James

    • Mynda Treacy

      February 28, 2012 at 2:25 pm

      Hi James,

      Ah, this takes me back to the days when I was studying for my accounting exams 🙂

      To work this out you can use the NPER function as follows:

      =NPER(4%,0,-2000,1000000)

      Result: 158.4521953

      Good luck with your trading!

      Kind regards,

      Mynda.

      • James

        February 28, 2012 at 3:43 pm

        Mynda,

        Wow, that is awesome and it works perfectly with different %’s. Glad I could bring you back. I was wondering 2 things: 1) is there a way I can show that formula so that it produces a spreadsheet with all the numbers of each trade that is made in the original formula (was the redundant?) 2) can there be a spreadsheet produced that will change the numbers ON my actual trades that I make? Basically, a running spreadsheet that changes with actual gains and/or loses and shows me how I’m doing toward my goal. For instance, on my first trade I made 37% and on my second trade I made 4%. Hope that make sense!

        Thanks so much it is greatly appreciated!

        James

        • Mynda Treacy

          February 29, 2012 at 6:48 am

          Hi James,

          I’m glad that was what you were after.

          1. I’m not sure what you mean in your first question.

          2. To find out how many more trades at the average rate of return you need to make $1m you could do something like this:

          =NPER(reference cell containing the average actual trades,0,-(reference cell containing the actual money available to invest now),1000000)

          Kind regards,

          Mynda.

  34. Siddharth

    February 10, 2012 at 6:56 pm

    Nice

  35. Samir

    December 30, 2011 at 4:52 pm

    Compound Interest monthly for $2,260,000.00 at 7.25% annual interest for 5 years: Is it $3,206,970.34?

    Also, will the formulas (functions) you give out be stored permanently in my Excel, for future use in plain English, or would I have to enter them every time?

    Thanks for your help.

    • Mynda Treacy

      December 30, 2011 at 10:43 pm

      Hi Samir,

      Thank you for your question.

      Your calculation only allows for interest to be compounded annually. Compounding monthly returns the result: $3,243,893.00. This is the formula for compounding monthly =2260000*(1+0.0725/12)^(5*12).

      Kind regards,

      Mynda.

      • Petr

        February 22, 2012 at 9:29 pm

        …or =FV(7.25%/12,60,0,2260000,1)

        Right?

        • Mynda Treacy

          February 22, 2012 at 10:00 pm

          Yes, correct. Thanks for your elegant solution, Petr.

          I must have been having a mental blank the day I replied to Samir 🙂

          Also, if you want a positive figure you could use:

          =FV(7.25%/12,60,0,-2260000,1)

          Kind regards,

          Mynda.

  36. Goran

    December 13, 2011 at 12:32 pm

    Hi Mynda,
    What would be the formula for the following problem:
    I wish to calculate the future value for a savings account calculated daily but compounded monthly at 5.9%
    In addition the formula should have a starting amount (say $5000) and a recurring monthly contribution of $1000 over a time period ‘years’
    I have tried using the traditional FV and PV functions but could not return the correct result.
    The variables are:

    Interest Rate 5.9%

    Initial Deposit $5000

    Monthly deposit $1000

    Years (invested)

    Future value $

    I wish to use this to compare different savings accounts. Your help is really really appreciated!
    Thanks
    Goran

    • Mynda Treacy

      December 13, 2011 at 4:48 pm

      Hi Goran,

      Your formula would be:

      =FV(5.9%/12,12,0,-5000)+FV(5.9%/12,12,-1000,0,1)

      =$17,693.61

      the first FV calculates the compound interest on the deposit and the second FV calculates the interest and savings.

      But here are some caveats:

      1. Your interest rate of 5.9% may already include an assumption of daily compounding and therefore the formula above would yield a different result. You will need to check if it’s an AER or not. If it is an AER then you need to convert it back to the nominal rate for it to work in the above formula. To convert AER to the nominal rate you need to take the AER to the 12th root, and I’m sorry, I don’t know how to do the 12th root in Excel.

      2. I’ve assumed you’ll be making deposits on the first day of each month. If not replace the last 1 in the formula with a zero.

      Alternatively you could use the calculator on this website:

      ING Savings Calculator

      I hope that helps.

      Kind regards,

      Mynda.

  37. Chris Goosen

    December 6, 2011 at 8:04 pm

    =FV(6%/12,240,-100,0,1)
    I am a bit rusty. I want to deposit an amount into my grandsons account when he turns 9yrs of age in Dec . So 9 years @ monthly deposits of 10 units I know the / means divide but what does , mean ? is the above 21 multiplied by 240? minus 100 ????
    Thanks

    • Mynda Treacy

      December 6, 2011 at 10:11 pm

      Hi Chris,

      The commas simply separate each component of the formula so that Excel knows what to do with each figure.

      So, say you put $100 in the bank when your grandson turns 9 in December, then you put another $10 in each month for the next 9 years at 6% interest compounded monthly. Your formula would look like this:

      =FV(0.5%,108,-10,-100,1)

      I hope that helps.

      Kind regards,

      Mynda.

  38. Chuck joseph

    November 3, 2011 at 10:24 pm

    how would you calculate payments made monthly for 60 months compounded monthly?

    • Mynda Treacy

      November 4, 2011 at 9:43 am

      Hi Chuck,

      You haven’t given me all of the information to answer your question but it would be something like this:

      =PV x (1+R/12)^60

      I hope that points you in the right direction.

      Kind regards,

      Mynda.

  39. Chuck joseph

    November 3, 2011 at 10:17 pm

    good stuff

    • Daryll Bruno

      July 19, 2012 at 2:37 am

      Help, I need a formula to find out the time frame in years to accumulate 160,000 in a savings account paying 8% compounded monthly where I make monthly deposits of 1700. I would prefer a formula I can do on calculator not in excel. I know this will involve a lot of logarithms because of the unknown N-timeframe

      daryll B

      • Mynda Treacy

        July 20, 2012 at 10:25 pm

        Hi Daryll,

        Phew, that takes me back to my accounting exam days…I’m not sure I want to go there 🙂

        I found this post that has your solution. See Calculation #5.

        Kind regards,

        Mynda.

Sidebar

More results...

Shopping Cart

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now
  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
 
  • About My Online Training Hub
  • Contact
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Copyright © 2023 · My Online Training Hub · All Rights Reserved

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.

Download A Free Copy of 100 Excel Tips & Tricks

excel tips and tricks ebook

We respect your privacy. We won’t spam you.

x