Al Arafat Siddique
This article illustrates how to find the interest rate in future value annuity with 2 well-explained examples. We can easily find it out by using Excel’s built-in RATE function. To make important financial decisions, for example, to take a loan to buy a new car, it is important to know the rate of interest that you’ll have to pay to the provider. This article will certainly help you to learn and implement Excel’s built-in functionality to find out how to find Interest Rate in Future Value Annuity in Excel.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
How to Find Interest Rate in Future Value Annuity in Excel: 2 Examples
Problem: We want to find out the interest rate per period of an annuity of a fund of $50,000 to be raised in 20 years. We’ll deposit a fixed amount of $100 at the end of each month throughout the 20 years’ time. Here, we have two cases- one with no existing balance and the other with an existing balance of 1,000 dollars. 
Problem Analysis: To solve the problem we need to use the RATE function which is Excel’s built-in financial function. The RATE function calculates the interest rate per period of an annuity to pay off a loan or the rate of return needed to reach an amount of investment.
The syntax of the RATE function:
=RATE(nper, pmt, pv, [fv], [type], [guess])
Let’s analyze the data we’ve got in the above problem and compare it with the arguments of the RATE function.
| Argument | Required/Optional | Value from the Problem |
|---|---|---|
| nper-number of payment periods in an annuity | Required | 240 = 20 years * 12 months |
| pmt-the amount of payment made each period and cannot change over the life of the annuity | Required | $100 |
| pv- the present value | Required | Case 1: $0 Case 2: $1,000 |
| fv– the future value or a cash balance expected after the last payment | Optional | $50,000
|
| type-payment type | Optional | 0 or omit– payments are due at the end of the period 1-payments are due at the beginning of the period |
| guess– guess on the rate of interest | Optional | Default is 10% |
Now we’re going to illustrate two examples to find out the annuity interest rate for the future value for the cases described above.
1. Find an Annuity Interest Rate to Achieve Desired Future Value
This example illustrates how to find out the annuity interest rate to achieve the desired future with no existing balance. We configured cell C12 with the RATE function and hit Enter:
The formula in cell C12 is =RATE(C5, C6, C7,-C8, C9, C10)
C5- nper : 240 for 20 years (no of payment per year is 12).
C6- pmt : 100 dollars cash paid per month.
C7-pv : 0 dollar.
C8 -fv : 50,000 dollars.
C9 -type : 0 as payments are due at the end of the period.
C10-guess :10% as default.
Results:
The formula returned 0.56% as the interest rate per month. To get the annual interest rate (6.66%), we multiplied the output by 12. 
Read more: How to Calculate Future Value in Excel with Different Payments
Similar Readings
- How to Calculate Interest Rate from EMI in Excel (with Easy Steps)
- Loan Amortization Schedule with Variable Interest Rate in Excel
- Nominal vs Effective Interest Rate in Excel (2 Practical Examples)
2. Calculate Annuity Interest Rate to Earn Expected Future Value with the Existing Balance
In this example, we’ll find out the interest rate per period to earn an expected future value. But this time it already has some existing balance. We configured cell C12 with the RATE function and hit Enter:
The formula in cell C12 is =RATE(C5, C6, C7,-C8, C9, C10)
C5- nper : 240 for 20 years (no of payment per year is 12).
C6- pmt : 100 dollars cash paid per month.
C7-pv : 1,000 dollars.
C8 -fv : 50,000 dollars.
C9 -type : 0 as payments are due at the end of the period.
C10-guess :10% as default.
Results:
The formula returned 0.56% as the interest rate per month. To get the annual interest rate (6.08%), we multiplied the output by 12. The interest rate will decrease more with the increasing amount of existing balance i.e., the present value.
Read More: How to Calculate Effective Interest Rate in Excel with Formula
Notes
- Annuity: An annuity is defined as a fixed amount of money that is paid at an equal interval of time.
- By default, the result shows as a round number (1%), with no decimal places. We can add decimal places following the steps below:
-
- Go to the Home Tab.
- Navigate to the Number panel.
- Choose the Increase Decimal
Conclusion
Now, we know how to find interest rate in future value annuity in Excel. Hopefully, it would encourage you to use this functionality more confidently. Any questions or suggestions don’t forget to put them in the comment box below
Related Articles
- Calculate Annuity Factor in Excel (2 Ways)
- How to Do Ordinary Annuity in Excel (2 Methods)
- Apply Present Value of Annuity Formula in Excel
- How to Apply Future Value of an Annuity Formula in Excel
- How to Calculate Monthly Interest Rate in Excel (3 Simple Methods)
- Calculate Future Investment Value with Inflation, Tax and Interest Rates
- How to Calculate Interest Rate on a Loan in Excel (2 Criteria)
