steve1040 said:
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).
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 -----
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
- 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
