How to use the function rate?

[Lewis Koh]

Hi,

I tried looking up on rate( ) and used it to compound interest. Is this the correct function to use?

I have a sample here calculating the future value based on 3.44% monthly return with present value of 3000 and future value of 4500. I want to find out the new monthly return if I were to increase my future value to say 6000.

[Richard Buttrey]

Hi,

Excel does not actually have a specific function for compounding interest on an initial sum of money. The FV() function is designed returns to return the future value of an initial investment plus a series of constant payments.You've arrived at the right answer by using a very special version of it where you effectively ignore any future payments. And as you found you had to introduce a negative sign in the function to give you a positive result.

This one case where you should understand and use the general formula

PV * (1 + i ) ^ n

where PV is the initial sum, i the interest rate, (i.e. 5% would be .05) and n is the number of periods.

Hence in your case the formula would be

Please Login or Register  to view this content.

Regards

[Lewis Koh]

Hi,

Actually I'm looking for the rate of return per month. I can use the formula =((B5/B2)^(1/12))-1 to get the rate but just wondering if there is an excel function that I can use in excel 2007.