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):

=RATE(1,0,200,–260,0,.01)*365/14

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

actually be.

Growth rates

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):

=RATE(26,–200,–40000, 48500,0,.01)*26

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.