Financial formula

[Karen]

Need some help on a financial formula :

Let's say I have $1,000 in an account. The interest rate is 5.5%pa. If start
date is 10 Mar 2003 and end date is 30 June 2009, interest is added on to the
principal. How much interest will be generated at the end date?

What formula can I use?

Thanks.

 

[Fred Smith]

How often is interest credited? Assuming every month, you want the FV
function:
=fv(5.5%/12,75,0,-1000)
=1409.12

This is the amount you will have in the bank at the end of the 75 months. Do
determine, the interest earned, subtract the original principal.

This doesn't account for the 20 days from 10 Jun 09 to 30 Jun 09. If you are
actually going to receive interest for this period, use the following:
=fv(5.5%/12,75,0,-1000)*(1+20/365*5.5%)-1000

Regards,
Fred.

 

[Karen]

Hi Fred,

Thanks for the formula, it worked very well. However, I tried replacing the
numbers with cell references and it does not tally.

=fv(5.5%/12,75,0,-1000)*(1+20/365*5.5%)-1000 returns $413.37.

I changed your formula to :
=FV(A2/12,A4,0,-A1)*(1+A5/365*A2%)-A1 and it returns $409.16

Start date in cell B5, end date in cell B6)

A1 : Principal amount = 1000
A2 : Interest = 5.5
A3 : Loan period (I used formula =DATEDIF(B5,B6,"y") & " yrs, " &
DATEDIF(B5,B6,"ym") & " mths, " & DATEDIF(B5,B6,"md") & " days" to work out
the yrs/mth/date)
A4 : Loan period in mths (I used formula
=(YEAR(B6)*12+MONTH(B6))-(YEAR(B5)*12+MONTH(B5)) to work out the mths)
A5 : Balance days (I used =DATEDIF(B5,B6,"md") to work out balance days)

Cell A3 is just to check that years/mths/days are correct.

Where did I go wrong?

Regards
Karen

 

[Jacob Skaria]

Karen,

A2 in %..Try

=FV(A2%/12,A4,0,-A1)*(1+A5/365*A2%)-A1

If this post helps click Yes

 

[Karen]

Hi Jacob

Thanks to your tip, I found out what's the problem. I removed the % sign as
A2 is already in percentage.

Regards
Karen

 

[Karen]

Now I need to calculate daily compound interest. Can I use this formula?
=fv(5.5%/12,75,0,-1000)*(1+20/365*5.5%)-1000 or is there another formula for
daily compound interest?

Thanks.