is there an Excel table available?

[Silvabod]

My maths isn't up to this! I'm hoping there is a pre-defined Excel table in
existence already - if so, please point me to where I can download?

For a range of Annual Percentage Rates (APR) - between say 3% and 8%, the
AER rates for interest paid monthly (and the daily rates by month, as
interest is allegedly calculated daily).

Intention is to track progress/total of various cash investments. Some are
annual deposits, capital increased annually, others are "ad hoc" deposits
with spasmodic capital injections, interest accrued daily, credited monthly.
It's the latter giving me the maths headache! I've traced the actual (bank
formula, but converting it to Excel .... beyond my capability.

 

[Niek Otten]

You don't need tables.
Excel has a set of financial functions that cover interest calculations.
Look in HELP for RATE, NPER, FV and EFFECT, NOMINAL
Post again if you can't figure it out

 

[Silvabod]

Thanks! Will try (too late tonight).

You don't need tables.
Excel has a set of financial functions that cover interest calculations.
Look in HELP for RATE, NPER, FV and EFFECT, NOMINAL
Post again if you can't figure it out

 

[Marcus Fox]

My maths isn't up to this! I'm hoping there is a pre-defined Excel table in
existence already - if so, please point me to where I can download?

For a range of Annual Percentage Rates (APR) - between say 3% and 8%, the
AER rates for interest paid monthly (and the daily rates by month, as
interest is allegedly calculated daily).

Daily interest rate is the nominal rate divided by 365. You can find the
nominal rate using the formula =NOMINAL(AER,number_of_investment_periods) In
your case number_of_investment_periods is 365, simply divide by 365 to get
the daily interest rate. If you get #NAME as a result, you will need to
install the add in from the CD.

Marcus

 

[Silvabod]

Marcus, thanks for the reply. However the actual number returned is
completely wrong, so what am I doing wrong?

My "test" example. 10% APR, number of periods 12 (months). Opening capital
£10000 - logically, after 12 months of interest credits, the final sum
should equal or very closely equate to £11,000.

Function =NOMINAL(10,12) which returns 0.095689685 (to 9 decimal places).
BUT - (my maths is basic) no matter how I use this as a multiplier applied
to the capital 10000, I cannot get a sensible result (i.e equating to
£11000) after 12 months,

I've created a table using (10/365)*x (where "x" is the no of days in the
month) - this returns interest of over 10%.

What I'm TRYING to acheive is the actual daily interest rate which,
annualised, would return 10% . I'm quoting 10% as a "test" - what I need
are the actual figures for a range of interest rates. This has to be
calculable This exists - the banks are using it.

 

[Niek Otten]

Divide the 0.095689685 by 12 and you have the monthly rate (0.79741%)

10.000*(1+0.79741%)^12 returns exactly 11000; FV = PV*(1+i)^n