Re: calculate interest for one-time payment on loan

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.

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?



Yeah, that's pretty close... just need to factor in the principle.

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

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):


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.


[Date Prev][Date Next]   [Thread Prev][Thread Next]   [Thread Index] [Date Index] [Author Index]