RATE
The RATE function returns the interest rate of an investment, loan or annuity based on a series of regular periodic cash flows (payments of a constant amount and all cash flows at constant intervals) and at a fixed interest rate.
RATE(num-periods, payment, present-value, future-value, when-due, estimate)
num-periods: A number value representing the number of periods. num-periods must be greater than or equal to 0.
payment: A number value representing the payment made or amount received each period. payment is often formatted as currency. At each period, an amount received is a positive amount and an amount invested is a negative amount. For example, it could be a monthly loan payment (negative) or the periodic payment received on an annuity (positive).
present-value: A number value representing the initial investment, or the amount of the loan or annuity. present-value is often formatted as currency. At time 0, an amount received is a positive amount and an amount invested is a negative amount. For example, it could be an amount borrowed (positive) or the initial payment made on an annuity contract (negative).
future-value: An optional number value that represents the value of the investment or remaining cash value of the annuity (positive amount), or the remaining loan balance (negative amount), after the final payment. future-value is often formatted as currency. At the end of the investment period, an amount received is a positive amount and an amount invested is a negative amount. For example, it could be the balloon payment due on a loan (negative) or the remaining value of an annuity contract (positive). If omitted, future-value is assumed to be 0.
when-due: An optional modal value that specifies whether payments are at the beginning or end of each period. Most mortgage and other loans require the first payment at the end of the first period (0), which is the default. Most lease and rent payments, and some other types of payments, are due at the beginning of each period (1).
end (0 or omitted): Payment is treated as being received or made at the end of each period.
beginning (1): Payment is treated as being received or made at the beginning of each period.
estimate: An optional number value specifying the initial estimate for the rate of return. estimate is a number value and is entered as a decimal (for example, 0.08) or with a per cent sign (for example, 8%). If omitted, 10% is assumed. If the default value does not result in a solution, initially try a larger positive value. If this does not result in an outcome, try a small negative value. The minimum value allowed is –1.
Example |
|---|
Suppose you would like to help your daughter buy her first house when she is old enough. She has just turned 3 and you expect she will need her own place in 15 years (num-periods is 15*12). You think you will need to have £150,000 (future-value, which is positive because it will be a cash inflow) set aside in a savings account by the time she turns 18. You can set aside £50,000 today (present-value is –50000 because this is a cash outflow) and add £200 (payment is –200 because this is also a cash outflow) to the account at the beginning of each month. Over the next 15 years, the savings account is expected to earn interest monthly (periodic-rate is 0.045/12). =RATE(15*12, –200, –50000, 150000, 1, 0.1/12) returns approximately 0.376962210924744%, which is per month because num-periods was monthly, or approximately a 4.52% annual rate. Therefore, if the savings account is expected to earn at least this rate over the entire period, it would grow to at least £150,000 over the 15 years. |