Microsoft Office Tutorials and References
In Depth Information
The Basic Excel Financial Functions
If you borrow $200 and agree to pay $260 in 14 days, the interest rate is calculated with the
following formula (see Figure 11-9):
The period is set to one because the loan has only one payment. The period of one actually
represents a 14-day period, so the rate is converted to an annual percentage rate by dividing by 14
days and multiplying by 365 days. The result, 782%, is so large because the term is so short.
Figure 11-9: Calculating the interest rate on a short-term loan.
Interest rates are often stated as annual percentage rates (APRs), even if the term of
the loan is more or less than a year. Converting rates to APR, regardless of the term,
allows you to compare different loans. If you try to compare a monthly interest rate to
an annual interest rate, the monthly interest rate will look much smaller but may not
A common use of the RATE function is to calculate the growth rate on a retirement account.
Assume for this example that you have a $40,000 balance in your 401(k) at the beginning of the
year and $48,500 at the end of the year. You put $200 per paycheck into the account all year
(26 payments). This formula shows how your investments performed (see Figure 11-10):
The RATE function returns the growth rate over each of the 26 periods, so you must multiply it
by 26 to get the annual growth rate of 7.49%.
The guess argument is used by several financial functions. You can omit this argument
and let Excel use the default value, or you can explicitly provide a value. If the result is
not close to what it should be, you can try using a different value for the guess argument.