Mark Parent said:
If I have opening and closing balances, and the number of equal
monthly payments made, can I calculate the 'effective' interest rate?
Suppose you have the opening balance of 1000 in A1, the closing balance of
8000 in A2, the monthly payments of 100 in A3, and the number of payments
(48) in A4. Then:
=rate(A4,-A3,-A1,A2)
Note that the signs (positive or negative) for the "pmt" and "pv" arguments
must be different from the sign for the "fv" argument. I assume you mean
that you deposited monthly payments as well as the opening balance.
Alternatively, if you mean that you withdrew the monthly payments, then:
=rate(A4,A3,-A1,A2)
In either case, note that this is the __periodic__ rate corresponding to the
"nper" argument -- in this case, monthly. The "effective" rate is the
compounded annual rate, namely:
=(1+rate(...))^12 - 1
where "..." is whichever form of the RATE function above that you choose.
Alternatively:
=fv(rate(...),12,0,-1) - 1
Note: In some cases, you might need to format the cell explicitly as
Percentage with a desired number of decimal places.
----- original message -----
