If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Financial formula
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. |
#2
|
|||
|
|||
Financial formula
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. |
#3
|
|||
|
|||
Financial formula
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. |
#4
|
|||
|
|||
Financial formula
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. |
#5
|
|||
|
|||
Financial formula
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. |
#6
|
|||
|
|||
Financial formula
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. |
#7
|
|||
|
|||
Financial formula
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. |
Thread Tools | |
Display Modes | |
|
|