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!
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 🙂
Training Return on Investment
Bryce
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
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.
Umesh NK
Hai…!
Morgan L
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
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.
Steve Colley
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
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.
Mary Jo
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
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.
Kieran
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
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.
Elizabeth
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
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.
James Fagg
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
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.
Cameron Luken
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
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.
Stuart
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
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.
Angel Nguyen
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
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.
George
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
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.
George R
Why doesn’t the PMTmatch the FV (Principal plus interest) on excell when you multipy the payment by the terms?
Mynda Treacy
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.
Debra
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
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.
Lou
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
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.
Doug
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
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.
Kevin Swindells
great
Mynda Treacy
🙂 cheers, Kevin.
Rudi Mostert
Compound interest
Mynda Treacy
Hi Rudi,
Yes, this is the formula for compound interest.
Kind regards,
Mynda.
Glen Liburd
This seems like an awsome website. I am eager to take advantage of the information that you provide.
Mynda Treacy
Cheers, Glen 🙂
David
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
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.
Muhammad
Good learning tool
Mynda Treacy
Thanks Muhammad & Sylvia 🙂
Sylvia
Thanks for sharing! I love it when I find what I’m looking for. Clear en precise information.
YOHANNANKUTTY
good material
Mynda Treacy
Cheers 🙂
Chris
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
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.
melvin
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
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.
Rex Deme
Excel tip and tricks are like magic
Mynda Treacy
🙂 Cheers, Rex.
Sanat Jain
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
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.
Jo
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
Thanks Jo. Yes, I was having a ‘moment’ when I replied to Samir, as Petr pointed out above with his elegant solution 🙂
Ian
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
Hi Ian,
You can download a free Excel savings calculator at Vertex42 that will take into account weekly deposits:
https://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.
Gavin Roberts
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
Gavin Roberts
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
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
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
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
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
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
Hi Mynda,
I’m trying to calculate the following:
Principles amount is 34,262,750 with 7.5% compunded daily.
Mynda Treacy
Hi Fred,
Over what time is the principle invested?
Kind regards,
Mynda.
Bob Jameson
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
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
Hi Mynda
Thank you for your help. I will come back to you if I do find a solution.
Gav
Stacey Gambrell
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
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.
James
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
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
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
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.
Siddharth
Nice
Samir
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
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
…or =FV(7.25%/12,60,0,2260000,1)
Right?
Mynda Treacy
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.
Goran
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
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.
Chris Goosen
=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
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.
Chuck joseph
how would you calculate payments made monthly for 60 months compounded monthly?
Mynda Treacy
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.
Chuck joseph
good stuff
Daryll Bruno
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
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.