Question

When does RATE function return #NUM! error ? Explain its syntax and significance.

Hint:

RATE shows #NUM! when its iteration does not converge to 0.0000001 in 20 tries. Syntax is RATE(nper, pmt, pv, fv, type, guess).

Answer 1

Step 1: What the RATE function does:
The RATE function returns the interest rate per period of an annuity. An annuity is a series of equal payments made at regular intervals.

Step 2: When it returns the #NUM! error:
RATE is calculated by iteration, which means Excel tries one guess after another. It can have zero or more solutions.
If the successive results of RATE do not converge to 0.0000001 after 20 iterations, RATE returns the #NUM! error value.

Step 3: Syntax:
The syntax is =RATE(nper, pmt, pv, fv, type, guess). The arguments are:

Argument	Meaning
nper	Total number of payment periods in the annuity
pmt	Payment made each period
pv	Present value, the total amount that a series of future payments is worth now
fv	Future value, the cash balance after the last payment. If omitted, it is taken as 0
type	0 or 1. Zero or omitted means payment at the end of the period, 1 means at the beginning
guess	Guess for what the rate will be. If omitted, it is taken as 10%

Step 4: Significance:
RATE is useful in finding the effective interest rate on a loan or an investment.
It supports financial decision making, because the user can compare the true cost or return of different options.

Final Answer:
RATE gives #NUM! when it does not converge within 20 iterations. Its syntax is =RATE(nper, pmt, pv, fv, type, guess) and it helps find the effective interest rate (6 marks).