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.
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.
Note:
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.