Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations wOOdy-Soft on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Using function Rate(nper,pmt,pv,fv,type,guess)
- Thread starter domt
- Start date
Rate Function
Returns aDouble specifying the interest rate per period for an annuity.
Syntax
Rate(nper, pmt, pv[, fv[, type[, guess]]])
The Rate function has thesenamed arguments:
Part Description
nper Required. Double specifying total number of payment periods in the annuity. For example, if you make monthly payments on a four-year car loan, your loan has a total of 4 * 12 (or 48) payment periods.
pmt Required. Double specifying payment to be made each period. Payments usually contain principal and interest that doesn't change over the life of the annuity.
pv Required. Double specifying present value, or value today, of a series of future payments or receipts. For example, when you borrow money to buy a car, the loan amount is the present value to the lender of the monthly car payments you will make.
fv Optional.Variant specifying future value or cash balance you want after you make the final payment. For example, the future value of a loan is $0 because that's its value after the final payment. However, if you want to save $50,000 over 18 years for your child's education, then $50,000 is the future value. If omitted, 0 is assumed.
type Optional. Variant specifying a number indicating when payments are due. Use 0 if payments are due at the end of the payment period, or use 1 if payments are due at the beginning of the period. If omitted, 0 is assumed.
guess Optional. Variant specifying value you estimate will be returned by Rate. If omitted, guess is 0.1 (10 percent).
Remarks
An annuity is a series of fixed cash payments made over a period of time. An annuity can be a loan (such as a home mortgage) or an investment (such as a monthly savings plan).
For allarguments, cash paid out (such as deposits to savings) is represented by negative numbers; cash received (such as dividend checks) is represented by positive numbers.
Rate is calculated by iteration. Starting with the value of guess, Rate cycles through the calculation until the result is accurate to within 0.00001 percent. If Rate can't find a result after 20 tries, it fails. If your guess is 10 percent and Rate fails, try a different value for guess.
Returns aDouble specifying the interest rate per period for an annuity.
Syntax
Rate(nper, pmt, pv[, fv[, type[, guess]]])
The Rate function has thesenamed arguments:
Part Description
nper Required. Double specifying total number of payment periods in the annuity. For example, if you make monthly payments on a four-year car loan, your loan has a total of 4 * 12 (or 48) payment periods.
pmt Required. Double specifying payment to be made each period. Payments usually contain principal and interest that doesn't change over the life of the annuity.
pv Required. Double specifying present value, or value today, of a series of future payments or receipts. For example, when you borrow money to buy a car, the loan amount is the present value to the lender of the monthly car payments you will make.
fv Optional.Variant specifying future value or cash balance you want after you make the final payment. For example, the future value of a loan is $0 because that's its value after the final payment. However, if you want to save $50,000 over 18 years for your child's education, then $50,000 is the future value. If omitted, 0 is assumed.
type Optional. Variant specifying a number indicating when payments are due. Use 0 if payments are due at the end of the payment period, or use 1 if payments are due at the beginning of the period. If omitted, 0 is assumed.
guess Optional. Variant specifying value you estimate will be returned by Rate. If omitted, guess is 0.1 (10 percent).
Remarks
An annuity is a series of fixed cash payments made over a period of time. An annuity can be a loan (such as a home mortgage) or an investment (such as a monthly savings plan).
For allarguments, cash paid out (such as deposits to savings) is represented by negative numbers; cash received (such as dividend checks) is represented by positive numbers.
Rate is calculated by iteration. Starting with the value of guess, Rate cycles through the calculation until the result is accurate to within 0.00001 percent. If Rate can't find a result after 20 tries, it fails. If your guess is 10 percent and Rate fails, try a different value for guess.
- Thread starter
- #3
Er ... the documentation - as provided to you by vladk - is clear: the Rate function returns the interest rate per period, not the APR (which seems to be what you are looking for)
To calculate the APR you need to multiply the rate per period (i.e. the value returned by the Rate function) by the number of payment periods in the year, whatever that might be.
To calculate the APR you need to multiply the rate per period (i.e. the value returned by the Rate function) by the number of payment periods in the year, whatever that might be.
- Thread starter
- #5
Thanks again to both of you.
However the number of pay periods per year is important and the rate function doesn’t account for it.
On a $6000 loan at 9.75% for 5 years with monthly payments (60 payments total), the payment per month would be $126.75 and putting npr=60, pmt= 126,75, and ,pv=6000 into the rate function gives the correct rate of 9.75.
But, if the same amount at the same rate were to be paid off in 60 payments extending over 30 years
(2 payments per year), the periodic payment would be $310.35, and the function would not work.
Perhaps we have to accept that the function’s use is limited to 12 monthly payments.
domt
However the number of pay periods per year is important and the rate function doesn’t account for it.
On a $6000 loan at 9.75% for 5 years with monthly payments (60 payments total), the payment per month would be $126.75 and putting npr=60, pmt= 126,75, and ,pv=6000 into the rate function gives the correct rate of 9.75.
But, if the same amount at the same rate were to be paid off in 60 payments extending over 30 years
(2 payments per year), the periodic payment would be $310.35, and the function would not work.
Perhaps we have to accept that the function’s use is limited to 12 monthly payments.
domt
>Perhaps we have to accept that the function’s use is limited to 12 monthly payments.
Or perhaps we have to accept a lack of understanding of what the function does. Here try this:
Firstly:
Rate(60, 126.75, -6000)
results in a rate of 0.8126% per period, which you need to multiply by the number of periods in the year (in your starting eample this is 12) to get the APR
i.e
NoPeriodsInYear=12
APR=Rate(60, 126.75, -6000) * NoPeriodsInYear
=9.75%
Are you saying that following the same rule for your second example
NoPeriodsInYear=2
APR=Rate(60, 310.35, -6000) * NoPeriodsInYear
doesn't provide the correct and expected result of 9.75% (give or take some floating point error and ignoring the fact that the payments should probably be closer $310.39)?
Or perhaps we have to accept a lack of understanding of what the function does. Here try this:
Firstly:
Rate(60, 126.75, -6000)
results in a rate of 0.8126% per period, which you need to multiply by the number of periods in the year (in your starting eample this is 12) to get the APR
i.e
NoPeriodsInYear=12
APR=Rate(60, 126.75, -6000) * NoPeriodsInYear
=9.75%
Are you saying that following the same rule for your second example
NoPeriodsInYear=2
APR=Rate(60, 310.35, -6000) * NoPeriodsInYear
doesn't provide the correct and expected result of 9.75% (give or take some floating point error and ignoring the fact that the payments should probably be closer $310.39)?
- Thread starter
- #7
- Status
Similar threads
- Locked
- Question
- Locked
- Question