2007 - Formula for Calculating Interest due on given date with principal & AER

[chas125]

Hi all, first post here though I've read thousands.
I want to know what formula to use to calculate interest due on a loan on any particular date, where the rate is quoted as an AER (or Effective rate) and the interest is calculated (and compounded) daily (assuming no repayments made for this purpose)
I thought I had it with ACCRMINT but it doesn't account for compounding

e.g.
£10,000 borrowed on say 19/4/12 (Assume no repayments made) @ 10% AER and 1 year later on 19/4/13 £1000 interest would have accrued, simple. However this is an AER and according to [link removed] this equates to a nominal annual rate of 9.5322% and daily rate of 0.0261% so based on this by my calcs after 183 days (can't do exact 6 months as based on 365 day year) £486.42 interest has accrued (ACCRMINT produces £501.37)

Hope I've explained the problem ok?? ... and someone has the answer! ... I'm sure its dead simple, but I don't have the answer!

Thanks in advance, Chas

 

[xenou]

Typically if the effective rate is quoted the daily compounded rate is EAR/number of periods, or in this case 10%/365 = .000273973

But it might not be. You should read the fine print on the particular loan or deposit in question. Personally I prefer to just use an amortization schedule, which gives:

at daily rate of .000261 $489.15
at daily rate of .000273973 $514.08

Of course neither Excel nor apparently your calculator quite gets to the same answer, but in my experience you have to be familiar with the company you are dealing with and how they work out their rates - we are all in the same ballpark, but there's different assumptions going into different formulas/calculators/etc.

 

[andiemac]

I agree with xenou, as you will often find financial calculators that don't return the same results as you might find in Excel, and I am not suggesting which is the better choice.

I would suggest that you try the =fv() function in Excel, and possibly the =effect() function as well. I did not end up with the same numbers that you did, but the =fv() - Prinicple of 10,000 would result in the same value as xenou's example of "daily rate of .000273973 $514.08". =FV((0.1/365),183,0,-10000,0) - 10000

 

[chas125]

Thanks for your prompt replies, xenou & andiemac ...
Yes I accept that various financial calculators can produce different results, but ultimately there is a "correct" answer and method of calculation .. I just don't know how to do it on excel!

I could use a schedule based on the .000261 rate (which I've been doing to cross check results anyway) but this is cumbersome and I would be amazed if there is not an excel function which does this neatly, concisely and precisely given two dates, an AER & a principal sum.

The daily rate of .000261 is the one to use (to 6 d.p. anyway ) and produces results of £489.15 @ 183 days as xenou states and £999.37 @ 365 days using a (compounding) schedule - which is only marginally out from the 1000 it should be, due to the fact rates just to 6 d.p. I expect - fine by me.

The .000273973 (10%/365) rate is simply the wrong one to use and doesn't take account of compounding daily and produces the wrong (high) results as can be seen above - The effect() function is not the one to use either, as it produces an AER given the nominal rate ... however, using the reverse function , the nominal() function would give a more useful % figure to work to as it calculates the nominal rate (9.5323% from the AER of 10% & 9.5323/365 = .000261 - note this is the same figure given on the calculator.

However, using the fV function with the nominal function & the 10% rate, does appear to to do the trick!
i.e. =FV((nominal(10%,365)/365),183,0,-10000)-10000 - produces same results as compounding schedule above
I still reckon there's probably another, simpler formula in the excel repertoire that does this ... seems to me that the calc I'm trying to do is "more basic" than that which fV function is designed for.

Any other suggestions?

Thanks guys

 

[xenou]

However, using the fV function with the nominal function & the 10% rate, does appear to to do the trick!
i.e. =FV((nominal(10%,365)/365),183,0,-10000)-10000 - produces same results as compounding schedule above
I still reckon there's probably another, simpler formula in the excel repertoire that does this ... seems to me that the calc I'm trying to do is "more basic" than that which fV function is designed for.

Sounds like a winner to me - I didn't even know we had a nominal function in Excel. Nice one.

 

[andiemac]

There could be a finanacial add on that you could get, but I have not used any of these so can not make any recommendations.

 

[xenou]

As to why there is a not a "simple" inbuilt function for this, my guess is that normally as a consumer you are dealing with nominal rates (that's the rate that is generally told to you as the depositor or borrower). So typically for computing interest you are working with the nominal rate, from which you then want to look at the actual interest that accrues to you, and so on. I could be wrong - it's one theory anyway. Following this line of reasoning we could have (but don't have) two versions of every financial function dealing with rates, one that uses nominal rates and one that uses effective rates. But that of course would probably be an awkward situation of another kind.
ξ