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.
Now, you’d probably think that with over 50 financial functions in Excel there’d be one that would suit this scenario, but alas there isn’t. There is however a workaround.
First we need to know what the mathematical equation for compound interest is:
=PV x (1+R/M)^N
Where:
PV = Present Value – how much you put in the bank
R = Interest Rate – the annual rate the bank will pay you
M = Number of compounding periods – if the bank compounds the interest monthly then this is 12
N = number of investment periods – the compounding periods your money is invested for
Taking our example above
P = $10,000
R = 6%
M = 12
N = 24
Our formula in Excel would read:
=10000 x (1+.06/12)^24
=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!
What if you save $100 per month for 20 years at 6%?
The formula for this is slightly different and thankfully Excel has one built in:
It’s the FV function and it works like this:
=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 (20 years x 12 months)
pmt = the amount of the payment (represented as a negative number) -$100
pv = the amount we will start with (note if you make a lump sum deposit it will be a negative number) – $0
type = when payments are deposited; 0 = end of each period, 1 = beginning of each period
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.
{ 32 comments… read them below or add one }
good stuff
how would you calculate payments made monthly for 60 months compounded monthly?
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.
=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
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.
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
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.
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.
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.
…or =FV(7.25%/12,60,0,2260000,1)
Right?
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.
Nice
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
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.
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
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.
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.
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.
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
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.
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
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.
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.
Hi Mynda
Thank you for your help. I will come back to you if I do find a solution.
Gav
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
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
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.
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.
Thanks Jo. Yes, I was having a ‘moment’ when I replied to Samir, as Petr pointed out above with his elegant solution
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)
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.
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.