Re: calculate interest for one-time payment on loan

[ken <gebser speakeasy net>]

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.  (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).  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.


Best,
ken