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.

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" wrote in message
...
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.

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


"Fred Smith" wrote:

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" wrote in message
...
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.

Karen,

A2 in %..Try

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

If this post helps click Yes
---------------
Jacob Skaria


"Karen" wrote:

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


"Fred Smith" wrote:

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" wrote in message
...
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.

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

"Jacob Skaria" wrote:

Karen,

A2 in %..Try

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

If this post helps click Yes
---------------
Jacob Skaria


"Karen" wrote:

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


"Fred Smith" wrote:

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" wrote in message
...
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.

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.


"Karen" wrote:

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

"Jacob Skaria" wrote:

Karen,

A2 in %..Try

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

If this post helps click Yes
---------------
Jacob Skaria


"Karen" wrote:

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


"Fred Smith" wrote:

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" wrote in message
...
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.

No. This formula assumes monthly compounding (that's why the term was
specified in months). For daily compounding, you specify the term in days,
and make the corresponding adjustment to the interest rate, as in:

=fv(a2/365,b6-b5,0,-a1)-a1

Regards,
Fred.

"Karen" wrote in message
...
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.


"Karen" wrote:

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

"Jacob Skaria" wrote:

Karen,

A2 in %..Try

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

If this post helps click Yes
---------------
Jacob Skaria


"Karen" wrote:

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


"Fred Smith" wrote:

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" wrote in message
...
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.