Help on Interest calculation? Advanced maths ?

This worksheet works, for one scenario.  Need help, please, for the second 
scenario

Initial deposit �250, invest regular �250 per month.  Gross interest 5.6%, 
calculated daily, paid on the anniversary of account opening (i.e 366 days)

The 5.6 is in range-named cell "APR".   Cells B11..B376 are "=$E10...", 
Cells $D10... are "=$B10...+$C10...)
Formula in (CALC column) cells E10...E375 is 
=POWER(APR/100+1,1/365)*$B10....
Spreadsheet accuracy - exact to the penny (agrees the bank's printed 
statement).

06       A        B                 C                D                 E 
F
07    day      daily            deposit        subtotal        CALC 
daily
08      #        total 
interest
09
10      0            0.00        250.00    250.00000  250.03732   0.03732
11      1        250.04                       250.03732  250.07465   0.03733
12      2        250.07                       250.03733  250.07250   0.03733
continue to row 380 (366 days)
with 12 monthly deposits of �250
        day      daily            deposit 
daily int
        #          total              total 
total
376   366   3342.22        3250.00 
92.22

Second scenario is slightly different.  There's just ONE initial deposit, 
interest still calculated daily, but added monthly.
�100,000 would thus become �105,750 after 1 year, at 5.75% gross interest 
rate, with 12 interest payments credited into the "deposit" column.

I'm using the following formula to calculate the Annual Effective Rate 
(AER).
Gross interest rate of 5.75 (rangename APR)
The number "12" (in cell rangename PERIOD) = the number of periodic interest 
payments - so
=(POWER((1+APR/100),1/PERIOD)-1)*PERIOD*100 which converts to 
=(POWER((1+5.75/100),1/12)-1)*12*100, evaluates to 5.60381 (which is in 
"rangenamed" cell AER).
Using a spreadsheet formatted similar to example above, the "CALC" cells 
have slightly changed formula =POWER(AER/100+1,1/365)*$B10 ,(AER replacing 
APR).  BUT I cannot get a sensible result - it's known totals - should be 
�105,750.00 / �5750.00.  Where am I going wrong ?  Is there a logic fault? 
(I did take out what would have been duplication of the 12 monthly interest 
additions, in the running total column).

I'm not an advanced mathematician.  Have a friend, who is, he created the 
Excel formulae from the British Banking Association;s (hugely complex) 
mathematical formulae.

HELP ! please?





0
nospam5510 (78)
4/25/2007 4:27:44 PM
excel 39879 articles. 2 followers. Follow

16 Replies
812 Views

Similar Articles

[PageSpeed] 42

Hi Silvabod,

You're making this hard for yourself. Check out Excel's financial functions.

Cheers
-- 
macropod
[MVP - Microsoft Word]
-------------------------

"Silvabod" <nospam@thank.you> wrote in message news:4kLXh.105$en5.56@newsfe6-win.ntli.net...
> This worksheet works, for one scenario.  Need help, please, for the second scenario
>
> Initial deposit �250, invest regular �250 per month.  Gross interest 5.6%, calculated daily, paid on the anniversary of account 
> opening (i.e 366 days)
>
> The 5.6 is in range-named cell "APR".   Cells B11..B376 are "=$E10...", Cells $D10... are "=$B10...+$C10...)
> Formula in (CALC column) cells E10...E375 is =POWER(APR/100+1,1/365)*$B10....
> Spreadsheet accuracy - exact to the penny (agrees the bank's printed statement).
>
> 06       A        B                 C                D                 E F
> 07    day      daily            deposit        subtotal        CALC daily
> 08      #        total interest
> 09
> 10      0            0.00        250.00    250.00000  250.03732   0.03732
> 11      1        250.04                       250.03732  250.07465   0.03733
> 12      2        250.07                       250.03733  250.07250   0.03733
> continue to row 380 (366 days)
> with 12 monthly deposits of �250
>        day      daily            deposit daily int
>        #          total              total total
> 376   366   3342.22        3250.00 92.22
>
> Second scenario is slightly different.  There's just ONE initial deposit, interest still calculated daily, but added monthly.
> �100,000 would thus become �105,750 after 1 year, at 5.75% gross interest rate, with 12 interest payments credited into the 
> "deposit" column.
>
> I'm using the following formula to calculate the Annual Effective Rate (AER).
> Gross interest rate of 5.75 (rangename APR)
> The number "12" (in cell rangename PERIOD) = the number of periodic interest payments - so
> =(POWER((1+APR/100),1/PERIOD)-1)*PERIOD*100 which converts to =(POWER((1+5.75/100),1/12)-1)*12*100, evaluates to 5.60381 (which is 
> in "rangenamed" cell AER).
> Using a spreadsheet formatted similar to example above, the "CALC" cells have slightly changed formula 
> =POWER(AER/100+1,1/365)*$B10 ,(AER replacing APR).  BUT I cannot get a sensible result - it's known totals - should be �105,750.00 
> / �5750.00.  Where am I going wrong ?  Is there a logic fault? (I did take out what would have been duplication of the 12 monthly 
> interest additions, in the running total column).
>
> I'm not an advanced mathematician.  Have a friend, who is, he created the Excel formulae from the British Banking Association;s 
> (hugely complex) mathematical formulae.
>
> HELP ! please?
>
>
>
>
> 

0
invalid (293)
4/26/2007 8:50:21 AM
Macropod, thanks for the suggestion - unfortunately there isn't a pre-formed 
Excel financial function for posted "problem" scenario.  ACCRINT is close, 
but unless "periods" can be modified (and I don't have the core 
knowledge/skill to do it) it's not the answer.  Critically, please see 
penultimate para.

I posted the problem example in its simplest form.  One deposit, one 
interest rate, with "interest calculated daily, credited monthly".  I now 
see that this could be interpreted as a bond.  It's not, it's a deposit 
account.  I should perhaps have mentioned it, to avoid confusion.

Actuality - during any year, there's Capital credits and debits, and the 
interest rate is variable.  Further complication - if the monthly interest 
"due" date (calendar month-end) is a non-banking day (weekend, or Bank 
Holiday) interest is credited on first "working" day thereafter.  Periods 
are therefore yet another variable, hence the need for daily calculation. 
(Which is why I reduced the posting to basics!)  All these I can handle, 
once the accurate "daily calculation" methodology for the posted example is 
in place.  The formula's test/proof is the answer "5750.00" after 365 
iterations - I'd be happy with +/- �1.00 difference.

Per Google search "Excel ACCRINT", ACCRINT gives different results for the 
same data, depending on which "flavour" of Excel is used.  I need 
mathematical accuracy, not an approximation.

Can you help further, please?
Silvabod.

"macropod" <invalid@invalid.invalid> wrote in message 
news:OzNom$9hHHA.1240@TK2MSFTNGP04.phx.gbl...
> Hi Silvabod,
>
> You're making this hard for yourself. Check out Excel's financial 
> functions.
>
> Cheers
> -- 
> macropod
> [MVP - Microsoft Word]
> -------------------------
>
> "Silvabod" <nospam@thank.you> wrote in message 
> news:4kLXh.105$en5.56@newsfe6-win.ntli.net...
>> This worksheet works, for one scenario.  Need help, please, for the 
>> second scenario
>>
>> Initial deposit �250, invest regular �250 per month.  Gross interest 
>> 5.6%, calculated daily, paid on the anniversary of account opening (i.e 
>> 366 days)
>>
>> The 5.6 is in range-named cell "APR".   Cells B11..B376 are "=$E10...", 
>> Cells $D10... are "=$B10...+$C10...)
>> Formula in (CALC column) cells E10...E375 is 
>> =POWER(APR/100+1,1/365)*$B10....
>> Spreadsheet accuracy - exact to the penny (agrees the bank's printed 
>> statement).
>>
>> 06       A        B                 C                D                 E 
>> F
>> 07    day      daily            deposit        subtotal        CALC daily
>> 08      #        total interest
>> 09
>> 10      0            0.00        250.00    250.00000  250.03732   0.03732
>> 11      1        250.04                       250.03732  250.07465 
>> 0.03733
>> 12      2        250.07                       250.03733  250.07250 
>> 0.03733
>> continue to row 380 (366 days)
>> with 12 monthly deposits of �250
>>        day      daily            deposit daily int
>>        #          total              total total
>> 376   366   3342.22        3250.00 92.22
>>
>> Second scenario is slightly different.  There's just ONE initial deposit, 
>> interest still calculated daily, but added monthly.
>> �100,000 would thus become �105,750 after 1 year, at 5.75% gross interest 
>> rate, with 12 interest payments credited into the "deposit" column.
>>
>> I'm using the following formula to calculate the Annual Effective Rate 
>> (AER).
>> Gross interest rate of 5.75 (rangename APR)
>> The number "12" (in cell rangename PERIOD) = the number of periodic 
>> interest payments - so
>> =(POWER((1+APR/100),1/PERIOD)-1)*PERIOD*100 which converts to 
>> =(POWER((1+5.75/100),1/12)-1)*12*100, evaluates to 5.60381 (which is in 
>> "rangenamed" cell AER).
>> Using a spreadsheet formatted similar to example above, the "CALC" cells 
>> have slightly changed formula =POWER(AER/100+1,1/365)*$B10 ,(AER 
>> replacing APR).  BUT I cannot get a sensible result - it's known totals - 
>> should be �105,750.00 / �5750.00.  Where am I going wrong ?  Is there a 
>> logic fault? (I did take out what would have been duplication of the 12 
>> monthly interest additions, in the running total column).
>>
>> I'm not an advanced mathematician.  Have a friend, who is, he created the 
>> Excel formulae from the British Banking Association;s (hugely complex) 
>> mathematical formulae.
>>
>> HELP ! please?
>>
>>
>>
>>
>>
> 


0
nospam5510 (78)
4/26/2007 4:37:41 PM
Hi Silvabod,

If you're going to try working EOMonth weekends and holidays into your calculations, you're really looking at a highly customised 
version of What Excel does. I suppose you could construct formulae to do that, but a UDF might be easier to manage in the long term, 
and would probably lead to a smaller and faster workbook if there's a great number of calculations to perform.

Cheers
-- 
macropod
[MVP - Microsoft Word]
-------------------------

"Silvabod" <nospam@thank.you> wrote in message news:pz4Yh.582$s35.342@newsfe4-gui.ntli.net...
> Macropod, thanks for the suggestion - unfortunately there isn't a pre-formed Excel financial function for posted "problem" 
> scenario.  ACCRINT is close, but unless "periods" can be modified (and I don't have the core knowledge/skill to do it) it's not 
> the answer.  Critically, please see penultimate para.
>
> I posted the problem example in its simplest form.  One deposit, one interest rate, with "interest calculated daily, credited 
> monthly".  I now see that this could be interpreted as a bond.  It's not, it's a deposit account.  I should perhaps have mentioned 
> it, to avoid confusion.
>
> Actuality - during any year, there's Capital credits and debits, and the interest rate is variable.  Further complication - if the 
> monthly interest "due" date (calendar month-end) is a non-banking day (weekend, or Bank Holiday) interest is credited on first 
> "working" day thereafter.  Periods are therefore yet another variable, hence the need for daily calculation. (Which is why I 
> reduced the posting to basics!)  All these I can handle, once the accurate "daily calculation" methodology for the posted example 
> is in place.  The formula's test/proof is the answer "5750.00" after 365 iterations - I'd be happy with +/- �1.00 difference.
>
> Per Google search "Excel ACCRINT", ACCRINT gives different results for the same data, depending on which "flavour" of Excel is 
> used.  I need mathematical accuracy, not an approximation.
>
> Can you help further, please?
> Silvabod.
>
> "macropod" <invalid@invalid.invalid> wrote in message news:OzNom$9hHHA.1240@TK2MSFTNGP04.phx.gbl...
>> Hi Silvabod,
>>
>> You're making this hard for yourself. Check out Excel's financial functions.
>>
>> Cheers
>> -- 
>> macropod
>> [MVP - Microsoft Word]
>> -------------------------
>>
>> "Silvabod" <nospam@thank.you> wrote in message news:4kLXh.105$en5.56@newsfe6-win.ntli.net...
>>> This worksheet works, for one scenario.  Need help, please, for the second scenario
>>>
>>> Initial deposit �250, invest regular �250 per month.  Gross interest 5.6%, calculated daily, paid on the anniversary of account 
>>> opening (i.e 366 days)
>>>
>>> The 5.6 is in range-named cell "APR".   Cells B11..B376 are "=$E10...", Cells $D10... are "=$B10...+$C10...)
>>> Formula in (CALC column) cells E10...E375 is =POWER(APR/100+1,1/365)*$B10....
>>> Spreadsheet accuracy - exact to the penny (agrees the bank's printed statement).
>>>
>>> 06       A        B                 C                D                 E F
>>> 07    day      daily            deposit        subtotal        CALC daily
>>> 08      #        total interest
>>> 09
>>> 10      0            0.00        250.00    250.00000  250.03732   0.03732
>>> 11      1        250.04                       250.03732  250.07465 0.03733
>>> 12      2        250.07                       250.03733  250.07250 0.03733
>>> continue to row 380 (366 days)
>>> with 12 monthly deposits of �250
>>>        day      daily            deposit daily int
>>>        #          total              total total
>>> 376   366   3342.22        3250.00 92.22
>>>
>>> Second scenario is slightly different.  There's just ONE initial deposit, interest still calculated daily, but added monthly.
>>> �100,000 would thus become �105,750 after 1 year, at 5.75% gross interest rate, with 12 interest payments credited into the 
>>> "deposit" column.
>>>
>>> I'm using the following formula to calculate the Annual Effective Rate (AER).
>>> Gross interest rate of 5.75 (rangename APR)
>>> The number "12" (in cell rangename PERIOD) = the number of periodic interest payments - so
>>> =(POWER((1+APR/100),1/PERIOD)-1)*PERIOD*100 which converts to =(POWER((1+5.75/100),1/12)-1)*12*100, evaluates to 5.60381 (which 
>>> is in "rangenamed" cell AER).
>>> Using a spreadsheet formatted similar to example above, the "CALC" cells have slightly changed formula 
>>> =POWER(AER/100+1,1/365)*$B10 ,(AER replacing APR).  BUT I cannot get a sensible result - it's known totals - should be 
>>> �105,750.00 / �5750.00.  Where am I going wrong ?  Is there a logic fault? (I did take out what would have been duplication of 
>>> the 12 monthly interest additions, in the running total column).
>>>
>>> I'm not an advanced mathematician.  Have a friend, who is, he created the Excel formulae from the British Banking Association;s 
>>> (hugely complex) mathematical formulae.
>>>
>>> HELP ! please?
>>>
>>>
>>>
>>>
>>>
>>
>
> 

0
invalid (293)
4/28/2007 2:14:12 AM
[Possible reposting.  Google Groups seems to be having trouble again :-(.]

On Apr 25, 8:27 am, "Silvabod" <nos...@thank.you> wrote:
> Gross interest 5.6%
> [....]
> =POWER(APR/100+1,1/365)*$B10

I am not familiar with the terminology in your region.  Here, you are using 
"gross interest" as if it were an "annual effective rate" -- that is, a 
compounded rate.  But....

> at 5.75% gross interest rate, with 12 interest payments credited
> into the "deposit" column.
> [....] 
> =(POWER((1+5.75/100),1/12)-1)*12*100, evaluates to 5.60381
> (which is in "rangenamed" cell AER).
> [....] =POWER(AER/100+1,1/365)*$B10
> [....] BUT I cannot get a sensible result 

Here, you are using 5.60381 as if it were an "annual effective rate", but 
you are computing it as a nominal rate -- that is, a simple (not compounded) 
rate.  That is probably why your result is not "sensible".

> There's just ONE initial deposit,
> interest still calculated daily, but added monthly.

The terminology is ambiguous (the banking industry's fault, not yours).  You 
might reasonably think this means that simple interest is computed each month 
and compounded monthly.  That is indeed one methodology that (US) banks use.  
I was going to suggest a model for that computation.  But....

> £100,000 would thus become £105,750 after 1 year

Based on that expectation, it appears that the bank simply means that 
interest is compounded daily at an "annual effective rate" of 5.75% -- the 
same methodology used in your first scenario.  This can be computed a number 
of equivalent ways.  Arguably, the simplest is:

=fv(rate(365,0,-1,1+5.75%), 365, 0, -100000)

You can replace RATE(...) with (1+5.75%)^(1/365)-1 or 
POWER(1+5.75%,1/365)-1, which is similar to what you have been doing.  And 
you can replace FV(...) with 100000*(1+r)^365, where "r" is RATE(...) or one 
of its alternatives.

HTH.


--- original posting (complete) ---

On Apr 25, 8:27 am, "Silvabod" <nos...@thank.you> wrote:
> This worksheet works, for one scenario.  Need help, please, for the second
> scenario
> 
> Initial deposit £250, invest regular £250 per month.  Gross interest 5.6%,
> calculated daily, paid on the anniversary of account opening (i.e 366 days)
> 
> The 5.6 is in range-named cell "APR".   Cells B11..B376 are "=$E10...",
> Cells $D10... are "=$B10...+$C10...)
> Formula in (CALC column) cells E10...E375 is
> =POWER(APR/100+1,1/365)*$B10....
> Spreadsheet accuracy - exact to the penny (agrees the bank's printed
> statement).
> 
> 06       A        B                 C                D                 E
> F
> 07    day      daily            deposit        subtotal        CALC
> daily
> 08      #        total
> interest
> 09
> 10      0            0.00        250.00    250.00000  250.03732   0.03732
> 11      1        250.04                       250.03732  250.07465   0.03733
> 12      2        250.07                       250.03733  250.07250   0.03733
> continue to row 380 (366 days)
> with 12 monthly deposits of £250
>         day      daily            deposit
> daily int
>         #          total              total
> total
> 376   366   3342.22        3250.00
> 92.22
> 
> Second scenario is slightly different.  There's just ONE initial deposit,
> interest still calculated daily, but added monthly.
> £100,000 would thus become £105,750 after 1 year, at 5.75% gross interest
> rate, with 12 interest payments credited into the "deposit" column.
> 
> I'm using the following formula to calculate the Annual Effective Rate
> (AER).
> Gross interest rate of 5.75 (rangename APR)
> The number "12" (in cell rangename PERIOD) = the number of periodic interest
> payments - so
> =(POWER((1+APR/100),1/PERIOD)-1)*PERIOD*100 which converts to
> =(POWER((1+5.75/100),1/12)-1)*12*100, evaluates to 5.60381 (which is in
> "rangenamed" cell AER).
> Using a spreadsheet formatted similar to example above, the "CALC" cells
> have slightly changed formula =POWER(AER/100+1,1/365)*$B10 ,(AER replacing
> APR).  BUT I cannot get a sensible result - it's known totals - should be
> £105,750.00 / £5750.00.  Where am I going wrong ?  Is there a logic fault?
> (I did take out what would have been duplication of the 12 monthly interest
> additions, in the running total column).
> 
> I'm not an advanced mathematician.  Have a friend, who is, he created the
> Excel formulae from the British Banking Association;s (hugely complex)
> mathematical formulae.
> 
> HELP ! please?


0
4/28/2007 4:18:03 AM
I wrote:
> Arguably, the simplest is:
> =fv(rate(365,0,-1,1+5.75%), 365, 0, -100000)
> 
> You can replace RATE(...) with (1+5.75%)^(1/365)-1 or 
> POWER(1+5.75%,1/365)-1, which is similar to what you have been doing.  And 
> you can replace FV(...) with 100000*(1+r)^365, where "r" is RATE(...) or one 
> of its alternatives.

Which, of course, is simply 100000*(1+5.75%).  Sigh, sometimes I get caught 
up in the moment.  I am used to people expressing nominal interest rates, not 
APYs, or asking about time frames other than one year.

That comes full circle back to your original question.  What exactly are you 
asking?

If you want to know how you would model it on a daily basis, it seems to be 
exactly the model that you used for the first scenario.  You simply have only 
the one deposit.  The daily interest factor (1 + interest rate) is 
POWER(1+5.75/100,1/365), in the manner that you were writing before.

PS:  If you are wondering why the bank bothers to say "added monthly" if 
indeed it means that interest is compounded daily, one bad experience of mine 
might shed some light on the question.  Initially, the bank refused to pay 
interest pro rated through the withdrawal date, which was (just) before the 
monthly interest payment date.
0
4/28/2007 4:38:00 AM
"Silvabod" <nos...@thank.you> wrote...
..=2E.
>Initial deposit =A3250, invest regular =A3250 per month.  Gross interest 5=
..6%,
>calculated daily, paid on the anniversary of account opening (i.e 366 days)

Meaning interest accrues as if there were daily compounding, but
isn't, er, vested until the anniversary date?

>The 5.6 is in range-named cell "APR".   Cells B11..B376 are "=3D$E10...",
>Cells $D10... are "=3D$B10...+$C10...)
>Formula in (CALC column) cells E10...E375 is
>=3DPOWER(APR/100+1,1/365)*$B10....

Meaning there are different interest rates for each day?

>Spreadsheet accuracy - exact to the penny (agrees the bank's printed
>statement).

Good luck! I spent too much of a month many years ago getting a PC-
based Pascal program to reproduce the rounding semantics of an
mainframe (MVS) FORTRAN program. This can at times require finding
different transition points for every dollar value from 0 to $10,000
(or whatever the largest amount may be) for fractional parts between
0=2E495 and 0.505. Back then I was eventually able to find the bit
pattern that always matched the mainframe transition point, but this
can be a VERY NASTY exercise.

..=2E.
>Second scenario is slightly different.  There's just ONE initial deposit,
>interest still calculated daily, but added monthly.

Again, do you mean compounded daily but only VESTED on the same day in
subsequent months?

>=A3100,000 would thus become =A3105,750 after 1 year, at 5.75% gross inter=
est
>rate, with 12 interest payments credited into the "deposit" column.

This means that 5.75% is an effective annual interest rate rather than
an APR.

>I'm using the following formula to calculate the Annual Effective Rate (AE=
R).
>Gross interest rate of 5.75 (rangename APR)
>The number "12" (in cell rangename PERIOD) =3D the number of periodic inte=
rest
>payments - so
>=3D(POWER((1+APR/100),1/PERIOD)-1)*PERIOD*100 which converts to
>=3D(POWER((1+5.75/100),1/12)-1)*12*100, evaluates to 5.60381 (which is in
>"rangenamed" cell AER).

You've got this completely backwards, at least if you have any
intention of adhering to standard Interest Theory terminology.
POWER(1+R,1/PERIOD)-1 term translates an EFFECTIVE interest rate in
terms of the original temporal basis (annual) to an EFFECTIVE interest
rate in terms of the alternative temporal base PERIOD (monthly).
Multiplying this by 12 gives a NOMINAL annual rate compounded monthly,
*NOT* an annual effective rate.

>Using a spreadsheet formatted similar to example above, the "CALC" cells
>have slightly changed formula =3DPOWER(AER/100+1,1/365)*$B10 ,(AER
>replacing APR).  BUT I cannot get a sensible result - it's known totals - =
should
>be =A3105,750.00 / =A35750.00.  Where am I going wrong ?  Is there a logic=
 fault?
..=2E.

Definitely there's a logic fault. See above. You have your definitions
backwards.

This looks like you're taking the NOMINAL annual rate compounded
monthly, erroneously converting it to a daily compounded rate, then
trying to use that rate to reproduce the effective annual interest
rate of 5.75%.

>I'm not an advanced mathematician.  Have a friend, who is, he created the
>Excel formulae from the British Banking Association;s (hugely complex)
>mathematical formulae.

Your friend became confused. Not unlikely for a mathematician reading
something written in bureaucratese by nonmathematicians.

If the APR is 5.60% (exactly), and it's nominal annual compounded
monthly, the monthly effective rate would be 0.056/12 =3D
0=2E00466666666666667 (to the limit of Excel's precision), and that
monthly rate would compound to an effective annual rate of
(1+0.00466666666666667)^12-1 =3D 0.0574599283787853 (to the limit or
Excel's precision) or 5.75% rounded to basis points.

0
hrlngrv1 (375)
4/28/2007 9:35:31 PM
First - thank you both, Harlan and Joeu2004, for your responses.

I have had limited independant success - see the worksheet at website link
http://homepage.ntlworld.com/lsdolby/EXCEL/

This was acheived before I read your posts (been away for a couple of days).

PURPOSE - I want to verify my bank statement - I'm reasonably certain 
there's a significant error on it.    I used �100,000 deposit as a 
convenience - relatively easy to determine apreadsheet accuracy!  I used 
just the one deposit, and the one interest rate, for simplicity (fact is, 
there's random deposits and varied interest rates during the year - and, 
it's ongoing (normal deposit account) not limited to just one year.

You will see (from the Sterling currency) that I'm in UK.  APR's and AER's 
are confusing, esp as the definitions seem to change between "loans" and 
"savings" accounts!  To clarify, where I've used expression APR - that's 
GROSS interest (and fully complies with the definition of interest earned ar 
5.75 gross for deposit of 1 year).
If you have downloaded the worksheet, you will see that the "classic" 
calemdar year works out exactly.  But only for a calendar year starting 1st 
Jan !!.  Reality is of course different, but the fixed rule remains, that 
interest is calculated on the last (working) day of the month - if that's a 
weekend, or another bank (i.e. national) holiday, it's calculated on the 
first working day of the following month.  (The "classic" worksheet takes no 
account of these - only "last day of month" (it is after all, a test sheet).

I appreciate that actual (banking) fomulae are complex - the "matrix 
algebra" are on the British Banking Association website page - link 
http://www.bba.org.uk/bba/jsp/polopoly.jsp?d=135&a=1575
Unfortunately, the various examples detailed do not include my bank's 
deposit account scenario!

I'm now digesting the detailed info you gave, but given your apparent 
interest (you both took time and trouble to give comprehensive responses) 
thought you'd like to see the physical efforts of a rank (maths) amateur 
(and, you might actually have a use for the ready-formatted sheet).

Thanks again to you both.  Silvabod








"Harlan Grove" <hrlngrv@gmail.com> wrote in message 
news:1177796131.811486.258100@y80g2000hsf.googlegroups.com...
"Silvabod" <nos...@thank.you> wrote...
....
>Initial deposit �250, invest regular �250 per month.  Gross interest 5.6%,
>calculated daily, paid on the anniversary of account opening (i.e 366 days)

Meaning interest accrues as if there were daily compounding, but
isn't, er, vested until the anniversary date?

>The 5.6 is in range-named cell "APR".   Cells B11..B376 are "=$E10...",
>Cells $D10... are "=$B10...+$C10...)
>Formula in (CALC column) cells E10...E375 is
>=POWER(APR/100+1,1/365)*$B10....

Meaning there are different interest rates for each day?

>Spreadsheet accuracy - exact to the penny (agrees the bank's printed
>statement).

Good luck! I spent too much of a month many years ago getting a PC-
based Pascal program to reproduce the rounding semantics of an
mainframe (MVS) FORTRAN program. This can at times require finding
different transition points for every dollar value from 0 to $10,000
(or whatever the largest amount may be) for fractional parts between
0.495 and 0.505. Back then I was eventually able to find the bit
pattern that always matched the mainframe transition point, but this
can be a VERY NASTY exercise.

....
>Second scenario is slightly different.  There's just ONE initial deposit,
>interest still calculated daily, but added monthly.

Again, do you mean compounded daily but only VESTED on the same day in
subsequent months?

>�100,000 would thus become �105,750 after 1 year, at 5.75% gross interest
>rate, with 12 interest payments credited into the "deposit" column.

This means that 5.75% is an effective annual interest rate rather than
an APR.

>I'm using the following formula to calculate the Annual Effective Rate 
>(AER).
>Gross interest rate of 5.75 (rangename APR)
>The number "12" (in cell rangename PERIOD) = the number of periodic 
>interest
>payments - so
>=(POWER((1+APR/100),1/PERIOD)-1)*PERIOD*100 which converts to
>=(POWER((1+5.75/100),1/12)-1)*12*100, evaluates to 5.60381 (which is in
>"rangenamed" cell AER).

You've got this completely backwards, at least if you have any
intention of adhering to standard Interest Theory terminology.
POWER(1+R,1/PERIOD)-1 term translates an EFFECTIVE interest rate in
terms of the original temporal basis (annual) to an EFFECTIVE interest
rate in terms of the alternative temporal base PERIOD (monthly).
Multiplying this by 12 gives a NOMINAL annual rate compounded monthly,
*NOT* an annual effective rate.

>Using a spreadsheet formatted similar to example above, the "CALC" cells
>have slightly changed formula =POWER(AER/100+1,1/365)*$B10 ,(AER
>replacing APR).  BUT I cannot get a sensible result - it's known totals - 
>should
>be �105,750.00 / �5750.00.  Where am I going wrong ?  Is there a logic 
>fault?
....

Definitely there's a logic fault. See above. You have your definitions
backwards.

This looks like you're taking the NOMINAL annual rate compounded
monthly, erroneously converting it to a daily compounded rate, then
trying to use that rate to reproduce the effective annual interest
rate of 5.75%.

>I'm not an advanced mathematician.  Have a friend, who is, he created the
>Excel formulae from the British Banking Association;s (hugely complex)
>mathematical formulae.

Your friend became confused. Not unlikely for a mathematician reading
something written in bureaucratese by nonmathematicians.

If the APR is 5.60% (exactly), and it's nominal annual compounded
monthly, the monthly effective rate would be 0.056/12 =
0.00466666666666667 (to the limit of Excel's precision), and that
monthly rate would compound to an effective annual rate of
(1+0.00466666666666667)^12-1 = 0.0574599283787853 (to the limit or
Excel's precision) or 5.75% rounded to basis points.


0
nospam5510 (78)
4/30/2007 2:14:50 PM
joeu2004, please see response to Harlan Grove which is dual addressed to 
include you
Thanks, Silvabod

"joeu2004@hotmail.com" <joeu2004hotmailcom@discussions.microsoft.com> wrote 
in message news:46044C9E-D024-4157-8BF5-E1AAC82B0C34@microsoft.com...
> [Possible reposting.  Google Groups seems to be having trouble again :-(.]
>
> On Apr 25, 8:27 am, "Silvabod" <nos...@thank.you> wrote:
>> Gross interest 5.6%
>> [....]
>> =POWER(APR/100+1,1/365)*$B10
>
> I am not familiar with the terminology in your region.  Here, you are 
> using
> "gross interest" as if it were an "annual effective rate" -- that is, a
> compounded rate.  But....
>
>> at 5.75% gross interest rate, with 12 interest payments credited
>> into the "deposit" column.
>> [....]
>> =(POWER((1+5.75/100),1/12)-1)*12*100, evaluates to 5.60381
>> (which is in "rangenamed" cell AER).
>> [....] =POWER(AER/100+1,1/365)*$B10
>> [....] BUT I cannot get a sensible result
>
> Here, you are using 5.60381 as if it were an "annual effective rate", but
> you are computing it as a nominal rate -- that is, a simple (not 
> compounded)
> rate.  That is probably why your result is not "sensible".
>
>> There's just ONE initial deposit,
>> interest still calculated daily, but added monthly.
>
> The terminology is ambiguous (the banking industry's fault, not yours). 
> You
> might reasonably think this means that simple interest is computed each 
> month
> and compounded monthly.  That is indeed one methodology that (US) banks 
> use.
> I was going to suggest a model for that computation.  But....
>
>> �100,000 would thus become �105,750 after 1 year
>
> Based on that expectation, it appears that the bank simply means that
> interest is compounded daily at an "annual effective rate" of 5.75% -- the
> same methodology used in your first scenario.  This can be computed a 
> number
> of equivalent ways.  Arguably, the simplest is:
>
> =fv(rate(365,0,-1,1+5.75%), 365, 0, -100000)
>
> You can replace RATE(...) with (1+5.75%)^(1/365)-1 or
> POWER(1+5.75%,1/365)-1, which is similar to what you have been doing.  And
> you can replace FV(...) with 100000*(1+r)^365, where "r" is RATE(...) or 
> one
> of its alternatives.
>
> HTH.
>
>
> --- original posting (complete) ---
>
> On Apr 25, 8:27 am, "Silvabod" <nos...@thank.you> wrote:
>> This worksheet works, for one scenario.  Need help, please, for the 
>> second
>> scenario
>>
>> Initial deposit �250, invest regular �250 per month.  Gross interest 
>> 5.6%,
>> calculated daily, paid on the anniversary of account opening (i.e 366 
>> days)
>>
>> The 5.6 is in range-named cell "APR".   Cells B11..B376 are "=$E10...",
>> Cells $D10... are "=$B10...+$C10...)
>> Formula in (CALC column) cells E10...E375 is
>> =POWER(APR/100+1,1/365)*$B10....
>> Spreadsheet accuracy - exact to the penny (agrees the bank's printed
>> statement).
>>
>> 06       A        B                 C                D                 E
>> F
>> 07    day      daily            deposit        subtotal        CALC
>> daily
>> 08      #        total
>> interest
>> 09
>> 10      0            0.00        250.00    250.00000  250.03732   0.03732
>> 11      1        250.04                       250.03732  250.07465 
>> 0.03733
>> 12      2        250.07                       250.03733  250.07250 
>> 0.03733
>> continue to row 380 (366 days)
>> with 12 monthly deposits of �250
>>         day      daily            deposit
>> daily int
>>         #          total              total
>> total
>> 376   366   3342.22        3250.00
>> 92.22
>>
>> Second scenario is slightly different.  There's just ONE initial deposit,
>> interest still calculated daily, but added monthly.
>> �100,000 would thus become �105,750 after 1 year, at 5.75% gross interest
>> rate, with 12 interest payments credited into the "deposit" column.
>>
>> I'm using the following formula to calculate the Annual Effective Rate
>> (AER).
>> Gross interest rate of 5.75 (rangename APR)
>> The number "12" (in cell rangename PERIOD) = the number of periodic 
>> interest
>> payments - so
>> =(POWER((1+APR/100),1/PERIOD)-1)*PERIOD*100 which converts to
>> =(POWER((1+5.75/100),1/12)-1)*12*100, evaluates to 5.60381 (which is in
>> "rangenamed" cell AER).
>> Using a spreadsheet formatted similar to example above, the "CALC" cells
>> have slightly changed formula =POWER(AER/100+1,1/365)*$B10 ,(AER 
>> replacing
>> APR).  BUT I cannot get a sensible result - it's known totals - should be
>> �105,750.00 / �5750.00.  Where am I going wrong ?  Is there a logic 
>> fault?
>> (I did take out what would have been duplication of the 12 monthly 
>> interest
>> additions, in the running total column).
>>
>> I'm not an advanced mathematician.  Have a friend, who is, he created the
>> Excel formulae from the British Banking Association;s (hugely complex)
>> mathematical formulae.
>>
>> HELP ! please?
>
> 


0
nospam5510 (78)
4/30/2007 2:15:51 PM
Supplemental to courtesy reply, I've now assimilated your two detailed 
responses.

The FV function by definition is of no use in my "problem" scenario, it's 
specific to a "one deposit" bond or similar security, which I clarified was 
not the case in later posting 26 Apr, 17.37 to "macropod", (see also the 
reply to both yourself and Harlan, today 30 Apr, 15.15)

SNIP - "If you want to know how you would model it on a daily basis, it 
seems to be
exactly the model that you used for the first scenario.  You simply have 
only
the one deposit.  The daily interest factor (1 + interest rate) is
POWER(1+5.75/100,1/365), in the manner that you were writing before" - END 
SNIP

which is identical to my existing formula (range-named cell APR contains 
value 5.75)

Re your PS - (also covered in in joint response a couple of hours ago) , I 
am also unhappy with my bank statement.  Purpose of the exercise is to 
create a dynamic worksheet to prove my suspicion.

Silvabod.
"joeu2004@hotmail.com" <joeu2004hotmailcom@discussions.microsoft.com> wrote 
in message news:2F19E70A-798F-4798-AAE6-D4F9320F79E5@microsoft.com...
>I wrote:
>> Arguably, the simplest is:
>> =fv(rate(365,0,-1,1+5.75%), 365, 0, -100000)
>>
>> You can replace RATE(...) with (1+5.75%)^(1/365)-1 or
>> POWER(1+5.75%,1/365)-1, which is similar to what you have been doing. 
>> And
>> you can replace FV(...) with 100000*(1+r)^365, where "r" is RATE(...) or 
>> one
>> of its alternatives.
>
> Which, of course, is simply 100000*(1+5.75%).  Sigh, sometimes I get 
> caught
> up in the moment.  I am used to people expressing nominal interest rates, 
> not
> APYs, or asking about time frames other than one year.
>
> That comes full circle back to your original question.  What exactly are 
> you
> asking?
>
> If you want to know how you would model it on a daily basis, it seems to 
> be
> exactly the model that you used for the first scenario.  You simply have 
> only
> the one deposit.  The daily interest factor (1 + interest rate) is
> POWER(1+5.75/100,1/365), in the manner that you were writing before.
>
> PS:  If you are wondering why the bank bothers to say "added monthly" if
> indeed it means that interest is compounded daily, one bad experience of 
> mine
> might shed some light on the question.  Initially, the bank refused to pay
> interest pro rated through the withdrawal date, which was (just) before 
> the
> monthly interest payment date. 


0
nospam5510 (78)
4/30/2007 3:14:05 PM
Harlan, I've now had time to assimilate your input.  There's some comments 
within your original text, and a final para.
Silvabod

"Harlan Grove" <hrlngrv@gmail.com> wrote in message 
news:1177796131.811486.258100@y80g2000hsf.googlegroups.com...
"Silvabod" <nos...@thank.you> wrote...
....
>Initial deposit �250, invest regular �250 per month.  Gross interest 5.6%,
>calculated daily, paid on the anniversary of account opening (i.e 366 days)

Meaning interest accrues as if there were daily compounding, but
isn't, er, vested until the anniversary date?  CORRECT

>The 5.6 is in range-named cell "APR".   Cells B11..B376 are "=$E10...",
>Cells $D10... are "=$B10...+$C10...)
>Formula in (CALC column) cells E10...E375 is
>=POWER(APR/100+1,1/365)*$B10....

Meaning there are different interest rates for each day? >> SORRY - TYPO - 
the APR (change that to AER) is 5.75% not 5.6.  BUT - this isn't the problem 
worksheet - it's the one that DOES work (different "rules" - this one's an 
initial deposit, regular monthly deposit, interest calculated daily credited 
just once, on the anniversary date one year later).  THIS worksheet is 100% 
accurate, agrees exactly with the Bank's statement.

>Spreadsheet accuracy - exact to the penny (agrees the bank's printed
>statement).

Good luck! I spent too much of a month many years ago getting a PC-
based Pascal program to reproduce the rounding semantics of an
mainframe (MVS) FORTRAN program. This can at times require finding
different transition points for every dollar value from 0 to $10,000
(or whatever the largest amount may be) for fractional parts between
0.495 and 0.505. Back then I was eventually able to find the bit
pattern that always matched the mainframe transition point, but this
can be a VERY NASTY exercise.

YOU ARE way advanced beyond me. I have no mainframe or PASCAL experience, 
nor access to either!  I'm hoping for an Excel solution ....
....
>Second scenario is slightly different.  There's just ONE initial deposit,
>interest still calculated daily, but added monthly.

Again, do you mean compounded daily but only VESTED on the same day in
subsequent months?  >>  DOES VESTED = CREDITED ?  Yes (see worksheet if 
not - that's how it's done - but also see explanation of "added monthly". 
Not only is the # days per month a variable, but also the DAY of the month - 
if it falls on a non-banking day (weekend or national/bank holiday.

>�100,000 would thus become �105,750 after 1 year, at 5.75% gross interest
>rate, with 12 interest payments credited into the "deposit" column.

This means that 5.75% is an effective annual interest rate rather than
an APR.

>I'm using the following formula to calculate the Annual Effective Rate 
>(AER).
>Gross interest rate of 5.75 (rangename APR)
>The number "12" (in cell rangename PERIOD) = the number of periodic 
>interest
>payments - so
>=(POWER((1+APR/100),1/PERIOD)-1)*PERIOD*100 which converts to
>=(POWER((1+5.75/100),1/12)-1)*12*100, evaluates to 5.60381 (which is in
>"rangenamed" cell AER).

You've got this completely backwards, at least if you have any
intention of adhering to standard Interest Theory terminology.
POWER(1+R,1/PERIOD)-1 term translates an EFFECTIVE interest rate in
terms of the original temporal basis (annual) to an EFFECTIVE interest
rate in terms of the alternative temporal base PERIOD (monthly).
Multiplying this by 12 gives a NOMINAL annual rate compounded monthly,
*NOT* an annual effective rate.

>Using a spreadsheet formatted similar to example above, the "CALC" cells
>have slightly changed formula =POWER(AER/100+1,1/365)*$B10 ,(AER
>replacing APR).  BUT I cannot get a sensible result - it's known totals - 
>should
>be �105,750.00 / �5750.00.  Where am I going wrong ?  Is there a logic 
>fault?
....

Definitely there's a logic fault. See above. You have your definitions
backwards.

This looks like you're taking the NOMINAL annual rate compounded
monthly, erroneously converting it to a daily compounded rate, then
trying to use that rate to reproduce the effective annual interest
rate of 5.75%.

>I'm not an advanced mathematician.  Have a friend, who is, he created the
>Excel formulae from the British Banking Association;s (hugely complex)
>mathematical formulae.

Your friend became confused. Not unlikely for a mathematician reading
something written in bureaucratese by nonmathematicians.

If the APR is 5.60% (exactly), and it's nominal annual compounded
monthly, the monthly effective rate would be 0.056/12 =
0.00466666666666667 (to the limit of Excel's precision), and that
monthly rate would compound to an effective annual rate of
(1+0.00466666666666667)^12-1 = 0.0574599283787853 (to the limit or
Excel's precision) or 5.75% rounded to basis points.

YOU ARE absolutely correct - I am comfused by banking terms in UK there's 
three - APR, AER and EAR and GROSS Interest.  Further confusion - the 
application of these terms seem to differ, dependant on whether it's a 
loan/mortgage, or a deposit account.

Since we are discussing deposit accounts, the "headline" rate is the AER - 
�100 deposited for a year at 5.65% AER yields �105.65
5.65% EAR = 5.50876% Gross (to 5 places) but using "gross" in my spreadsheet 
gives a massive error, so unless there's a logic error somewhere, I have to 
stick to AER (I'll change APR to AER in spreadsgeet) in the calculated 
column, as giving "best" but still unacceptable, accuracy.


0
nospam5510 (78)
4/30/2007 4:25:08 PM
On Apr 30, 6:14 am, "Silvabod" <nos...@thank.you> wrote:
> You will see (from the Sterling currency) that I'm in UK.  APR's and AER's
> are confusing, esp as the definitions seem to change between "loans" and
> "savings" accounts!

The terms are equally confused and misused in the States.  (In the
States, we use the term APY instead of AER, which makes things even
more confusing, IMHO.)

> To clarify, where I've used expression APR - that's GROSS interest

Okay.  That is also often called the "nominal" interest rate.

> I appreciate that actual (banking) fomulae are complex - the "matrix
> algebra" are on the British Banking Association website page - link
> http://www.bba.org.uk/bba/jsp/polopoly.jsp?d=3D135&a=3D1575

The key thing for you to understand on that web page is the
relationship between the "gross interest rate" and the AER, which is
explained very clearly by examples under the title "Explanation of the
AER".

Previously, you wrote:
> Second scenario is slightly different.  There's just ONE initial deposit,
> interest still calculated daily, but added monthly.
> =A3100,000 would thus become =A3105,750 after 1 year, at 5.75% gross
> interest rate, with 12 interest payments credited into the "deposit" colu=
mn.

Who says 100,000 would grow to 105,750 after 1 year in that case?

That is inconsistent with the examples on the BBA web page, which I
believe are correct.  In your example, if we assume that interest is
compounded monthly, the monthly interest rate is 5.75%/12, and the
final balance is 105,903.98.  The final balance can be calculated by
either of the following equivalent formulas:

=3Dfv(5.75%/12, 12, 0, -100000)

=3D100000*(1+5.75%/12)^12

Compare with the BBA example of  5% gross interest rate paid
quarterly.

Thus, the AER is approximately 5.90398%.  This can be calculated
exactly (within the limits of binary computers) by either of the
following equivalent formulas:

=3Dfv(5.75%/12, 12, 0, -1) - 1

=3D(1+5.75%/12)^12 - 1

The latter is consistent with the formula in section "(d)" near the
top of the web page.

The only issue is what is meant exactly by the phrase "calculated
daily, but added monthly".  As I noted previously in this thread, that
phrase is used ambiguously in the States.

Nonetheless, once you know the AER, it really does not matter.

> Reality is of course different, but the fixed rule remains, that
> interest is calculated on the last (working) day of the month - if that's=
 a
> weekend, or another bank (i.e. national) holiday, it's calculated on the
> first working day of the following month.  (The "classic" worksheet takes=
 no
> account of these - only "last day of month"

As noted in the BBA web page, the AER is merely a "notional rate" -- I
prefer the term "conceptual rate".  Once you know the AER, you can
compute a "conceptual" compounded daily rate and, ergo, compute the
actual interest earned in a period of any number of days.  The
compounded daily rate can be computed by either of the following
equivalent formulas:

=3Drate(365, 0, -1, 1+r)

=3D(1+r)^(1/365) -1

where "r" is one of the formulas for AER above.

Thus, for a period between the dates D1 and D2 (that is, D1 is the
date of the end of the previous period, and D2 is the date of the end
of the current period), the balance at the end of the current period
can be computed by either of the following equivalent formulas:

=3Dfv(d, D2-D1, 0, -b)

=3Db*(1+d)^(D2-D1)

where "d" is the compounded daily rate and "b" is the balance at the
end of the previous period.

Having said all that, I hasten to point out that we do __not__ truly
"know" the AER for your example.  (Sigh.)

I ass-u-me-d that "added monthly" means "paid monthly", which should
mean "compounded monthly" according to the BBA web page.  And I
suspect that is the intended meaning in your case.

But in the States, "calculated daily" can also mean "compounded
daily", even in the context of "added monthly".  Klunk!  In that case,
the AER would be
(1+5.75%/365)^365 - 1 for non-leap years.  That is approximately
5=2E91805%.

(In the States, financial institutions have the option of using 366 in
place of 365 for leap years.)

You might play with both interpretations to see which more closely
matches your bank statements.

And if it truly is compounded daily (notwithstanding "added monthly"),
I doubt that you would see any difference in any one period in your
example if you used a daily rate of 5.75%/365 instead of the
compounded daily rate.  I compute a difference of 0.000011% in the
final balance for the entire year -- less than 0.02.

> I have had limited independant success - see the worksheet at website link
> http://homepage.ntlworld.com/lsdolby/EXCEL/

I did not look at your spreadsheet.  But hopefully the above is
helpful.



0
joeu2004 (766)
4/30/2007 6:48:37 PM
Addressing just ONE point at this time.  Slightly changing my terminology 
for clarity, to fall in line with what is now accepted practice, I said that 
�100,000 in a deposit account @ 5.75% GROSS interest yields �105,750 - which 
you query.  I believe it to be correct, because British Banking Code of 
Practice requires UK banks to publish both Gross interest and EAR (Effective 
Annual Rate) to 2 decimal places.  It appears that banks' GROSS interest 
figures are the true "2 decimal" figures for Deposit accounts, the EAR is an 
approximation calculated from Gross.

I had a second project in mind, piggy-backing on the current one (for 
ultimate use within it) - to create a table of EAR's - to which end, the 
formula
=(POWER((1+GROSS/100),1/PERIOD)-1)*PERIOD*100 gives EAR,
where GROSS = gross interest, PERIOD = # times per year it is paid.  Have 
verified it against various banks' differing published rates.

"Terminology"  problem with "credited" and "compounded" - is perhaps best 
clarified by sight of my worksheet.  It's conditionally formatted to display 
exactly what would appear on a bank statement (displays entries only on 
dates which have activity, i.e the monthly interest credits). My belief is 
that it's calculated daily, credited monthly - the "compounding" (interest 
on interest) being daily.  I'm open to correction!

I'm printing off your response, to attempt validation of a 4 month period of 
my actual bank statement, using your alternate suggestions (4 months is a 
period with no activity except monthly interest credits, and a static 
interest rate).  Comparison versus actual should be interesting, show I'm on 
the right lines!

Also have a second, totally unrelated problem, which may take me off-line 
for a couple of days.  My PC has a corrupted Adobe Acrobat reader prog, 
which refuses to either work, uninstall or load up the latest version.  May 
have to totally reformat HDD and re-install XP, all applications and data 
(fortunately I back up regularly).  On past experience, if that's 
necessary - at least 2 days work! (Many of the website docs I need are pdf 
files).

Thanks for your continuing interest.   Silvabod.

"joeu2004" <joeu2004@hotmail.com> wrote in message 
news:1177958917.851664.276950@l77g2000hsb.googlegroups.com...
On Apr 30, 6:14 am, "Silvabod" <nos...@thank.you> wrote:
> You will see (from the Sterling currency) that I'm in UK.  APR's and AER's
> are confusing, esp as the definitions seem to change between "loans" and
> "savings" accounts!

The terms are equally confused and misused in the States.  (In the
States, we use the term APY instead of AER, which makes things even
more confusing, IMHO.)

> To clarify, where I've used expression APR - that's GROSS interest

Okay.  That is also often called the "nominal" interest rate.

> I appreciate that actual (banking) fomulae are complex - the "matrix
> algebra" are on the British Banking Association website page - link
> http://www.bba.org.uk/bba/jsp/polopoly.jsp?d=135&a=1575

The key thing for you to understand on that web page is the
relationship between the "gross interest rate" and the AER, which is
explained very clearly by examples under the title "Explanation of the
AER".

Previously, you wrote:
> Second scenario is slightly different.  There's just ONE initial deposit,
> interest still calculated daily, but added monthly.
> �100,000 would thus become �105,750 after 1 year, at 5.75% gross
> interest rate, with 12 interest payments credited into the "deposit" 
> column.

Who says 100,000 would grow to 105,750 after 1 year in that case?

That is inconsistent with the examples on the BBA web page, which I
believe are correct.  In your example, if we assume that interest is
compounded monthly, the monthly interest rate is 5.75%/12, and the
final balance is 105,903.98.  The final balance can be calculated by
either of the following equivalent formulas:

=fv(5.75%/12, 12, 0, -100000)

=100000*(1+5.75%/12)^12

Compare with the BBA example of  5% gross interest rate paid
quarterly.

Thus, the AER is approximately 5.90398%.  This can be calculated
exactly (within the limits of binary computers) by either of the
following equivalent formulas:

=fv(5.75%/12, 12, 0, -1) - 1

=(1+5.75%/12)^12 - 1

The latter is consistent with the formula in section "(d)" near the
top of the web page.

The only issue is what is meant exactly by the phrase "calculated
daily, but added monthly".  As I noted previously in this thread, that
phrase is used ambiguously in the States.

Nonetheless, once you know the AER, it really does not matter.

> Reality is of course different, but the fixed rule remains, that
> interest is calculated on the last (working) day of the month - if that's 
> a
> weekend, or another bank (i.e. national) holiday, it's calculated on the
> first working day of the following month.  (The "classic" worksheet takes 
> no
> account of these - only "last day of month"

As noted in the BBA web page, the AER is merely a "notional rate" -- I
prefer the term "conceptual rate".  Once you know the AER, you can
compute a "conceptual" compounded daily rate and, ergo, compute the
actual interest earned in a period of any number of days.  The
compounded daily rate can be computed by either of the following
equivalent formulas:

=rate(365, 0, -1, 1+r)

=(1+r)^(1/365) -1

where "r" is one of the formulas for AER above.

Thus, for a period between the dates D1 and D2 (that is, D1 is the
date of the end of the previous period, and D2 is the date of the end
of the current period), the balance at the end of the current period
can be computed by either of the following equivalent formulas:

=fv(d, D2-D1, 0, -b)

=b*(1+d)^(D2-D1)

where "d" is the compounded daily rate and "b" is the balance at the
end of the previous period.

Having said all that, I hasten to point out that we do __not__ truly
"know" the AER for your example.  (Sigh.)

I ass-u-me-d that "added monthly" means "paid monthly", which should
mean "compounded monthly" according to the BBA web page.  And I
suspect that is the intended meaning in your case.

But in the States, "calculated daily" can also mean "compounded
daily", even in the context of "added monthly".  Klunk!  In that case,
the AER would be
(1+5.75%/365)^365 - 1 for non-leap years.  That is approximately
5.91805%.

(In the States, financial institutions have the option of using 366 in
place of 365 for leap years.)

You might play with both interpretations to see which more closely
matches your bank statements.

And if it truly is compounded daily (notwithstanding "added monthly"),
I doubt that you would see any difference in any one period in your
example if you used a daily rate of 5.75%/365 instead of the
compounded daily rate.  I compute a difference of 0.000011% in the
final balance for the entire year -- less than 0.02.

> I have had limited independant success - see the worksheet at website link
> http://homepage.ntlworld.com/lsdolby/EXCEL/

I did not look at your spreadsheet.  But hopefully the above is
helpful.




0
nospam5510 (78)
5/1/2007 11:46:29 AM
["Reposting" -- rewritten!  It appears that Google Groups screwed up
again.  Argh!]

On May 1, 3:46 am, "Silvabod" <nos...@thank.you> wrote:
> Addressing just ONE point at this time.  Slightly changing my terminology
> for clarity, to fall in line with what is now accepted practice

I am not knowledgable about UK banking practices and regulations per
se, so I cannot speak dispositively about terminology.  However....

> British Banking Code of Practice requires UK banks [....]

According to the BBA:   "The Banking and Business Banking Codes are
voluntary codes setting standards for good banking practice for banks
and building societies in the UK."  See http://www.bba.org.uk/bba/jsp/polop=
oly.jsp?d=3D140
..

The BBA is a [the official?] publisher of the BBBC.  So I will assume
that their terminology and explanations are dispositive, unless and
until an expert in UK banking practices proves they are wrong.

However, since the BBBC is voluntary, it is possible that __your__
bank uses different terminology or uses the terminology differently.

> British Banking Code of
> Practice requires UK banks to publish both Gross interest and EAR (Effect=
ive
> Annual Rate) to 2 decimal places.  It appears that banks' GROSS interest
> figures are the true "2 decimal" figures for Deposit accounts, the EAR is=
 an
> approximation calculated from Gross.

According to the BBA at http://www.bba.org.uk/bba/jsp/polopoly.jsp?d=3D135&=
a=3D261
:

"Where interest rates are advertised, these must be described as
follows:  a. W% gross [...] and d. Z% Annual Equivalent Rate ('AER')
[=2E...] The AER is a truly reflective rate, taking into account the
frequency with which the product has interest paid or compounded.
[=2E...] Advertisements which quote a rate of interest must quote the
Annual Equivalent Rate and the contractual rate."

(The gross rate is defined to be the contractual rate.)

The BBBC uses the term AER, not EAR.  I find no mention of "Effective
Annual Rate" or "EAR" on the BBA web pages.  But in common
terminology, the term "effective annual rate" matches the AER as
defined by the BBBC according to the BBA.  See
http://en.wikipedia.org/wiki/Effective_interest_rate , which of course
is not dispositive.

> the formula
> =3D(POWER((1+GROSS/100),1/PERIOD)-1)*PERIOD*100 gives EAR,
> where GROSS =3D gross interest, PERIOD =3D # times per year it is paid

I doubt it.  It is certainly not true for AER.  AER is computed by:

=3Dpower(1 + GROSS/100/PERIOD, PERIOD) - 1

On the other hand, GROSS can be computed by:

=3D(power(1 + AER/100, 1/PERIOD) - 1) * PERIOD * 100

So perhaps you are merely confusing gross and AER.

> I said that =A3100,000 in a deposit account @ 5.75% GROSS interest yields
> =A3105,750 - which you query.  I believe it to be correct

That is correct only if interest is paid and compounded once a year.

The BBA explains on http://www.bba.org.uk/bba/jsp/polopoly.jsp?d=3D135&a=3D=
1575
:

   If an account pays or credits interest once a year, then the AER
   is equal to the gross rate.

   If an account pays interest more often than once a year, then the
   AER is calculated by adding each interest payment to the deposit
   and calculating the next interest payment on the total -
compounding
   the interest.

   For example, an account offering 5% gross interest paid quarterly
on
   =A3100 pays

      =A31.25 (1.25% (=BC of 5%) of =A3100) after 3 months,
      =A31.26 (1.25% of =A3101.25 (=A3100 + =A31.25)) after six months*,
      =A31.28 (1.25% of =A3102.51) after nine months, and
      =A31.30 at the end of the year (1.25% of =A3103.79),
      giving a total including interest of =A3105.09.

      The AER is thus 5.09%.

      *In practice, the calculation is worked to more decimal places
to
       avoid rounding errors.

Thus, AER is the compounded gross rate per period.  The final balance
in the example can be compounded by:

=3D100 * power(1 + 5/100/4, 4)

which I write equivalently as 100*(1+5%/4)^4.  Note that the first
"100" is the initial balance, having nothing to do with percentage
conversion.

Likewise, for 100,000 at 5.75%, the final balance can be computed by
100000*(1+5.75%/n)^n, where "n" is 12 or 365, depending on the
compounding frequency (monthly or daily, respectively).

> "Terminology"  problem with "credited" and "compounded"
> [....] My belief is that it's calculated daily, credited monthly - the
> "compounding" (interest on interest) being daily.  I'm open to correction!

As I explained in a previous posting, that is very possible.  I do not
believe the BBA explanations offer any clarity.  From the quotes
above, it might appear that "pay" and "credit" are synonymous with
"compounding".  But on the same web page, the BBA also writes:

   The Annual Equivalent Rate is a notional rate quoted in
   advertisements for interest bearing accounts which illustrates the
   contractual (gross) interest rate [...] as if paid and compounded
   on an annual basis.

Arguably, "paid and compounded" seem to be distinct.  On the other
hand, it could just be purposeful redundancy to add clarity.

> I'm printing off your response, to attempt validation of a 4 month period=
 of
> my actual bank statement, using your alternate suggestions (4 months is a
> period with no activity except monthly interest credits, and a static
> interest rate).  Comparison versus actual should be interesting

Absolutely the right thing to do.  And you might consider a completely
different methology, which is common in the States for savings
accounts.

__Simple__ interest is computed on a daily basis or on the average
daily balance for the period (typically a month).  Interest is
credited to the account each period.  Thus, interest is compounded
each period, not daily.  The daily interest rate would be GROSS/365.
(The BBBC permits either 365 or 366 to be used in leap years.)

When the average daily balance is used, the interest for the period
can be computed by:

=3Da*(d2 - d1)*GROSS/100/365)

where "a" is the average daily balance for the current period, "d1" is
the date when interest was credited in the previous period, and "d2"
is the date when interest is credited in the current period.

The advertised AER can be computed by the following array formula (use
ctrl-shift-Enter):

=3D(product(1 + {31,28,31,30,31,30,31,31,30,31,30,31}*GROSS/100/365) -
1) * 100

The actual AER, which might appear on each periodic bank statement,
depends on the daily balance or average daily balance as well as the
actual days between crediting interest to the account, taking bank-
closed days into account.

HTH.

0
joeu2004 (766)
5/1/2007 7:25:30 PM
Minor errata....

On May 1, 11:25 am, joeu2004 <joeu2...@hotmail.com> wrote:
> AER is computed by:
> =power(1 + GROSS/100/PERIOD, PERIOD) - 1

I believe that should be:

=(power(1 + GROSS/100/PERIOD, PERIOD) - 1) * 100

I am not used to writing percentages 5.75/100.  I write simply 5.75%,
which avoids multiplying and dividing by 100.

> When the average daily balance is used, the interest for the period
> can be computed by:
> =a*(d2 - d1)*GROSS/100/365)

Of course, the last parenthesis is a typo.  It should be:

 =a*(d2 - d1)*GROSS/100/365

0
joeu2004 (766)
5/1/2007 7:32:57 PM
On May 1, 3:46 am, "Silvabod" <nos...@thank.you> wrote:
> I said that =A3100,000 in a deposit account @ 5.75% GROSS
> interest yields =A3105,750 - which you query.  I believe it to be correct
> [....]
> the formula
> =3D(POWER((1+GROSS/100),1/PERIOD)-1)*PERIOD*100
> gives EAR, where GROSS =3D gross interest, PERIOD =3D # times
> per year it is paid.  Have verified it against various banks' differing
> published rates.

Forgive my incessant postings, but let me try to simplify all of this.

__If__ 100,000 at 5.75% gross to 105,750 in one year,....

And __if__ POWER(1+rate1,1/period)*period gets you rate2, where
"rate1" and "rate2" are the bank's published rates,....

Then you simply have the terminology __backwards__:  "rate1" (5.75%)
is the AER (or "EAR"), perhaps only approximately, and "rate2" is the
gross rate.

That is one of the points that Harlan made, but it seems to have been
lost on you.  Hopefully, all the information I provided previously
convinces you of that fact.  If not, you just have to accept it on
faith.

Therefore:

(1) __If__ the deposit is compounded monthly or daily,....

     Then given the AER, the monthly and daily rates are computed as
follows, using your notation:

     monthly rate:  =3D(power(1 + AER/100, 1/12) - 1) * 100

     daily rate:      =3D(power(1 + AER/100, 1/365) - 1) * 100

     Caveat:  Because the published AER is only an approximation, the
monthly and daily rates are also inexact.

(2) __If__ the deposit earns simple interest on the daily or average
daily
     balance, which is then compounded monthly,....

     Then it is difficult to compute the daily rate from the AER.

     You need to find the daily rate "r" such that the following
equals the AER (where "r" is expressed like 5.75, as you prefer,
instead of 5.75%):

     =3D(product(1 + {31,28,31,30,31,30,31,31,30,31,30,31}*r/100/365) -
1) * 100

     That is an array formula, which must be commited using ctrl-shift-
Enter.  You could use Excel Solver to find "r".

     Caveat:  But again, since the published AER is only an
approximation, the formula above might not agree with the published
gross rate (divided by 365).

A word about accuracy....

You asserted:
> British Banking Code of  Practice requires UK banks to publish both
> Gross interest and EAR (Effective Annual Rate) to 2 decimal places.

I will have to take your word for it.  I cannot find that requirement
in the part of the BBBC posted on the BBA web site.  In any case, that
does not necessarily preclude the bank's using greater precision
internally -- unless you can point to regulation to that effect.

By analogy, the corresponding US regulation states:

     The annual percentage yield, the annual percentage yield
     earned, and the interest rate shall be rounded to the nearest
     one-hundredth of one percentage point (.01%) and expressed
     to two decimal places.  For account disclosures, the interest
     rate may be expressed to more than two decimal places.

Parsing that carefully, it implies that the internal rate, which might
be specified on the account disclosure, might have higher precision
than how it is specified on other disclosures.

Moreover, US regulation states:

     The annual percentage yield (and the annual percentage yield
     earned) will be considered accurate if not more that one-
twentieth
     of one percentage point (.05%) above or below the annual
     percentage yield (and the annual percentage yield earned)
     determined in accordance with the rules in appendix A of this
part
     [which specifies the algorithms for calculating the APY].

The point is:  if the BBBC permits similar latitude, even if the
published AER and gross rate are 5.75% and 5.6%, there are a lot of
reasons why you might not be able to derive those exact numbers by
mathematically-correct conversion formulas, even if you use a number
between 5.745% and 5.7549...9% for the AER.  The gross rate might be a
number between 5.595% and 5.6049...9%.  The final balance of "105,750"
might be a number between 105,749.50 and 105.750.49.

> I'm printing off your response, to attempt validation of a 4 month period=
 of
> my actual bank statement, using your alternate suggestions (4 months is a
> period with no activity except monthly interest credits, and a static
> interest rate).  Comparison versus actual should be interesting, show I'm=
 on
> the right lines!

When you try to apply these concepts to your bank statement, you need
be very flexible.

0
joeu2004 (766)
5/2/2007 4:47:22 AM
Joeu, apologies for the long delay in response.  Have had more than a few 
major problems, still ongoing (one of which was pc-related).  Unfortunately 
they are ongoing, so I don't yet have the time or freedom to fully 
appreciate and employ the enormous wealth of information you have provided.

I'm also posting this separately just in case you no longer have this 
highlighted as ongoing - would be discourteous not to acknowledge your 
effort on my behalf!.

I do appreciate the time and effort you expended - it's definately not 
wasted!

"joeu2004" <joeu2004@hotmail.com> wrote in message 
news:1178081242.744369.135430@e65g2000hsc.googlegroups.com...
On May 1, 3:46 am, "Silvabod" <nos...@thank.you> wrote:
> I said that �100,000 in a deposit account @ 5.75% GROSS
> interest yields �105,750 - which you query.  I believe it to be correct
> [....]
> the formula
> =(POWER((1+GROSS/100),1/PERIOD)-1)*PERIOD*100
> gives EAR, where GROSS = gross interest, PERIOD = # times
> per year it is paid.  Have verified it against various banks' differing
> published rates.

Forgive my incessant postings, but let me try to simplify all of this.

__If__ 100,000 at 5.75% gross to 105,750 in one year,....

And __if__ POWER(1+rate1,1/period)*period gets you rate2, where
"rate1" and "rate2" are the bank's published rates,....

Then you simply have the terminology __backwards__:  "rate1" (5.75%)
is the AER (or "EAR"), perhaps only approximately, and "rate2" is the
gross rate.

That is one of the points that Harlan made, but it seems to have been
lost on you.  Hopefully, all the information I provided previously
convinces you of that fact.  If not, you just have to accept it on
faith.

Therefore:

(1) __If__ the deposit is compounded monthly or daily,....

     Then given the AER, the monthly and daily rates are computed as
follows, using your notation:

     monthly rate:  =(power(1 + AER/100, 1/12) - 1) * 100

     daily rate:      =(power(1 + AER/100, 1/365) - 1) * 100

     Caveat:  Because the published AER is only an approximation, the
monthly and daily rates are also inexact.

(2) __If__ the deposit earns simple interest on the daily or average
daily
     balance, which is then compounded monthly,....

     Then it is difficult to compute the daily rate from the AER.

     You need to find the daily rate "r" such that the following
equals the AER (where "r" is expressed like 5.75, as you prefer,
instead of 5.75%):

     =(product(1 + {31,28,31,30,31,30,31,31,30,31,30,31}*r/100/365) -
1) * 100

     That is an array formula, which must be commited using ctrl-shift-
Enter.  You could use Excel Solver to find "r".

     Caveat:  But again, since the published AER is only an
approximation, the formula above might not agree with the published
gross rate (divided by 365).

A word about accuracy....

You asserted:
> British Banking Code of  Practice requires UK banks to publish both
> Gross interest and EAR (Effective Annual Rate) to 2 decimal places.

I will have to take your word for it.  I cannot find that requirement
in the part of the BBBC posted on the BBA web site.  In any case, that
does not necessarily preclude the bank's using greater precision
internally -- unless you can point to regulation to that effect.

By analogy, the corresponding US regulation states:

     The annual percentage yield, the annual percentage yield
     earned, and the interest rate shall be rounded to the nearest
     one-hundredth of one percentage point (.01%) and expressed
     to two decimal places.  For account disclosures, the interest
     rate may be expressed to more than two decimal places.

Parsing that carefully, it implies that the internal rate, which might
be specified on the account disclosure, might have higher precision
than how it is specified on other disclosures.

Moreover, US regulation states:

     The annual percentage yield (and the annual percentage yield
     earned) will be considered accurate if not more that one-
twentieth
     of one percentage point (.05%) above or below the annual
     percentage yield (and the annual percentage yield earned)
     determined in accordance with the rules in appendix A of this
part
     [which specifies the algorithms for calculating the APY].

The point is:  if the BBBC permits similar latitude, even if the
published AER and gross rate are 5.75% and 5.6%, there are a lot of
reasons why you might not be able to derive those exact numbers by
mathematically-correct conversion formulas, even if you use a number
between 5.745% and 5.7549...9% for the AER.  The gross rate might be a
number between 5.595% and 5.6049...9%.  The final balance of "105,750"
might be a number between 105,749.50 and 105.750.49.

> I'm printing off your response, to attempt validation of a 4 month period 
> of
> my actual bank statement, using your alternate suggestions (4 months is a
> period with no activity except monthly interest credits, and a static
> interest rate).  Comparison versus actual should be interesting, show I'm 
> on
> the right lines!

When you try to apply these concepts to your bank statement, you need
be very flexible.


0
nospam5510 (78)
5/28/2007 11:20:22 AM
Reply:

Similar Artilces:

HELP #2
I recently cleaned my hard drive to the bone and reinstalled everything on my Windows 98SE computer. Now when I run Publisher 2000, after about 15 seconds up pops a window that says, "There was a problem starting the Office Assistant. Would you like to try reinstalling it?" I have the stand alone version of Publisher 2000. I've tried the "Repair" option without success. Isn't the Ofc Asst the paper clip in Word? I have Word 97. Anyone know the fix? -- Don -------- Vancouver, USA - One of the great cities in one of the 45+ countries in America! OFF2000: &q...

Excel exact copy workbook to workbook HELP NEEDED !!
I have looked everywhere for this answer and I turn to the news groups to find the answer. I wish to copy a group of formulas from one excel workbook to another excel workbook. I could drag and drop but all the fomulas now reference the original workbook in their formulas. This is not what I wish to see. I need it to be an exact copy. I am dealing with way too much data to retype it all. Basically I want page six of the second workbook to look exactly like page six from the original workbook. John, Try this in Windows Explorer. Select the file. Menu pick edit/copy, then edit/ past...

Please help!!! Manually removal of Exchange 5.5!
I need to manually remove my old exchange 5.5 from the AD and my e2k, but I am not sure how much to delete! Is it just the server or is it anything else? I have tried every thing I could fined on the Microsoft web! Well, it's a big task. I guess you have the Exchange 2003 server in place in your org. You need to remove the registry keys as well as edit the ADSI to remove the Exchange 2000. You better read the docs. Search the MS site for Manually removing Exchange articles. Cheers ! Ruwan Dissanayake >-----Original Message----- >I need to manually remove my old exchang...

Time Sync help
Does anyone have an example or article handy of a way to sync workstations time w/ a server? Are you talking about a Server and WorkStation that you have created. If so I have done the exact code in the past, however, not sure if I can help. It all depends on how your server and work station communicate. This can be further complicated if you have a server running along with several work stations.. across time lines. "Kelly Brimstone" <kellybrimstone@yahoo.com> wrote in message news:7d3b4b05.0309021126.49bb14d7@posting.google.com... > Does anyone have an example or articl...

Send As help #2
Can you guide me to information about how to properly set up a "send as" account? The user uses the "send from" field with 3 other email addresses and is getting rejections intermittently. Also is getting an Lsasrv 40961 error intermittently in his event log. In news:E4A65D7E-39A9-425D-9F04-5FF435482FF9@microsoft.com, Tony K <TonyK@discussions.microsoft.com> typed: > Can you guide me to information about how to properly set up a "send > as" account? > The user uses the "send from" field with 3 other email addresses and > is gettin...

Help with adding x-axis major gridlines
I'm trying to make a chart that will have small tick-marks along the x-axis for a fiscal week, and a long tick-mark separating the fiscal month. I think I'm not formatting the data properly in the worksheet. Any ideas? Use a Line chart with dates as the X values. Double click the X axis. On the Scale tab, set the X axis minimum to occur on the 1st of a month. Set the Base Unit to Day(s), the Major Unit to 1 Month(s), the Minor Unit to 7 Day(s). On the Patterns tab, choose whatever for Major Ticks and something besides None for Minor Ticks. Click OK. On the Chart menu, choose C...

Formulas in a paragraph???HELP!!!
How would the formulas go in the below paragraph? This letter will confirm that Vision Financial Corp will accept =c9 as full payment of debt; you may take advantage of this special offer immediately by contacting us at . Once credit or check payment by phone is taken, processed and applied to the account, the account will be considered settled in full. =A9 will be released of any further financial liability converning repayment of this account. How many times are you going to ask this question? What is wrong with the responses you've already received? You have many of ...

Help! Money hangs on startup
Hi all, I had to hard reset my laptop, and after this Money hangs at startup. It plays the startup sound and hangs. What could be the problem here? Thanks in advance. -- Johan Johan Parin wrote: > Hi all, > > I had to hard reset my laptop, and after this Money hangs at startup. It > plays the startup sound and hangs. What could be the problem here? > The problem was solved by removing the following files: My Money.M14 My Money.lrd My Money Backub.mnf -- Johan ..M14 is a backup Money 2005 file from a Money 2006 upgrade. Removing it eliminates your roll-back ability but...

Need Help with Creating a Multi-part Rule for Outlook 2003
Hi all -- Has anyone else come across the need to do the following: * Have a rule that copies and then forwards all non-domain generated e-mail to another e-mail address. For example: - Have all e-mail sent by anyone within the xyz.com domain to remain in the Inbox, but anything not sent by someone within xyz.com will be copied and forwarded to another recipient. If any one knows of a way to accomplish the above or if it is not possible, please reply here. Sincerely, Ralph Greenberg, MCSA Set a rule to copy/forward all mail items (no defining crit...

Error accessing Help files
I am unable to use any of the hyperlinks in the Help topics. I can select a topic, which opens right up, but then if I click on Related Topics or any of the other hyperlinked topics, I get a script error. I am running Windows 2000, Version 5.0. My Publisher is version 2000 SR-1. Suggestions? After managing to set up OE-QuoteFix on his new PC, Ed reads a message from TJF <tomme_fent@iand.uscourts.gov>... > I am unable to use any of the hyperlinks in the Help > topics. I can select a topic, which opens right up, but > then if I click on Related Topics or any of the other...

calculating #2
I have an EXCEL workbook that for some reason does not calculate completely. when i hit F9 for it to calculate it takes a long time and at the bottom of the page the word "Calculate" does not go away. any ideas? Hi Frank, Calculate is an indication that you have turned Calculation off in Tools, options, Calculation (Tab), [X] Manual -- and that you have something that needs to be calculated. If that is not the problem then try Ctrl+Alt+F9 which recalculates all cells on all worksheets in all open workbooks. If you still have a problem show us what your formulas loo...

Rookie-building DB-want to get right the first time! Help?
Rookie user: Access 2007, Using “Picture Yourself Learning: Microsoft Access 2007” as reference guide. Need suggestions for how to structure what seems to me to be a very complicated DB. I want to build it the best way the first time, so I don’t do a lot of work and not have it do what I need. Here’s what I need to do: Track GIS datasets for about 50 natural and environmental hazards. The data itself does not need to be tracked. I do need to track its source (National, State, County or City data) and know its date of creation, and frequency of updates for example. I don’t k...

Please Help
i put vba textbox in word 2002, i exit design mode and saved BUT.. every time i open it up it opens up in design mode!!!!! what can i do to make the file open not in design mode??? i thought off writing in document_open : close the design mode, but i dont know the code line. all answers will do.. thenxs I bet you'd get better answers in an MSWord newsgroup (as opposed to an Excel newsgroup). DirectD@gmail.com wrote: > > i put vba textbox in word 2002, i exit design mode and saved BUT.. > every time i open it up it opens up in design mode!!!!! > > what can i do to mak...

Outlook 2002 Notes Connector help
I use Outlook 2002 (Offix XP SP2) with the Outlook Notes connector and access a Notes 5.x database running under W2K Server SP3. For some reason, this seems to cause the Notes service on the server to hang when accessing mail. It hangs even faster if I try to copy email from Notes to a PST. Has anyone seen this problem? Is there a fix? Thanks! ...

Excel Help Please quick question
i want to have a cell automatically fill its self in when another cell says a certain thing. eg - if i type "blue" into D5 i want D6 to automatically change to "yes" Is this possible Thanks In Cell D6 put this formula =3DIF(D6=3D"blue","yes","") On Nov 15, 7:13=A0pm, Tom Jacques <Tom Jacq...@discussions.microsoft.com> wrote: > i want to have a cell automatically fill its self in when another cell sa= ys a > certain thing. > > eg - if i type "blue" into D5 i want D6 to automatically change t...

Exchange 5.5 on NT Recovery help!
Hello, I'm looking for some general direction on recovering an Exchange 5.5 server on NT... We need to recover a NT 4.0 Exchange 5.5 server from 2003.. The server was backed up directly to tape using NT Backup. We have two options... 1. If we have the original server, I should be able to load NT, SP6a, then perform the restore from tape... Should work.. 2. If we don't have the original server, I'm hoping to simply restore the Exchange databases onto another NT/Exchange test server we have. Mount the store (somehow), and export the mailbox we need.. The bottom line i...

Need help printing multiple pages in VB.Net 2008
I am having a problem printing multiple pages with a common header/footer etc in VB 2008. I have the following code which will print a single page exactly as I need it. '***************** Print button click event ************ Private Sub cmdPrint_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdPrint.Click pdPrintCal.PrinterSettings.PrinterName = lblCurPrt.Text pdPrintCal.DefaultPageSettings.Landscape = True If chkPrtPView.Checked Then pvPrintCal.Document = pdPrintCal pvPrintCal.Icon = Me....

Automatic Mortgage Calculation
I am running MYM2003 SE Version 11. I have set up my mortgage account as a loan with all of the information regarding rate, term, amount, etc. I pay my mortgage to the bank from my checking account online. When I prepare the transaction, I input the Category as Loan Payment:Mortgage ( name of mortgage account). The mortgage account collects the data associated with the transaction but incorrectly calculations the amount of interest vs. principal. In fact the calculation is also inconsistent. (i.e. January Payment Principle 700 vs. 1243.69, March Payment Principle (138.67). This requires m...

Need help converting "InstalledOn" date for all Win32_QuickFixEngineering entries
Hello, all. I'm new to Powershell scripting, but have used VBScript for years. I've found that there is no way in VBScript to do the following and am hoping someone can help with a PowerShell script for the following: I need to be able to document all of the HotFixIDs, their description, and the date that the HotFixes were installed. I want this list to be a CSV list available in the root directory of the computers that need this information. All of the computers that I'm going to use this on are 64-bit computers and are running Windows Server 2008 and Vista. I found ...

Calculating diff between date/ time only between certain hours 03-01-10
I am trying to find the diff in hours only between 8:00 and 17:00 Monday to Friday only, any hours that fall outside of that time frame should not be counted. Each calculation will have different dates so if there is a way to do this without specifying the date that would be great! 2/19/2010 16:00 2/22/2010 9:00 ??? Thanks for any assistance you can offer! Hi there GMv1, Assuming "2/19/2010 16:00" is in cell A1 and "2/22/2010 9:00" is in cell B1. The formulae in cell C1 would be as follows : =INT((B1-A1)*24) Let us know if this helps yo...

Anyone intrested in helping a pathetic charity case?
Yes, the time has come for me to admit my failure and beg for someone to assist is setting up my server to use Exchange 2003 in place of the built-in SMTP/POP3 in windows server 2003. I can'r figure it out and Microsoft is absolutely no help at all. They can't even get a server product to work properly out of the box, why would they care to help fix their mess. if any system admins would like to help send me an e-mail to gmaier@tampabay.rr.com or MSN me Thanks desperate. Can you post details of the issue here? -- Bharat Suneja MCSE, MCT www.zenprise.com blog: www.suneja.com/b...

Outlook 2007 doesn't show entire message!! HELP please!
I have been searching for a probmlem i have with Outlook 2007. I am recieving emails fine, but what i noticed the other day, is that when i open the message with my PC, only part of the mesage is shown, the part that is from the sender, but when it includes a forwarded text, it will not show!! this is a huge problem.. once i missed an important notice because of this. But this problem does not occur when i open the mail with Mac. Do you have a virus scanner installed which integrates itself with Outlook? Uninstall this integration part of your virus scanner and try again; you'...

ldifde HELP
I am try to generate a list of all users that forward mail to an alternate mailbox recepient.. I found the command in this newsgroup but it does seems to work. Can someone take a look at let me know what I am missing ???. I am running Win2003 sp1 and exchange 2003 sp2 ldifde -f output.ldf -r "(objectClass=user)" -l displayname,altrecipient Thanks "Darren@community.nospam" <GQ@community.nospam> wrote: >I am try to generate a list of all users that forward mail to an alternate >mailbox recepient.. I found the command in this newsgroup but it does seems ...

Advanced Filter sort on "asterisk" (wild card) hides data, not just empty blank cells.
In debugging a program, I isolated the problem to the following issue. I manually created the following situation in column A, rows 1 to 6, by copying and pasting actual data in order to accurately re-create the problem: ABN/ACN/BN * ABN/ACN/BN 3158816 40000545415/005 Excel "Help" has this to say about the asterisk: " * (asterisk) Any number of characters in the same position as the asterisk For example, *east finds "Northeast" and "Southeast" " I am assuming that the asterisk, being the wild card symbol, being the "cell not empty" symbol, ...

Pls help Asset Allocation Question
My brother is 51 years and his wife is 46 yrs old. He is thinking of buying a home. He is not sure of how much of his total wealth / net worth should be in Stocks and Real estate Someone told him that cash and bonds aside, real estate and Stocks ( incl stock mutual funds) should be approx 50% each Since both stocks ( incl stock mutual funds) and real estate are more volatile, he was told to split that portion of his net wealth.. 50% each. Pls let me know if this is generally speaking, approximately correct...because I know it is difficult to be absolutely correct in these things with s...