Free Microsoft Excel 2013 Quick Reference

Formula: Calculate Reverse Compound Interest

I am having trouble finding a formula to calculate the initial ammount required to obtain a savings ammount after a period of time when there is compound interest involved.

For example:
If i wanted a total savings after 10 years of £100,000. How much would i need to pay monthly into my savings account if I get 5% annual interest calculated monthly.

Does anyone have a formula for this please. It would be very helpful as I have spent all day trying different variations on the FV function and others to try and work this out.

Many thanks
Michael.


Post your answer or comment

comments powered by Disqus
I am trying to set up my excel to calculate daily compound interest.

The amount is 10,000 at 0.75% per day for 6 months.

I have tried several different things with no success - help please

How to calculate quarterly compounded interest on cumulative deposit with
bank or company in ms excel

Hello.

I am calculating Daily compound interest on a Principal from a % p/a rate. I think I may have the right equation, however, I am not 100% confident with the answers.

The principal amount is $20,776.00, the interest rate is 4% p/a, the number of days is 14.

The equation I am using is = $20,776.00*(1+(4%/365))^14 = $20,807.90 I am pretty sure this is right.

However, where things go iffy is when I need to make deposits into the account. The 1st deposit is $43,505.00, this brings the balance to $64,312.90 and is made at the end of the 14 day period and the next is 28 days later. So my next equation look like this:
$64,312.90*(1+(4%/365))^28 =$64,510.53.

This goes on over another 10 months or so with days between deposits changing.

Am I doing this right? If not, how should I be doing it?

Thank you

I need to know if I deposit a fix amount (i.e. $10,000) at a fixed interest
rate (i.e.5%)
what is the compound interest at the end of the year. Need one formula in
one cell not a schedule by month

I am particularly interested in if NPV, PMT, and PPMT are calculated with
compound interest or simple interest?
If simple interest, then how can I get the results for compound interest?

Thank you.

Hi,

I've just checked all the Excel Compound Interest functions but I canít
seem to find the exact one for calculating implied compound interest.

I have a starting value of an investment and an ending value, together
with the number of years itís been invested. Iím looking for a function
that can tell me the smoothed implied compound growth per yearĖ Sorry if
Iíve missed an obvious one but can anyone help?

Thanks,

Daz

--
dazman
------------------------------------------------------------------------
dazman's Profile: http://www.excelforum.com/member.php...o&userid=25903
View this thread: http://www.excelforum.com/showthread...hreadid=497139

Hi, I have one large file and I need to compute the final value of investing a particular amount based on the returns given by the stock market. I'm trying to figure out a way that I can compute the answer without going into cell after cell with the same kind of formula. The formula that I'm looking for is like the formula for a compound interest. I.e. Final value = P(1+r)^n. However, in my case, r is always changing and hence P is also changing as well. What I'm currently doing is typing the same kind of formula into each cell. For example to find the year-end value I input: 1000(1.05) then the next year, the year-end value is this: (1000)(1.05)(1.10), and the next may be (1000)(1.05)(0.96)...and so on....does anyone know of a simpler way to do this?

Hi,

I've just checked all the Excel Compound Interest functions but I canít seem to find the exact one for calculating implied compound interest.

I have a starting value of an investment and an ending value, together with the number of years itís been invested. Iím looking for a function that can tell me the smoothed implied compound growth per yearĖ Sorry if Iíve missed an obvious one but can anyone help?

Thanks,

Daz

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.

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!

I am trying to calculate the annual interest when I know the compound
interest. I would like a formula I can use in excel

All,

please see attached.

I require the following:

1. under additional in "sheet" if the production in the year ahead exceeds 18,000,000 then production equipment must be added from the "trees" sheet C7
2. This is only to be done if the cell to the left of the active cell = 0 (this is because the cost will only be incurred once)
3. Compound interest must be calculated from 2011 (e.g. if the cost of new production equipment came in 2013 it would be ^2 if in 2014 ^3 etc)

this is what i have so far:

=IF(OFFSET(E13,0,-1)=0,IF(F7>Sheet2!$B$9,Sheet2!$C$7*(1+Sheet2!$B$3)^2,0))

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.

	VB:
	
 
    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 
     
     
    Do 
         
         
        LDM = [atpvbaen.xls].EoMonth(st_dt, 0) 
        If [atpvbaen.xls].Edate(org_st_dt, freq * freq_ctr)

Morning,

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?

Cheers,

Paul

Can anyone please tell me how to write a formula to add 5% interest to money that is deposited into a savings account on a yearly basis? Deposits are made to this account on a yearly basis, and I would like the formula to compound the interest so that it totals all deposits with the interest.

******** language="JavaScript" ************************************************************************>Microsoft Excel - Book1___Running: 12.0 : OS = (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutB9=
ABCD1YearContributionsPercent EarnedCompounded Amount22003$2,645.005.00%???32004$1,823.005.00% 42005$1,952.005.00% 52006$3,569.005.00% 62007$2,403.005.00% 72008$2,267.005.00% 8 9Total$14,659.00 Sheet1
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

Thank you,

Shawn

If you have an annual interest rate - say 5%, you calculate daily compounded
interest by:

=((1+5%/365)^(#days)-1)*principal amount

If your principal amount is $1,000 and the # of days is 60, then the
interest is $8.25 vs $8.22 simple interest

"Myra" wrote:

> Would like to create worksheet with daily compounding but without having to
> set a row per day. Any suggestions?

All,

I'm based in the UK and I'm trying to get to the bottom of a dilemma that
I'm facing - "How do I calculate how much the monthly repayment will be for a
loan that features two interest rates?"

I know the following:

Principal - £50,000
Interest compounded - Daily (probably 360 days)
Interest rate - 5% for 10 years
Reverting to - 7% for 5 years
Total mortgage duration - 15 years

I've already tried the following function
"PMT((1+5%/360)^(360/12)-1,15*12,-50000)", but this only allows for one
interest rate and doesn't account for the rate change, later in the term.

Any pointers???

Many thx.

S

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?
Regards
Graham

Hi Guys,

I am new to excel use and certainly not all that great with formulas...

After searching through all excels finacial tools, I came up empty.
What I am looking for is a formula to calculate the future value of an investment for a period of time, with daily compound interest of say 1% daily.

I am sure it is an easy thing to figure but the few tried like FV need a constant payment and I am only looking for an initial starting value with a future value after the time has expired with interest calculated...

If someone can help, it would be appreciated...

Thanks!
John

All,

I'm based in the UK and I'm trying to get to the bottom of a dilemma that
I'm facing - "How do I calculate how much the monthly repayment will be for a
loan that features two interest rates?"

I know the following:

Principal - £50,000
Interest compounded - Daily (probably 360 days)
Interest rate - 5% for 10 years
Reverting to - 7% for 5 years
Total mortgage duration - 15 years

I've already tried the following function
"PMT((1+5%/360)^(360/12)-1,15*12,-50000)", but this only allows for one
interest rate and doesn't account for the rate change, later in the term.

Any pointers???

Many thx.

S

I posted the following yesterday and was provided some excellent help:

Here is what I'm trying to do with a Excel formula. I need to add simple inflation percentage for a single year to a value, for example, if the value is 1000 and the inflation percent is 10, the value after one year is 1100. But I need to continue adding only the same percent to that value to provide a cumulative total. For example, after the second year the total would be 1100 plus 1200 (1100 + 100), which equals 2300. After the third year the total would be 1100 + 1200 +1300, which equals 3600. The number of years is determined by an input field, as is the inflation rate. I desperately need help in making this type of calculation.

The reply that worked best gave me the following formula:

A1 = initial value
A2 = inflation percent
A3 = num of yrs
A4 = A1*A3+COMBIN(A3+1,2)*A2/100*A1

Now, I'd like to take it a step further and calculate compounding. I added this post to my previous thread, but I'm afraid since it was already answered, it got lost:

Not to get greedy, but I was also wondering if anybody could help me with a formula that would calculate annual compounding on my original example. For example, instead of simply adding the same amount each year that was based on the original amount (100 in my example), I'd like to be able to add the percentage of the current year.

Hopefully, the following will make it clearer. On the 1000 amount and 10% interest I used in my example, here's what I'd like the values to be. After 1 year, the value would be 1100, then the second year would add 10% to that value, then add the total: 1100 + 110 = 1210 + 1100 = 2310. The third year value would be 1210 + 121 = 1331 + 2310 = 3641, etc. Again, any help on this is greatly appreciated.

Can anyone tell me if there is an Excel function to calculate the future
value of an initial principal amount after adding compound interest. E.g a
function that does the following

P x (1+i%)^n

Although the formula is straightforward my client, for reasons I do not
understand, wants a specific Excel function.

Any help much appreciated.

Andrew.

Deriving Compound Interest for Odd Duration such as in Days

What formula should be used to calculate the Maturity Value for an Odd Duration.?

Ex: -

Principal ( P ) - 10,000
Int ( R )- 8%
Compounding Freq ( m )- Quarterly ( 4 )
Duration ( n ) - 390 Days..

I have checked most sites and also reffered some Finance Mgmnt books, but they are focussing more on whole no of years or half-years, but in real life one can take a Fixed Deposit in any Bank for just 50 days or 73 days..

Then how does one calculate the Maturity value in such cases?

Warm Regards

e4excel


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