Free Microsoft Excel 2013 Quick Reference

Calculate Compound Interest Rate Growth


Has anybody ever come up with a way to discover the a compound interest growth rate based on actual numbers... for example;

Jan05 - 75
Jan06 - 150
Jan07 - 290
Jan08 - 440

I have a long list of investments that I am working on for a colleague, and want to show the growth in interest rate. In addition, I have payment schedules from loans for which I want to do the same, although I imagine the formula will be the same.

Any suggestions?



Post your answer or comment

comments powered by Disqus
Hi, Can anyone show me where to find a simple Excel template to calculate
compound interest on personal savings. I've searched the 'Templates' section
but all appear to be based on loans rather than savings -Maybe I'll have to
edit one of those?


I am trying to calculate the interest rate required for a loan of 200,000 with 60 monthly repayments of 6,500 to leave a residual of 60,000.

The rate is approximately 4.88%, calculated by trial and error in a 60 row worksheet, but is there a formula to calculate this figure direct?

Thanks in advance.


is anyonne able to help me, I am trying to work out a formula to calculate compounded interest rates.

I have a table that is 24 columns wide (months).
1 row that will have amounts input in to the columns (these will be different amounts)

I want to have a row along the bottom that calulates the compounded interest at a fixed interest rate on the total amount that is in the first row.

i have mnaged to do this using a table but surely there is a formula for this as the table can become very troublesome if the input amounts change.

Ive added an image (attached) to hopefully make this a bit clearer.

Many thanks in advance if you can help you will be saving me days of work

Can someone help define the formula to calculate the compound average growth
rate (CAGR) starting with an initial amount which grows or shrinks depending
upon the earnings for the year? Is the formula included in the formulae
included in Excel?

That data is in columns:
A: Year
B: Year's average interest rate growth or loss
C: Value of investment, e,g. starting at $1,000 and changing each year by
the rate in column B.
D: The CAGR effective to that year.

Thank you for your assistance.


Can anyone tell me if there is a way in Excel to calculate an interest rate (using the Financial "Rate" function) when the compounding frequency is different from the payment frequency? From what I gather, Excel always assumes that the compound period and the payment period are the same (i.e., monthly compounding for monthly billing, quarterly compounding for quarterly billing, etc.). I would like to solve for an interest rate that uses quarterly payments but monthly compounding. Does anyone know if this possible?

Question in the title really, is there an excel function to calculate a daily interest rate from an APR? The APR is compounded daily, maybe there is another way?


I am trying to calculate an average interest rate for the life of a 30 year bond issue, where over the life of the loan the principal paid was $1,000,000 and the interest paid was $800,000. I assume the interest is compounded continuously. Can you help?

Objective of Function: To find compounded interest for a given period.

User Input:
Start_Date =7/17/2007 (Date of Placing a Deposit)
From_Date =7/10/2007(Date from which user wants interest)
To_Date = 10/31/2007(Date till which user wants interest)
Principal_Amount = 50,000 (Amount invested/deposited)
Interest_Rate = 10.25%
Compounding_Frequency = 3 (in months if qtrly then 3, yearly then 12, monthly then 1 & so on)
(Hence in given case first compounding period ends on 10/17/2007)

So the output of the function in above case should be 440.72 (ie when
start date is 7/17/2007), The function which I have created works fine
for above case but when the start date is changed to 4/17/2007
then it fails to give the correct answer, may be my logic is wrong so if anyone figure out correct way then please help me.

Given below is the function which I have created.

    Date, Principal_Amount As Double, Interest_Rate As Double, 
    Compounding_Frequency As Integer) 
     'storing user input in temp variables for ease of reference
    st_dt = Start_Date 
    fm_dt = From_Date 
    to_dt = To_Date 
    pr_amt = Principal_Amount 
    int_rate = Interest_Rate 
    freq = Compounding_Frequency 
    freq_ctr = 1 'frequency counter for calculating # of completed compounding periods
    Dim LDM As Date 'for storing Last date of month
    Dim tmp_dt As Date 'for storing temp values for date
    Dim tmp_date_2 As Date 
    org_st_dt = st_dt 
        LDM = [atpvbaen.xls].EoMonth(st_dt, 0) 
        If [atpvbaen.xls].Edate(org_st_dt, freq * freq_ctr)

I am trying to calculate the effective annual interest rate earned on an investment and find the results are close but not really accurate. I suspect because I have not included the frequency of interest in my existing formula and I may need the help of a maths expert. The new formula I have worked out is;

r = n * nt root (A/P-1)

r = the effective interest rate
n = the number of times interest is added per year
t = the total number of years
A = the current value
P = the original value

The 2 problems I face are;
1. Confirming this formula would provide the correct answer (need maths expert here) &
2. How would "nt root" (as in sqr root, but using the product of the years and frequency) be used in Excel?

Hope this makes sense. It can be difficult to explain complex problems like this in words.
Thanks in advance for any replies.
cheers, Norman

Hi there. I'm trying to come up with a way of calculating Money earned over time by compounding interest, but with a twist. After reaching a set amount of money, all money above and beyond does not gain interest. example:
Principal: $120 (user input value)
Duration: 9 (user input value in days, compounding daily)
%Intertest: 4% (user selected value, either 2% or 4%)
Max interest you can earn: $6 (fixed)
Max interest generating money: $150 (variable dependant on %interest, = $150 or $300)
Response/Answer is final value. I don't need the daily results like the example.
Result would be: $170.84
$124.80 (4.80 interest)
$129.79 (4.99)
$134.98 (5.19)
$140.38 (5.40)
$146.00 (5.62)
$151.84 (5.84)
$157.84 (6.00 reached the max interest level)
$163.84 (6.00)
$170.84 (6.00)

my equations I have so far only do one (below 150 total) or the other (above) but not both.
and its just a regular formula: =IF(P

I am trying to create a calculator based on a worksheet I have. I do not know how to write a formula for simple interest calculations and for compound interest calculations. Any help would be really appreciated.

I am trying to determine an interest rate using excel
Example: If I start with $45 and in 5 years it grows to $100, what is the
rate it is compounding at?

Some years ago I came across a formulae to calculate Daily Interest on a Building Society Savings account in the UK.
I have used this since but find my calculations never work out the same as my BS, although to my advantage!
It is =B3*B4/360*DAYS360(B5,B6,TRUE)
B4=Interest Rate
B5=Starting Date
B6=Finishing Date
For some reason the formulae uses 360/year and not 365/year.
Using both still gives wrong answer.
If anyone has an answer or a better formulae I would appreciate it.
Many thanks.
I use Excel 2000

Hi Forum,

This is my first posting, so please be forgiving.

I need to know the most appropriate Excel function for solving a compound interest rate calculation for an unknown interest rate.

For example, $193 = $100*(1+I)^9yrs, solve for I. By trial and error, I know the solution is about 7.6%pa. Which of the many Excel financial or mathematical functions will give this answer.


It's been a long time since I've had to do this and I've simply forgotten how. Can someone please help me with the formulas in the attached excel file?

I've found 2 reliable online calculators and I've hard-coded the values for compound interest in the attached excel file. I just need to figure out how to calculate these myself for a project that I'm working on.

Full details are in the attachment.

Thanks in advance for the help!

For a loan based on constant payments and constant interest: We have functions such as PMT to calculate the payment, PPMT to calculate principal. Do you know what funtion to calculate the INTEREST RATE? For example: If I have present value, future value, monthly payment, and number of of payment. What function I will use to calculate the interest rate?

Thank you very much

I know I can use A = P(1 + r/q)n to get the compound interest, but I am unsure of how to get the compound interest on a savings that is added to monthly.

Let's say I start off like this:

January 08 PV= £1000 + 5% interest, Feb 08 PV=£1000+£100 + 5% interest

and so on...I know there is a simple answer, but it is just escaping me at the moment.

Thanks : )

hey all..
is there a way to calculate an interest rate based on the following info?

A. total interest paid over life of loan
B. total # of months of loan period
C. beginning principal balance
D. Ending balance to be 0, after # of months in B.

Here is my info..
A. 279,017.80
B. 360
C. 200,000
D. 0

I want the answer to come out to 7%..
i'm stumped.

I have a column of years and a column of numbers representing annual
amounts placed in a savings account for the year. I would like to calculate
the balance of interest earned added to the balance of the account and then
calculate the interest earned on the accumulating amounts each year. The
results would be the account balance displayed in an adjoining column. So
far, I have not found a worksheet function for that. Could someone point me
in the right direction? Perhaps there should be several columns of data?

Hello I am trying to make a pivot table with a custom "Grand total"

For example I need to calculated compound interest in the grand total
field. Is this possible?

Suppose 10% return every month (Jan-Dec)compounded monthly.

jan feb mar ... TOTAL
2001 10% 10% 10%... 313.8%
2002 10% 10% 10%... 313.8%
2003 10% 10% 10%... 313.8&

TOTAL = exp(sum(log(1+n))


I am trying to determine which formula in excel will allow me to calculate the interest rate for an annuity where I know the present value of the payments, the amount of each annual payment and the number of periods.

Any hints would be helpful.

I have a column of years and a column of numbers representing annual
amounts placed in a savings account for the year. I would like to calculate
the balance of interest earned added to the balance of the account and then
calculate the interest earned on the accumulating amounts each year. The
results would be the account balance displayed in an adjoining column. So
far, I have not found a worksheet function for that. Could someone point me
in the right direction? Perhaps there should be several columns of data?

I have $56,000 in a annuity. My my monthly income when I annuitize will be $380.00 per month for 180 months. At that time my account balance will be $0. What is my interest rate. (formula please)

I am preparing an Excel template for my users to help them calculate
compound interest rate over several years based on the interest rate of
a bank.

The first sheet contains 3 columns, StartDate, EndDate, Rate.
The second sheet which will be hidden to the users is for all the
various calculations. There will always be a row for the amount at year

I would like to produce a summary in the third sheet with the interest
and the total amount at end of each year and at the end of the period.

I know I can use pivot table but since it is an Excel template, I
cannot pre-define the criteria. Is there a way to achieve that without
using VBA?

RayYeung's Profile:
View this thread:

No luck finding an answer? You could always try Google.