Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate Interest Rate
If I have opening and closing balances, and the number of equal monthly
payments made, can I calculate the 'effective' interest rate? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate Interest Rate
You want the Rate function. Details in Help.
Regards, Fred. "Mark Parent" wrote in message ... If I have opening and closing balances, and the number of equal monthly payments made, can I calculate the 'effective' interest rate? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate Interest Rate
"Mark Parent" wrote:
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 ----- "Mark Parent" wrote in message ... If I have opening and closing balances, and the number of equal monthly payments made, can I calculate the 'effective' interest rate? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
calculate weighted average effective interest rate | Excel Worksheet Functions | |||
How to Calculate the Interest rate | Excel Discussion (Misc queries) | |||
Calculate interest rate on long term investment | Excel Worksheet Functions | |||
Calculate Interest Rate when payment changes! | Excel Worksheet Functions | |||
calculate interest on an increasing balance with variable rate | Excel Discussion (Misc queries) |