How to use the RATE function in Excel ?

In summary, the conversation discusses a task involving a quarterly deposit of €700 over 4.5 years with an accumulated value of €13,600. The formula S = [((1+i)n - 1) / i] ∙ R is mentioned to find the annual interest rate, with a solution of -1% for i. However, there are errors in the calculations and adjustments need to be made for consistency. The conversation concludes with a suggestion to use specific data and a financial calculator to understand and solve the problem accurately.
  • #1
indigo2
2
0
Hello :)

I was given this task:

A quarterly deposit is €700 in 4.5 years, and the accumulated value is €13,600. What is the value of annual interest rate?

And I would apply this formula:

S = [((1+i)n - 1) / i] ∙ R

€13,600 = [((1+i)18 - 1) / i] ∙ €700

To find out the annual interest rate I have to use the RATE function in excel but my solution is -1% for i.

View attachment 8198

What do I do wrong?

THANK YOU FOR ANY ADVICE :)
 

Attachments

  • dsfwa.JPG
    dsfwa.JPG
    10.4 KB · Views: 79
Mathematics news on Phys.org
  • #2
indigo said:
Hello :)

I was given this task:

A quarterly deposit is €700 in 4.5 years, and the accumulated value is €13,600. What is the value of annual interest rate?

And I would apply this formula:

S = [((1+i)n - 1) / i] ∙ R

€13,600 = [((1+i)18 - 1) / i] ∙ €700

To find out the annual interest rate I have to use the RATE function in excel but my solution is -1% for i.
What do I do wrong?

THANK YOU FOR ANY ADVICE :)

You've some problems, here.

For starters, 4 * 4,5 * 700 = 12600 < 13600. That makes no sense. You SHOULD get a negative interest rate.

Next, "Number of Periods" probably should read "Number of Years". Very confusing.

Finally, you adjusted your years to periods by a factor of 4, why didn't you also adjust your interest rate from a quarterly rate to an annual rate? One must be consistent.

Generally, you can figure these things out using very small and specific data. Try one (1) period. Try one (1) year. Get a feel for it and give it another go.
 
  • #3
Not familiar with Excel; but using a $7.99 financial calculator:
PMT = -730
FV = 13600
N = 18
i = ?
output:
i = .0089

That's the equivalent of 10.68% APR cpd. quarterly.
Right, TK?
 
  • #4
Wilmer said:
Not familiar with Excel; but using a $7.99 financial calculator:
PMT = -730
FV = 13600
N = 18
i = ?
output:
i = .0089

That's the equivalent of 10.68% APR cpd. quarterly.
Right, TK?

Wait, we're accumulating? Missed that. Wilmer knows best.
 

FAQ: How to use the RATE function in Excel ?

1. How do I use the RATE function in Excel?

The RATE function in Excel is used to calculate the interest rate per period of a loan or investment. To use the function, you need to enter the following parameters in the formula: RATE (nper, pmt, pv, [fv], [type], [guess]). Nper represents the total number of payment periods, pmt is the payment amount per period, pv is the present value of the loan or investment, fv is the future value (optional), type is the timing of the payments (optional), and guess is your estimated interest rate (optional).

2. What values do I need to input in the RATE function?

As mentioned in the previous answer, you need to input the following values in the RATE function: nper, pmt, pv, [fv], [type], and [guess]. Nper, pmt, and pv are required parameters, while the rest are optional. It is important to input the values in the correct order for the function to work properly.

3. Can the RATE function be used for both loans and investments?

Yes, the RATE function can be used for both loans and investments. However, the parameters may vary depending on the type of loan or investment. For example, if you are calculating the interest rate for a loan, you would need to enter the total number of payment periods, the payment amount per period, and the present value of the loan. But if you are calculating the interest rate for an investment, you would need to enter the total number of compounding periods, the periodic interest rate, and the present value of the investment.

4. What is the purpose of the [guess] parameter in the RATE function?

The [guess] parameter in the RATE function is used to provide an estimated interest rate. This is helpful when the function is unable to calculate the interest rate accurately based on the other parameters. If you do not have an estimated interest rate, you can leave this parameter blank, and Excel will use a default value of 0.1 (10%).

5. How can I use the RATE function to compare different loan or investment options?

The RATE function can be used to compare different loan or investment options by calculating the interest rate for each option and then comparing the results. You can also use the function to determine the most favorable option by changing the values of the parameters and seeing how the interest rate changes. This can help you make informed decisions about which loan or investment option is best for you.

Similar threads

Replies
2
Views
1K
Replies
31
Views
2K
Replies
2
Views
2K
Replies
1
Views
1K
Replies
2
Views
2K
Replies
1
Views
700
Replies
1
Views
2K
Back
Top