Re: Help on Interest calculation? Advanced maths ?

Tech-Archive recommends: Fix windows errors by optimizing your registry

"Silvabod" <nos...@xxxxxxxxx> 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?

Spread*** 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 spread*** 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.

.

Quantcast