Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to calculate a percentage formula with existing data? | Excel Worksheet Functions | |||
calculate compounded daily interest | Excel Discussion (Misc queries) | |||
How do I calculate year vs. year sales in a percentage? | Excel Discussion (Misc queries) | |||
how to calculate quarterly compounded interest in ms excel | Excel Discussion (Misc queries) | |||
How do I create a formula to calculate the average percentage rat | Excel Worksheet Functions |