Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 137
Default Formula to Calculate Compounded Percentage Per Year

Problem
Can anyone provide me with a formula to calculate 6% per year on an amount
of currency (14,000), for a period determine between two dates inclusive.

My Thoughts
Determine the number of days using the formula:
Start date 4/7/2005 End date 4/7/2007
=DATEDIF(A2,B2+1,"d") = 798 days

Then work out the 6% annual amount for every 366 days and remaining 66 days.
Not sure how to do this though...and I think I am overcomplicating the
calculation

Thanks in advance







  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,059
Default Formula to Calculate Compounded Percentage Per Year

On Apr 21, 12:44 pm, Dermot wrote:
Problem
Can anyone provide me with a formula to calculate 6% per year on
an amount of currency (14,000), for a period determine between two
dates inclusive.


I think you mean: you want to calculate the "future value" after
applying the compounded annual rate (6%) on a principal sum (14000)
invested at the earlier date. Right?


My Thoughts
Determine the number of days using the formula:
Start date 4/7/2005 End date 4/7/2007
=DATEDIF(A2,B2+1,"d") = 798 days


If we can assume that 6% is the compounded annual rate (not the
nominal interest rate), then:

=rate(365, 0, -1, 1+6%)

provides the compounded daily rate (suppose that formula is in C2),
and:

=fv(C2, B2 - A2, 0, -14000)

is the value on the date B2 of the amount invested on A2.

Note that this is valid even if the compounding frequency is not daily
because I am assuming that 6% is the effective annual rate based on
whatever the true compounding frequency is.

Caveat: I am also assuming that either that effective annual rate was
determined in a 365-day year, or it does not matter. It matters only
if the actually compounding frequency is daily.

HTH.


----- original posting -----

On Apr 21, 12:44*pm, Dermot wrote:
Problem
Can anyone provide me with a formula to calculate 6% per year on an amount
of currency (14,000), for a period determine between two dates inclusive.

My Thoughts
Determine the number of days using the formula:
Start date 4/7/2005 *End date 4/7/2007
=DATEDIF(A2,B2+1,"d") * = 798 days

Then work out the 6% annual amount for every 366 days and remaining 66 days.
Not sure how to do this though...and I think I am overcomplicating the
calculation

Thanks in advance


  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 461
Default Formula to Calculate Compounded Percentage Per Year

Im not sure how you get 798 from subtracting 4/7/2007 and 4/7/2005. But you
can create a formula using that number of days that looks like this

=(14000*(1+.06)^(798/366))

And you can put the values in to cells... like change 14000 to A1 and 798 to
a cell where you get the difference from.

"Dermot" wrote:

Problem
Can anyone provide me with a formula to calculate 6% per year on an amount
of currency (14,000), for a period determine between two dates inclusive.

My Thoughts
Determine the number of days using the formula:
Start date 4/7/2005 End date 4/7/2007
=DATEDIF(A2,B2+1,"d") = 798 days

Then work out the 6% annual amount for every 366 days and remaining 66 days.
Not sure how to do this though...and I think I am overcomplicating the
calculation

Thanks in advance







  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,059
Default Formula to Calculate Compounded Percentage Per Year

PS.....

On Apr 21, 1:11*pm, I wrote:
=rate(365, 0, -1, 1+6%)


For the nitpickers, I might have added that alternatively, you can get
the same result with the following formula:

=(1 + 6%) ^ (1/365)

One advantage is: sometimes, Excel's RATE implementation returns an
error because it fails to compute the very small percentage without
our providing a "guess". (I would hope that happens only when the
"pmt" argument is zero. But I don't know.)


=fv(C2, B2 - A2, 0, -14000)


Likewise, this can be replace with the following equivalent formula:

=14000 * (1 + C2)^(B2-A2)

Ergo, the two can be reduced to the following:

=14000 * (1 + 6%)^((B2-A1) / 365)

Of course, the first original two formulas can be reduced to the
following:

=fv(rate(365, 0, -1, 1+6%), B2 - A2, 0, -14000)


Finally....


Note that this is valid even if the compounding frequency is not daily
because I am assuming that 6% is the effective annual rate based on
whatever the true compounding frequency is.


If that assumption is false -- if 6% is the annual "interest" rate,
not the APY (aka APR) -- then you cannot compute the "future value"
without knowing the compounding frequency.
  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 137
Default Formula to Calculate Compounded Percentage Per Year

Thanks for the reply
To get 798 days ...........
Start date A2 = 4/7/2005 End date B2 = 9/9/2007
I entered the dates above in the formula below:
=DATEDIF(A2,B2+1,"d") = 798 days = number of days between the dates inclusive.



"akphidelt" wrote:

Im not sure how you get 798 from subtracting 4/7/2007 and 4/7/2005. But you
can create a formula using that number of days that looks like this

=(14000*(1+.06)^(798/366))

And you can put the values in to cells... like change 14000 to A1 and 798 to
a cell where you get the difference from.

"Dermot" wrote:

Problem
Can anyone provide me with a formula to calculate 6% per year on an amount
of currency (14,000), for a period determine between two dates inclusive.

My Thoughts
Determine the number of days using the formula:
Start date 4/7/2005 End date 4/7/2007
=DATEDIF(A2,B2+1,"d") = 798 days

Then work out the 6% annual amount for every 366 days and remaining 66 days.
Not sure how to do this though...and I think I am overcomplicating the
calculation

Thanks in advance









  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 461
Default Formula to Calculate Compounded Percentage Per Year

Oh, ok, that makes more sense. In your original message you had 4/7/2007 as
your end date which threw me off.

"Dermot" wrote:

Thanks for the reply
To get 798 days ...........
Start date A2 = 4/7/2005 End date B2 = 9/9/2007
I entered the dates above in the formula below:
=DATEDIF(A2,B2+1,"d") = 798 days = number of days between the dates inclusive.



"akphidelt" wrote:

Im not sure how you get 798 from subtracting 4/7/2007 and 4/7/2005. But you
can create a formula using that number of days that looks like this

=(14000*(1+.06)^(798/366))

And you can put the values in to cells... like change 14000 to A1 and 798 to
a cell where you get the difference from.

"Dermot" wrote:

Problem
Can anyone provide me with a formula to calculate 6% per year on an amount
of currency (14,000), for a period determine between two dates inclusive.

My Thoughts
Determine the number of days using the formula:
Start date 4/7/2005 End date 4/7/2007
=DATEDIF(A2,B2+1,"d") = 798 days

Then work out the 6% annual amount for every 366 days and remaining 66 days.
Not sure how to do this though...and I think I am overcomplicating the
calculation

Thanks in advance







  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 137
Default Formula to Calculate Compounded Percentage Per Year

oh, so it was...sorry about that!

"akphidelt" wrote:

Oh, ok, that makes more sense. In your original message you had 4/7/2007 as
your end date which threw me off.

"Dermot" wrote:

Thanks for the reply
To get 798 days ...........
Start date A2 = 4/7/2005 End date B2 = 9/9/2007
I entered the dates above in the formula below:
=DATEDIF(A2,B2+1,"d") = 798 days = number of days between the dates inclusive.



"akphidelt" wrote:

Im not sure how you get 798 from subtracting 4/7/2007 and 4/7/2005. But you
can create a formula using that number of days that looks like this

=(14000*(1+.06)^(798/366))

And you can put the values in to cells... like change 14000 to A1 and 798 to
a cell where you get the difference from.

"Dermot" wrote:

Problem
Can anyone provide me with a formula to calculate 6% per year on an amount
of currency (14,000), for a period determine between two dates inclusive.

My Thoughts
Determine the number of days using the formula:
Start date 4/7/2005 End date 4/7/2007
=DATEDIF(A2,B2+1,"d") = 798 days

Then work out the 6% annual amount for every 366 days and remaining 66 days.
Not sure how to do this though...and I think I am overcomplicating the
calculation

Thanks in advance







  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 137
Default Formula to Calculate Compounded Percentage Per Year

Thanks for the reply Joeu2004

Note:
The calculation was explained to me as 6% per annum.......I assumed from
that it was compounded each year....as you have explained.......I will need
to double check I have the correct understanding of the required calculation.
The seed of doubt!

Question 1
Can you explain "6% Nominal Interest for me....to ensure I understand the
term.
Would that be 6% of First Year + 6% of Second Year = 6% of remaining year
fraction?.....not compounding?



"joeu2004" wrote:

PS.....

On Apr 21, 1:11 pm, I wrote:
=rate(365, 0, -1, 1+6%)


For the nitpickers, I might have added that alternatively, you can get
the same result with the following formula:

=(1 + 6%) ^ (1/365)

One advantage is: sometimes, Excel's RATE implementation returns an
error because it fails to compute the very small percentage without
our providing a "guess". (I would hope that happens only when the
"pmt" argument is zero. But I don't know.)


=fv(C2, B2 - A2, 0, -14000)


Likewise, this can be replace with the following equivalent formula:

=14000 * (1 + C2)^(B2-A2)

Ergo, the two can be reduced to the following:

=14000 * (1 + 6%)^((B2-A1) / 365)

Of course, the first original two formulas can be reduced to the
following:

=fv(rate(365, 0, -1, 1+6%), B2 - A2, 0, -14000)


Finally....


Note that this is valid even if the compounding frequency is not daily
because I am assuming that 6% is the effective annual rate based on
whatever the true compounding frequency is.


If that assumption is false -- if 6% is the annual "interest" rate,
not the APY (aka APR) -- then you cannot compute the "future value"
without knowing the compounding frequency.

  #9   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,059
Default Formula to Calculate Compounded Percentage Per Year

On Apr 21, 2:55*pm, Dermot wrote:

The calculation was explained to me as 6% per annum.......I assumed from
that it was compounded each year....as you have explained.......I will need
to double check I have the correct understanding of the required calculation.
The seed of doubt!


A pretty big seed, IMHO. To some degree, it depends on the type of
financial institution. A mutual fund or other securities investment
is likely to mean APY -- the compounded annual rate. But a bank is
likely to mean the (nominal) "interest rate".

Are we talking about a US financial institution or otherwise?

A US financial institution should know to use the words "interest
rate" or "APY" (aka APR; annual percentage yield/rate). In US law,
"interest rate" specifically means the non-compounded rate (aka
nominal rate), whereas "APY" specifically means the compounded rate.


Can you explain "6% Nominal Interest for me....to ensure I understand the
term. Would that be 6% of First Year + 6% of Second Year = 6% of remaining
year fraction?.....not compounding?


It has nothing to do with year-to-year compounding or not. In US law,
interest must compound at least annually. At issue is how intra-
annual rates are computed (e.g. daily, monthly, quarterly).

Following US law, given a nominal annual rate "r" and a compounding
frequency "t", the rate per compounding period is r/t. Here, I am
using t=365 for daily [1], 52 for weekly, 12 for monthly, and 4 for
quarterly.

The APY (or APR, a deprecated term which is still used frequently) is
the annual effect (aka "effective rate") of that period compounding.
That is, if the periodic rate is r/t, the APY is (1 + r/t)^t -1, where
"^" means "to the power of" (that is, multplied by itself t times).

The Excel Analysis ToolPak does have the functions NOMINAL and
EFFECT. You can read their Help pages. (That is true for Excel 2003,
at least.) Personally, I don't use them.

Bringing this down to earth, if a bank specifies an "interest
rate" (those words have a technical meaning) of 6% with daily
compounding, then the APY is (1 + 6%)^365 - 1, which is the same as
FV(6%/365, 365, 0, -1) - 1. That is approximately 6.1831%.

Conversely, if a bank or investment institution specifies an APY of
6.1831%, that compares to a nominal "interest rate" with daily
compounding of 365 * ((1 + 6.1831%)^(1/365) - 1), which is the same as
365*RATE(365, 0, -1, 1+6.1831%). Of course, that should be 6%. (I
always use the exact result of interest calculations, not their
approximation.)

Complicating the whole thing is that (US) banks usually use an average
daily balance method to compute the periodic rate and, hence, the
APY. But that should make a difference only if the balance is
changing due to deposits and withdrawals. In your simple case, you
specified only an initial investment (or a value on a particular
date).

HTH.


Endnotes:

[1] Financial institutions have the option of using 366 for the daily
frequency in leap years. I presume that most do because it is to
their advantage. (A very-slightly lower daily rate. But remember:
large banks multiply that infinitesimal fractional difference times
thousands of accounts, some of which have 6- and 7-digit balances.)
  #10   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,059
Default Formula to Calculate Compounded Percentage Per Year

Errata....

On Apr 21, 4:28*pm, joeu2004 I wrote:
On Apr 21, 2:55*pm, Dermot wrote:

The calculation was explained to me as 6% per annum.......I assumed from
that it was compounded each year....as you have explained.......I will need
to double check I have the correct understanding of the required calculation.
The seed of doubt!


A pretty big seed, IMHO.


Unless you are talking about a bond.


  #11   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,059
Default Formula to Calculate Compounded Percentage Per Year

Errata*2....

On Apr 21, 4:28*pm, I wrote:
On Apr 21, 2:55*pm, Dermot wrote:
Can you explain "6% Nominal Interest for me....to ensure I understand the
term. *Would that be 6% of First Year + 6% of Second Year = 6% of remaining
year fraction?.....not compounding?


It has nothing to do with year-to-year compounding or not. *In US law,
interest must compound at least annually. *At issue is how intra-
annual rates are computed (e.g. daily, monthly, quarterly).


Unless you are talking about a bond.

But you since your subject line refers to a compounded rate, I ass-u-
me-d you were talking about an investment whose yield compounds.

[PS: My previous errata quoted the wrong part of Dermot's posting. I
would have linked this follow-up to that first errata, but sigh, I do
not see it in Google Groups yet.]
  #12   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 137
Default Formula to Calculate Compounded Percentage Per Year

Hi Joeu

Thanks for the thorough explanations, some of the functions I did not know
existed.

It's annual statutory interest of 6% on each individual annual amount.
The calculation is not compounded...it's an annual interest of 6% on the
first year
The second year is calculated separately at another 6% and then the
remaining days to get the value I am seeking....I am not sure what category
this would fall under but it's not compounded.

What formula would you suggest for the above calculation.

Thanks again
"joeu2004" wrote:

Errata....

On Apr 21, 4:28 pm, joeu2004 I wrote:
On Apr 21, 2:55 pm, Dermot wrote:

The calculation was explained to me as 6% per annum.......I assumed from
that it was compounded each year....as you have explained.......I will need
to double check I have the correct understanding of the required calculation.
The seed of doubt!


A pretty big seed, IMHO.


Unless you are talking about a bond.

  #13   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,059
Default Formula to Calculate Compounded Percentage Per Year

On Apr 22, 11:35*am, Dermot wrote:
It's *annual statutory interest of 6% on each individual annual amount.


Again, it would help to know what type of investment you are talking
about. Sounds like a bond.

It might also help to know what country's laws control the "statutory"
interest rate.


The calculation is not compounded...it's an annual interest of 6% on the
first year
The second year is calculated separately at another 6% and then the
remaining days to get the value I am seeking....I am not sure what category
this would fall under but it's not compounded.


It is called simple interest.


What formula would you suggest for the above calculation.


For simple interest, the approximate formula would be:

=14000 * 6% * (A2 - A1) / 365

where A1 and A2 are the starting and ending dates of the investment
respectively.

A more precise formula might use 14000*6%*DATEDIF(A1,A2,"y") +
14000*6%*f/365, where "f" is the number of remaining days -- similar
to what you hypothesized in your original posting.

Sorry if that is not specific enough for your use. I have to think
more about how to compute "f". I believe there are also some
"gotchas" to beware when using DATEDIF. But I have to run.

Post back if you need details.

  #14   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 137
Default Formula to Calculate Compounded Percentage Per Year

Hi Joeu

Quote
A more precise formula might use 14000*6%*DATEDIF(A1,A2,"y") +
14000*6%*f/365, where "f" is the number of remaining days -- similar
to what you hypothesized in your original posting.

Sorry if that is not specific enough for your use. I have to think
more about how to compute "f". I believe there are also some
"gotchas" to beware when using DATEDIF. But I have to run.

Ah, it's the "F" part that I was trying to figure out how to incorporate
it.....without overcomplicating things......I've done a good job of that
already!!!

Thanks in advance


"joeu2004" wrote:

On Apr 22, 11:35 am, Dermot wrote:
It's annual statutory interest of 6% on each individual annual amount.


Again, it would help to know what type of investment you are talking
about. Sounds like a bond.

It might also help to know what country's laws control the "statutory"
interest rate.


The calculation is not compounded...it's an annual interest of 6% on the
first year
The second year is calculated separately at another 6% and then the
remaining days to get the value I am seeking....I am not sure what category
this would fall under but it's not compounded.


It is called simple interest.


What formula would you suggest for the above calculation.


For simple interest, the approximate formula would be:

=14000 * 6% * (A2 - A1) / 365

where A1 and A2 are the starting and ending dates of the investment
respectively.

A more precise formula might use 14000*6%*DATEDIF(A1,A2,"y") +
14000*6%*f/365, where "f" is the number of remaining days -- similar
to what you hypothesized in your original posting.

Sorry if that is not specific enough for your use. I have to think
more about how to compute "f". I believe there are also some
"gotchas" to beware when using DATEDIF. But I have to run.

Post back if you need details.


  #15   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 137
Default Formula to Calculate Compounded Percentage Per Year

Joeu2004
How would you incorporate the "F" in the formula?

"Dermot" wrote:

Hi Joeu

Quote
A more precise formula might use 14000*6%*DATEDIF(A1,A2,"y") +
14000*6%*f/365, where "f" is the number of remaining days -- similar
to what you hypothesized in your original posting.

Sorry if that is not specific enough for your use. I have to think
more about how to compute "f". I believe there are also some
"gotchas" to beware when using DATEDIF. But I have to run.

Ah, it's the "F" part that I was trying to figure out how to incorporate
it.....without overcomplicating things......I've done a good job of that
already!!!

Thanks in advance


"joeu2004" wrote:

On Apr 22, 11:35 am, Dermot wrote:
It's annual statutory interest of 6% on each individual annual amount.


Again, it would help to know what type of investment you are talking
about. Sounds like a bond.

It might also help to know what country's laws control the "statutory"
interest rate.


The calculation is not compounded...it's an annual interest of 6% on the
first year
The second year is calculated separately at another 6% and then the
remaining days to get the value I am seeking....I am not sure what category
this would fall under but it's not compounded.


It is called simple interest.


What formula would you suggest for the above calculation.


For simple interest, the approximate formula would be:

=14000 * 6% * (A2 - A1) / 365

where A1 and A2 are the starting and ending dates of the investment
respectively.

A more precise formula might use 14000*6%*DATEDIF(A1,A2,"y") +
14000*6%*f/365, where "f" is the number of remaining days -- similar
to what you hypothesized in your original posting.

Sorry if that is not specific enough for your use. I have to think
more about how to compute "f". I believe there are also some
"gotchas" to beware when using DATEDIF. But I have to run.

Post back if you need details.




  #16   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,059
Default Formula to Calculate Compounded Percentage Per Year

On Apr 23, 5:47 pm, Dermot wrote:
A more precise formula might use 14000*6%*DATEDIF(A1,A2,"y") +
14000*6%*f/365, where "f" is the number of remaining days -- similar
to what you hypothesized in your original posting.


How would you incorporate the "F" in the formula?


One way -- not very elegant, but it seems to work:

=14000*6%*datedif(A1,A2,"y") +
14000*6%*(A2 - date(year(A1)+datedif(A1,A2,"y"),month(A1),day(A1) ))/
365

And instead of 365, you might write (date(1+year(A1),1,1)-
date(year(A1),1,1)), if you want to use 366 in leap years.

I'm "sure" there is a better, easier way. But it eludes me at the
moment.

Also, I am leery about using DATEDIF. I remember it has its quirks.
But I cannot remember them off-hand. Perhaps my recollection is
wrong.


----- previous posting -----

On Apr 23, 5:47*pm, Dermot wrote:
Joeu2004
How would you incorporate the "F" in the formula?



"Dermot" wrote:
Hi Joeu


Quote
A more precise formula might use 14000*6%*DATEDIF(A1,A2,"y") +
14000*6%*f/365, where "f" is the number of remaining days -- similar
to what you hypothesized in your original posting.


Sorry if that is not specific enough for your use. I have to think
more about how to compute "f". I believe there are also some
"gotchas" to beware when using DATEDIF. But I have to run.


Ah, it's the "F" part that I was trying to figure out how to incorporate
it.....without overcomplicating things......I've done a good job of that
already!!!


Thanks in advance


"joeu2004" wrote:


On Apr 22, 11:35 am, Dermot wrote:
It's *annual statutory interest of 6% on each individual annual amount.


Again, it would help to know what type of investment you are talking
about. *Sounds like a bond.


It might also help to know what country's laws control the "statutory"
interest rate.


The calculation is not compounded...it's an annual interest of 6% on the
first year
The second year is calculated separately at another 6% and then the
remaining days to get the value I am seeking....I am not sure what category
this would fall under but it's not compounded.


It is called simple interest.


What formula would you suggest for the above calculation.


For simple interest, the approximate formula would be:


=14000 * 6% * (A2 - A1) / 365


where A1 and A2 are the starting and ending dates of the investment
respectively.


A more precise formula might use 14000*6%*DATEDIF(A1,A2,"y") +
14000*6%*f/365, where "f" is the number of remaining days -- similar
to what you hypothesized in your original posting.


Sorry if that is not specific enough for your use. *I have to think
more about how to compute "f". *I believe there are also some
"gotchas" to beware when using DATEDIF. *But I have to run.


Post back if you need details.

  #17   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 137
Default Formula to Calculate Compounded Percentage Per Year


Thanks Joeu 2004

"joeu2004" wrote:

On Apr 23, 5:47 pm, Dermot wrote:
A more precise formula might use 14000*6%*DATEDIF(A1,A2,"y") +
14000*6%*f/365, where "f" is the number of remaining days -- similar
to what you hypothesized in your original posting.


How would you incorporate the "F" in the formula?


One way -- not very elegant, but it seems to work:

=14000*6%*datedif(A1,A2,"y") +
14000*6%*(A2 - date(year(A1)+datedif(A1,A2,"y"),month(A1),day(A1) ))/
365

And instead of 365, you might write (date(1+year(A1),1,1)-
date(year(A1),1,1)), if you want to use 366 in leap years.

I'm "sure" there is a better, easier way. But it eludes me at the
moment.

Also, I am leery about using DATEDIF. I remember it has its quirks.
But I cannot remember them off-hand. Perhaps my recollection is
wrong.


----- previous posting -----

On Apr 23, 5:47 pm, Dermot wrote:
Joeu2004
How would you incorporate the "F" in the formula?



"Dermot" wrote:
Hi Joeu


Quote
A more precise formula might use 14000*6%*DATEDIF(A1,A2,"y") +
14000*6%*f/365, where "f" is the number of remaining days -- similar
to what you hypothesized in your original posting.


Sorry if that is not specific enough for your use. I have to think
more about how to compute "f". I believe there are also some
"gotchas" to beware when using DATEDIF. But I have to run.


Ah, it's the "F" part that I was trying to figure out how to incorporate
it.....without overcomplicating things......I've done a good job of that
already!!!


Thanks in advance


"joeu2004" wrote:


On Apr 22, 11:35 am, Dermot wrote:
It's annual statutory interest of 6% on each individual annual amount.


Again, it would help to know what type of investment you are talking
about. Sounds like a bond.


It might also help to know what country's laws control the "statutory"
interest rate.


The calculation is not compounded...it's an annual interest of 6% on the
first year
The second year is calculated separately at another 6% and then the
remaining days to get the value I am seeking....I am not sure what category
this would fall under but it's not compounded.


It is called simple interest.


What formula would you suggest for the above calculation.


For simple interest, the approximate formula would be:


=14000 * 6% * (A2 - A1) / 365


where A1 and A2 are the starting and ending dates of the investment
respectively.


A more precise formula might use 14000*6%*DATEDIF(A1,A2,"y") +
14000*6%*f/365, where "f" is the number of remaining days -- similar
to what you hypothesized in your original posting.


Sorry if that is not specific enough for your use. I have to think
more about how to compute "f". I believe there are also some
"gotchas" to beware when using DATEDIF. But I have to run.


Post back if you need details.


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to calculate a percentage formula with existing data? MeaganNW Excel Worksheet Functions 4 September 8th 08 08:39 AM
calculate compounded daily interest Petro Excel Discussion (Misc queries) 3 January 11th 08 02:46 AM
How do I calculate year vs. year sales in a percentage? johngalt Excel Discussion (Misc queries) 2 January 10th 07 07:23 PM
how to calculate quarterly compounded interest in ms excel Shreepad Excel Discussion (Misc queries) 1 September 15th 05 04:59 PM
How do I create a formula to calculate the average percentage rat LD Excel Worksheet Functions 5 January 13th 05 07:17 PM


All times are GMT +1. The time now is 11:28 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"