Microsoft Office Tutorials and References

In Depth Information

**The Basic Excel Financial Functions**

If you get a #NUM! error or a result that is obviously incorrect on any of these basic

financial functions, the first place to look is the direction of the cash flows. Pay close

attention to the signs on the amounts in this section’s examples to get a better

understanding of how to sign the arguments.

Computing retirement payments

For some payment calculations, you may need to include a future value amount.

For this example, assume that you have $700,000 in a retirement account. You need to draw out

payments to live on for the next 20 years — but you also want $100,000 left to leave to heirs.

This formula computes how much you can take out every month (see Figure 11-8):

=PMT(6%/12,20*12,–700000,100000,0)

If your estimated 6% annual return on your money is accurate, you can withdraw $4,798.59 per

month, and still have $100,000 in the account 20 years from now.

Figure 11-8:
Calculating retirement payments.

Calculating rates

The RATE function computes the interest or discount rate on future cash flows. For transactions

where the interest rate is not specifically stated, the RATE function can be used to compute the

implicit interest rate (the rate that occurred whether stated or not). Its syntax is

RATE(nper, pmt, pv, fv, type, guess)

Payday loan rates

Payday loans are extremely short-term loans. Generally they are paid back in 14 days (the next

paycheck date), and a lender might charge $30 for every $100 borrowed.