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:
=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:
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%?
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