Excel--Calculate Compound Interest

[flynbrian99]

Anyone-

Does anyone know how to Calculate Compound Interest in Excel? For example, if you have a Money Market account that earns monthly interest and you want to know how much it will grow in a certain amount of time.

Thanks to whoever can help!

 

[flynbrian99]

I found this:
Compound interest is the amount that a dollar invested now will be worth in a
given number of periods at a given compounded interest rate per period. Although
Microsoft Excel does not include a function for determining compound interest,
you can use the following formula for this calculation:

=PV*(1+R)^N

where PV is present value, R is the interest rate, and N is the number of
investment periods.

If anyone has other ideas,would be much appreciated!

Thanks!

 

[xlhelp]

The function you are looking for is FV

=FV(rate,nper,pmt,pv,type)

You probably already know this, but just as reminder, the (interest) rate, number of periods (nper) should all be based on months. Type can be excluded since the default is Zero, end of the period.

Whether you use FV or the compund interest formula, the result is identical. You might get negative values when using the FV function because of the way Excel evaluates this particular function. Simply put, money out of your pocket is -ve and money into your pocket is +ve; as such, when using the FV function use -ve value for PV since it's money out of your pocket into an investment.

HTH

Indu

 

[flynbrian99]

Thanks for the info...
I am not quite sure when and where to use -ve...

Tell me if this is the right idea...

FV---Present Value
Rate---Interest Rate
nper---number of payments
pmt---amount of payments
pv---???
type---???

Can you clarify this for me...sorry I am kinda slow today!
LOL

Thanks!

 

[xlhelp]

Fv is future value.

PV is present value, the amount you are investing

Rate is the interest rate per month since it is going to be compounded monthly.

PMT would be applicable only if you are maing monthly installments into your investment.

Nper is the number of months the money is invested for.

The only three quantities you need to know are PV, rate and nper. So, 10,000 invested at 7% per annum for 1 year, the future value would be

=FV(7/1200, 12, -10000)

HTH

Indu

 

[flynbrian99]

This does not make sence...what is the 7/1200?

 

[xlhelp]

If the interest rate is 7% per year then the monthly interest rate is 7/12 and the 7% expressed in decimals is 7/100, so the result is that the monthly interest rate is 7/1200.

If the compound interest is calculated monthly then anything that is based on time such as the interest rate and the length of investment need to be converted to consistent units.

Indu

 

[flynbrian99]

OK...I see what you are saying but...I put this exact function in Excel =FV(7/1200,12,-10000) and the result was: $123,925.85. Can this be right?

I also tried this: =FV(3/1200,12,100,-5000) with $100 being a monthly payment to the $5000 existing in the account. Earning 3% interest for one year. My result was: $3,935.44?

For some reason I am not grasping this concept?
Thank you for your help...more help appreciated.

flynbrian-

 

[xlhelp]

Hi(gh) flynbrian99.

Haste makes waste. And in my case, it's so true lately.

Anyway, there is a comma missing in the formula that I typed.

It should be
=FV(7/1200, 12,, -10000)
Where 7/1200 is the monthly interest rate (in decimals), 12 is the number of months we are investing the money for, the blank space between the commas indicates that are no montly payments and -10000 is the money we are paying to the bank or the investment company. (Minus because the money goes out our pocket)

In case of your formula
=FV(3/1200,12,100,-5000)
the only thing wrong is positive 100. it should be
=FV(3/1200,12,-100,-5000)
again negative (-ve) since the money is going out of your pocket. So, with your formula with an intial deposit of $5,000 and a monthly payment of 100 at 3 percent annual interest rate, at the end of 12 months you would have $6,368.72, provided the interest is calculated monthly. You would make $168.72 on your investment in one year

If the interest was calulated quarterly, your formula would become
=FV(3/400,4,-300,-5000)
(-ve 300 using 100 a month) and you would make $3.46 less than before.

Indu

 

[flynbrian99]

Indu-

Thanks for your help! This is what I was trying to figure out...although I think I like the results from the other "wrong formula". My return was very nice!
Anyways, thanks again!

flynbrian-