Help on Interest calculation? Advanced maths ?

[Silvabod]

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?

 

[macropod]

Hi Silvabod,

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

Cheers

 

[Silvabod]

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.

Hi Silvabod,

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

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

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?

 

[macropod]

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]
-------------------------

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.

Hi Silvabod,

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

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

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?

 

[Guest]

[Possible reposting. Google Groups seems to be having trouble again :-(.]

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) ---

 

[Guest]

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.

 

[Harlan Grove]

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 logicfault?

....

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.

 

[Silvabod]

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

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.

 

[Silvabod]

joeu2004, please see response to Harlan Grove which is dual addressed to
include you
Thanks, Silvabod

[Possible reposting. Google Groups seems to be having trouble again :-(.]

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) ---

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?

 

[Silvabod]

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.

 

[Silvabod]

Harlan, I've now had time to assimilate your input. There's some comments
within your original text, and a final para.
Silvabod

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.

 

[joeu2004]

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".

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'sa
weekend, or another bank (i.e. national) holiday, it's calculated on the
first working day of the following month. (The "classic" worksheet takesno
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.

 

[Silvabod]

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.

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".

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.

 

[joeu2004]

["Reposting" -- rewritten! It appears that Google Groups screwed up
again. Argh!]

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/polopoly.jsp?d=140
..

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 (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 isan
approximation calculated from Gross.

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

"Where interest rates are advertised, these must be described as
follows: a. W% gross [...] and d. Z% Annual Equivalent Rate ('AER')
[....] The AER is a truly reflective rate, taking into account the
frequency with which the product has interest paid or compounded.
[....] 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
=(POWER((1+GROSS/100),1/PERIOD)-1)*PERIOD*100 gives EAR,
where GROSS = gross interest, PERIOD = # times per year it is paid

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

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

On the other hand, GROSS can be computed by:

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

So perhaps you are merely confusing gross and AER.

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

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=135&a=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
£100 pays

£1.25 (1.25% (¼ of 5%) of £100) after 3 months,
£1.26 (1.25% of £101.25 (£100 + £1.25)) after six months*,
£1.28 (1.25% of £102.51) after nine months, and
£1.30 at the end of the year (1.25% of £103.79),
giving a total including interest of £105.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:

=100 * 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 periodof
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:

=a*(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):

=(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.

 

[joeu2004]

Minor errata....

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

 

[joeu2004]

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 periodof
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'mon
the right lines!

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

 

[Silvabod]

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!

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.