Fresh Excel Tips

Let’s say a bank advertises that if you deposit $500 each month for 12 years, you will have $100,000 at the end of the period. What is the interest rate the bank is paying? To answer this question, use the RATE function, which returns the interest rate per period of an annuity. Here is the syntax that is used:

RATE(nper, pmt, pv, fv, type, guess)
nper: The total number of payment periods in an annuity.
pmt: The payment made each period, which is a constant value.
pv: The present value. This is the amount that a series of future payments is worth right now.
fv: The future value. This is the amount you want after the last payment is made.
type: A number that indicates when payments are due. 0 or omitted indicates the end of the period, and 1 indicates the beginning of the period.
guess: A guess for what the interest rate will be. If omitted, Excel uses 10%.

To calculate the interest rate:

1. In cell B1 enter the number of periods in years.
2. In cell B2 enter the monthly amount to deposit.
3. In cell B3 enter the final value the bank has advertised.
4. In cell B5 type the following formula: =RATE(B1*12,-B2,0,B3,0)*12.
5. Press Enter.

1. How to Use the FV function to calculate total savings account balance
2. How to Use the PMT function to determine the payment of a loan
3. How to use the PV function to decide how much money to invest
4. Use the DDB function to calculate double-declining balance method
5. How to check for future dates using TODAY Function in Excel