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 _______________________________________________ gnumeric-list mailing list [email protected] http://mail.gnome.org/mailman/listinfo/gnumeric-list
