Re: calculate interest for one-time payment on loan

[John Machin <sjmachin lexicon net>]

On 9/02/2007 11:08 PM, ken wrote:
> On 02/08/2007 03:59 PM somebody named John Machin wrote:
> > On 9/02/2007 3:37 AM, ken wrote:
> > > Hi, people,
> > >
> > > This should be a simple thing to do.  I'm surprised there's not a
> > > function for it.  Or maybe there is one.  I've looked at the FAQ, read
> > > through quite a few of the functions, and searched the archives for this
> > > list and haven't found what I need.  As my Subject line says, I'm
> > > looking for a way to calculate interest for one-time payment on loan.
> > > Too simplistically, it's this:
> > >
> > > InterestAmt = DailyInterestRate * NumberOfDays
> > >
> > > DailyInterestRate = AnnualInterestRate / 365
> > >
> > > NumberOfDays(DateStart, DateEnd)
> > >
> > > Gnumeric is nice because variables such as the above can be hardcoded
> > > into a formula or fetched from a cell.
> > Gnumeric is nice in many respects, but AFAIK that functionality is, and
> > always has been, fundamental to *all* spreadsheet software :-)
> >
> > >  I could hardcode everything but
> > > DateEnd; that needs to be fetched from a cell.
> > >
> > > Anyone know a way to do this?
> > Perhaps I'm missing something, but I would have been highly surprised if
> > there were a function supplied for such a short uncomplicated formula.
> >
> > Assuming:
> > 1. your DateEnd value is in cell A1
> > 2. the start date is 31 December 2006
> > 3. the annual interest rate is 7%
> > then the formula for "interest amount" is
> >  7 / 100 / 365 * (A1 - DATE(2006, 12, 31))
> > (or something like that) -- isn't it?
> >
> > HTH,
> > John
>
> John,
>
> Yeah, that's pretty close... just need to factor in the principle. 

Errrmmm, that's "principal", not "principle".


> (And
> thanks for providing the syntax to give me the date subtraction.)
>
> That then gives us this formula (I called 5% "0.05" just to eliminate
> one operation):
>
> =0.05/365*Principle*(B31-date(2006,12,31)
>
> However, this formula assumes that there are 365 days in every year,
> meaning that any interest-accruing period which includes a leap year is
> going to be inaccurate (too much interest accrued).

You may mean "includes a leap *day*".

If the punter has a loan for 366 days, he should be charged one more day 
that a punter who has a loan for 365 days, quite independently of 
whether any of those days is a leap day (29 Feb) or not. See below.

>  The above formula
> works fine when no leap year comes into consideration.  When there is a
> leap year though, "365" must become "366".  This leads to the need for
> two formulas, one for leap years, another for regular years, plus an
> algorithm for determining which years are leap years and which aren't.
>
> Alternatively, we could approximate by adding a quarter day to each
> year, i.e., make the year 365.25 days long, but when the term of the
> loan doesn't include a leap year, or the number of leap years is not
> exactly one-fourth of the total years in the loan, then inaccuracy
> creeps in again.
>
> There are a couple other factors to consider, but I'm leaving those out
> in this stage of the discussion.  I'm thinking that the above should
> provide reason enough for there to be a function which incorporated all
> the above factors... at least for me to expect that there would be a
> function of the sort: interest(DateStart, DateEnd, rate); a possible
> fourth, Boolean, argument would specify simple or compound interest.
>
> There's another, probably better way to structure the formula, one which
> counts years only-- i.e., 2/28/2001 - 2/28/2000 would return 1.0
> (years), and this regardless of whether the year was a leap year or not.
>   (After all, a year is a year, whether it's a leap year or not;
> specifying an *annual* rate is quite different from specifying a *daily*
> interest rate.)  Whatever fraction of a year might occur would be
> returned as just that, e.g., 2.45 years.
>
>
> Thanks again for your reply and, in advance to anyone who wants to jump
> in on this.

May I be blunt? Thank you. All that carry-on about leap years is a 
nonsense. Rates are expressed as *nominal* annual rates with no 
consideration given to leap years. You need to find out what convention 
is used in your country / state / financial-environment to transform a 
nominal annual rate to a daily rate *independently* of how many 29 
Februarys fall inside the period in question.

Depending on how the annual rate is specified, the answer could be:
daily_rate = annual_rate / n
or daily rate = (1+annual_rate)^(1/n)-1
where n is the number of days in an interest rate year ... one of 
365.25, 365, yea verily even occasionally 360 no kiddin' ...

Then you need to determine how many days are involved. E.g. If the loan 
is advanced on Monday and repaid on Friday, is that 5 days or 4 days 
worth of interest?

Then given you have a daily rate and a number of days, all you need are 
(again) simple formulas:

simple: principal * daily_rate * ndays
compound: principal * ((1 + daily_rate) ^ ndays - 1)

I'm not too sure what any of this has to do with Gnumeric, but I hope it 
helps.

Regards,
John