How to Find Interest Rate in Future Value Annuity in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.  Find Interest Rate in Future Value Annuity

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:

Find Interest Rate in Future Value Annuity

Explanation:
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


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:

Find Interest Rate in Future Value Annuity

Explanation:
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.

Find Interest Rate in Future Value Annuity

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

Al Arafat Siddique
Al Arafat Siddique

Hello! This is Arafat. Here I'm researching Microsoft Excel. I did my graduation from Bangladesh University of Engineering and Technology(BUET). My interest in data science and machine learning allured me to play with data and find solutions to real-life problems. I want to explore this data-driven world and make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo