Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate Daily Interest, Paid out Weekly
Not sure if the subject makes sense
I have 2 dates Rate = 8% Start = 12/28/2009 End = 2/18/2010 This is 7 weeks 3 days Daily interest = .08/365 = 0.02192% How do I find what the total for say $35.95 and the end of the period 02/18/2010 - If daily interest is applied at the end of every week. 1. Do I add the 3 days to 35.95 before calculating week1? 2. Is there a built in function that does this I'm thinking the total should be around 253.20 + the 3 days Thanks Steve |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate Daily Interest, Paid out Weekly
What is the principal and when is it paid. Are you talking about one
investment of $35.95 or a payment of $35.95 each week? There is a formula for computing future value, but I dont understand your question. Tom "steve1040" wrote: Not sure if the subject makes sense I have 2 dates Rate = 8% Start = 12/28/2009 End = 2/18/2010 This is 7 weeks 3 days Daily interest = .08/365 = 0.02192% How do I find what the total for say $35.95 and the end of the period 02/18/2010 - If daily interest is applied at the end of every week. 1. Do I add the 3 days to 35.95 before calculating week1? 2. Is there a built in function that does this I'm thinking the total should be around 253.20 + the 3 days Thanks Steve . |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate Daily Interest, Paid out Weekly
On Feb 18, 2:10*pm, tompl wrote:
What is the principal and when is it paid. *Are you talking about one investment of $35.95 or a payment of $35.95 each week? *There is a formula for computing future value, but I don’t understand your question. Tom "steve1040" wrote: Not sure if the subject makes sense I have 2 dates Rate = 8% Start = 12/28/2009 End = 2/18/2010 This is 7 weeks 3 days Daily interest = .08/365 = 0.02192% How do I find what the total for say $35.95 and the end of the period 02/18/2010 - If daily interest is applied at the end of every week. *1. Do I add the 3 days to 35.95 before calculating week1? *2. Is there a built in function that does this I'm thinking the total should be around 253.20 + the 3 days Thanks Steve .- Hide quoted text - - Show quoted text - 35.95 paid weekly by me Every 7 days (Weekly) The interest will be added to my account for the amount that is in my account. So at the end of the first 7 days I should have a total of 35.95 + 0.055 (Interest) = 36.01 Week 2 = 36.01 + Daily interest * 7 etc Instead of doing this for each week I just want the total at the end date along with |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate Daily Interest, Paid out Weekly
On Thu, 18 Feb 2010 14:05:38 -0800 (PST), steve1040
wrote: On Feb 18, 2:10*pm, tompl wrote: What is the principal and when is it paid. *Are you talking about one investment of $35.95 or a payment of $35.95 each week? *There is a formula for computing future value, but I don’t understand your question. Tom "steve1040" wrote: Not sure if the subject makes sense I have 2 dates Rate = 8% Start = 12/28/2009 End = 2/18/2010 This is 7 weeks 3 days Daily interest = .08/365 = 0.02192% How do I find what the total for say $35.95 and the end of the period 02/18/2010 - If daily interest is applied at the end of every week. *1. Do I add the 3 days to 35.95 before calculating week1? *2. Is there a built in function that does this I'm thinking the total should be around 253.20 + the 3 days Thanks Steve .- Hide quoted text - - Show quoted text - 35.95 paid weekly by me Every 7 days (Weekly) The interest will be added to my account for the amount that is in my account. So at the end of the first 7 days I should have a total of 35.95 + 0.055 (Interest) = 36.01 Week 2 = 36.01 + Daily interest * 7 etc Instead of doing this for each week I just want the total at the end date along with It'd probably be 253.20 + 3 days interest at the end. If so, the formula might be something like: =FV(0.08/52,7+3/7,-35.95,,1) --ron |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate Daily Interest, Paid out Weekly
This is a pretty complicated and unique scenario.
Assuming that you make a payment at the beginning of the term and at the beginning of every full seven day period during the term (the beginning of the final 3 day period would not include a payment) then the balance at the end of the term can be represented by this formula: =FV(B3/365*7,TRUNC((B2-B1)/7),-B4,,1)*((B3/365*MOD(B2-B1,7))+1) B1 is the start date B2 is the end date B3 is the annual interest rate B4 is the weekly payment amount This formula should work for any term that is greater than 7 days. With the details you provided the end value is $253.37 Tom |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate Daily Interest, Paid out Weekly
"steve1040" wrote:
Instead of doing this for each week I just want the total at the end date Okay. But it would be prudent to compare any one-line formula with the results of a weekly accumulation schedule just to double-check. And even though you will be interested in rounding the result to 2 decimal places (cents), I suggest that you avoid rounding for now and format numbers to, say, 8 decimal places to identified differences. Even though those differences might be beyond 2 decimal places for your example, they might make a noticable difference with large numbers. The "right" solution is not only one that gives the right answer (especially when rounded), but also one that does it the right way (ergo, it works in all cases). I'm thinking the total should be around 253.20 + the 3 days Yes. But the devil is the details. For one thing, you neglected to state the compounding frequency, the type of account, and/or the jurisdiction (probably US, but other countries use "$" for currency). Just because interest is posted to account weekly, that does not mean that it is computed weekly, compounded weekly, or even compounded at all. Moreover, that does not mean that interest is prorated by dividing by 52. In fact, for US accounts controlled by the Truth In Savings Act (at least every account insured by FDIC; but notably not necessarily brokerage accounts), interest is computed on a daily basis, regardless of the payment frequency and the compounding frequency, if compounded. So the first nut to crack is: the weekly interest rate is one of the following, but we do not know which one: B2: =A2/52 (compounded weekly, if at all) B2: =A2*7/365 (compounded weekly, if at all) B2: =(1+A2/365)^7 (compounded daily; paid weekly) where A2 is the annual interest rate (8%). Only the latter two apply to US accounts controlled by TISA. Theoretically, the first formula might apply to other accounts. But I doubt it because it results in a higher weekly rate than even the last formula (compounded daily). Another real-world consideration is: to what precision (number of decimal places) are periodic interest rate and periodic interest "payments" computed? That will vary from institution to institution. TISA has a very lax requirement for what constitutes an accurate percentage yield. I will assume that interest rates and payments are maintained to the full precision of Excel numbers, which are stored as IEEE 64-bit floating-point values. Again, for such small numbers as your example, this will not make a noticable difference. But in some cases, it might explain small differences between yours and a bank's calculations, usually less than $1. If you take my opening advice and prototype the solution for your example by calculating an 8-week (7-week 3-day) accumulation table, I think you will find that the correct solution is, depending on assumptions made for B2: (a) if compounded daily: =fv(A2/365,mod(B4,7),0,-fv(B2,int(B4/7),-A1,0,1)-A1) or =(fv(B2,int(B4/7),-A1,0,1)+A1) * (1 + A2/365)^mod(B4,7) or (b) if compounded weekly: =(fv(B2,int(B4/7),-A1,0,1)+A1) * (1 + A2*mod(B4,7)/365) where A1 is the weekly contribution (35.95), A2 is the annual interest rate (8%), and B2 is the chosen method for determining the weekly interest rate (see above). B4 is the number of days, computed by =A4-A3, where A3 is beginning (12/28/2009) and A4 is the ending date (2/18/2010). That results in about 289.34, when rounded, regardless of the method used for B2. But again, the difference is in the right-most decimal places, which might make a difference with larger numbers. Note the 1 in the last term of the inner FV() expression. This assumes that contributions are made at the beginning of each weekly period. Also note that A1 is added in the last term (with a minus sign in some cases to identify the direction of the cash flow). This assumes that you will make a contribution at the beginning of the 8th period, even though it is a short week (3 days). In contrast, note that the following gives a very different result (about 268.79). =fv(A2/52,B4/7,-A1,0,1) Admittedly, I have used this formula myself in the past, having not given it sufficient thought. I confess that I do not know off-hand why that result is so far off. It does equal the equivalent formula in the PV help page, namely: =A1*(1+A2/52)*((1+A2/52)^(B4/7)-1)/(A2/52) But that should be equivalent to one of the following (rounded results shown in parentheses), which obvious it is not: =fv(A2/52,int(B4/7),-A1,0,1)*(1+A2/52)^mod(B4,7) (254.37) or =(fv(A2/52,int(B4/7),-A1,0,1)+A1)*(1+A2/52)^mod(B4,7) (290.49) Oh well, perhaps if I have been looking at this too long are too long a day. :-( ----- original message ----- "steve1040" wrote in message ... On Feb 18, 2:10 pm, tompl wrote: What is the principal and when is it paid. Are you talking about one investment of $35.95 or a payment of $35.95 each week? There is a formula for computing future value, but I don’t understand your question. Tom "steve1040" wrote: Not sure if the subject makes sense I have 2 dates Rate = 8% Start = 12/28/2009 End = 2/18/2010 This is 7 weeks 3 days Daily interest = .08/365 = 0.02192% How do I find what the total for say $35.95 and the end of the period 02/18/2010 - If daily interest is applied at the end of every week. 1. Do I add the 3 days to 35.95 before calculating week1? 2. Is there a built in function that does this I'm thinking the total should be around 253.20 + the 3 days Thanks Steve .- Hide quoted text - - Show quoted text - 35.95 paid weekly by me Every 7 days (Weekly) The interest will be added to my account for the amount that is in my account. So at the end of the first 7 days I should have a total of 35.95 + 0.055 (Interest) = 36.01 Week 2 = 36.01 + Daily interest * 7 etc Instead of doing this for each week I just want the total at the end date along with |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculate Interest Paid w/ Additional Payments to Principle | Excel Worksheet Functions | |||
Calculate Daily Interest | Excel Discussion (Misc queries) | |||
calculate compounded daily interest | Excel Discussion (Misc queries) | |||
HOW to calculate the interest paid between 2 custom periods? | Excel Worksheet Functions | |||
How can I calculate weekly totals of daily data in Excel | Excel Worksheet Functions |