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