Hi Louis!
Use the RATE function but watch out because it returns the effective
rate for the period counted by the nper argument:
=RATE(20*12,-1000,120000,0,0)
Returns: 0.662503162469502%
This is the effective rate per month because we looked at 20 years of
monthly payments.
To get the APR(12), just multiply by 12:
=RATE(20*12,-1000,120000,0,0)*12
Returns: 7.95003794963402%
Where the frequency of the cash flow is different from the frequency
of the APR that you want, then you need a more complex formula:
=RATE(20*26,-500,120000,0,0)
Returns: 0.348364748725388%
Which is the fortnightly effective rate.
No problem if you want the APR26; you just multiply by 26. But if you
want the APR12 you need to use:
=((1+RATE(20*26,-500,120000,0,0))^(26/12)-1)*12
Returns: 9.07589298954754%
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
