present value compounded monthly

Hi

Use:

(FV/PV)^(1/n)-1

where FV = Future value, PV = present Value, n = number of periods.
The result is the interest rate per period

Originally Posted by elmasguapo

I know excel has a present value function, but can you use compounded monthly in that? For example, what interest rate, compounded monthly, will you need to take 20,000 to 50,000 in 10 years (compounded monthly)?

Yes, you can use the Excel financial functions. But in this case, you want to use RATE, not PV. To wit:

=12*RATE(10*12,0,20000,-50000)

10*12 is the number of months. 20000 is the present value; -50000 is the future value. Note the difference in the sign (minus v. plus).

Since the number of periods is months, RATE returns a monthly rate. The method for annualizing the monthly rate depends on the context.

Typically, we simply multiply by 12, as above.

But in some countries and for some purposes, we annualize by compounding the monthly rate. In that case, the formula would be:

=(1+RATE(10*12,0,20000,-50000))^12 - 1

PS.... It really does not matter, other than for compliance or comparison with reported rates, as long as we are consistent in the use of the annualized in other financial functions.

For example, if R1 is the annualized rate, the present value of the result of 50,000 after 10 years of monthly compounding -- that is, the amount that must be invested -- is either of the following formulas:

1. If R1 is =12*RATE(...), then =PV(R1/12, 10*12, 0, -50000).

2. If R1 is =(1+RATE(...))^12-1, then =PV((1+R1)^(1/12)-1, 10*12, 0, -50000)

Hi,
slight correction for monthly compounding

((FV/PV)^(1/n)-1)*12

i.e.
=((50000/20000)^(1/120)-1)*12

which gives the monthly compounding rate of 9.197979%