Re: calculate interest for one-time payment on loan
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 gnumeric-list@gnome.org http://mail.gnome.org/mailman/listinfo/gnumeric-list
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. >> >> 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
Re: interest rates
The important issue is what conventions are used for time and rate. About 25 years ago I tried to get information on this from Canadian banks. Some were cooperative. As I recall, the three that responded used three DIFFERENT rules. This was for weekly payment mortgages. In Canada, there is a little known law that prescribes that mortgage interest be computed "annually or semi-annually, not in advance". Moreover, if nominal annual rate is over 6%, the borrower must be provided with a schedule of payments, or everything defaults to the 6% rate after the fact. There've been some interesting commercial mortgage cases from the early 80s where rates were around 20% and the schedule was not correct. To get to monthly, weekly or daily mortgage payments, you have to know how many periods there are. For monthly payments, we can use 6 months, so the "working" rate is 100 * [(1 + nominal_rate/200)^(1/6) - 1 ] Is everybody still there? Weekly or daily? Well, there are, as I recall between 181 and 185 days (I should check this, it's been a while) in a "half-year, depending on the start date and whether one uses calendar "date". Or using days, one has to decide when things end. Or 365/2 = 182/5. But 182.5/7 is a bit more than 26 weeks, worse in leap years. Which is where the fun begins. I'm not sure I want to put canned formulas into Gnumeric or any other spreadsheet for this, and I would definitely like to see more transparent output for mortgage payments. Of course, in the UK (or at least England as Scotland may have its own rules) most mortgages are demand loans so use floating rates. JN ___ gnumeric-list mailing list gnumeric-list@gnome.org http://mail.gnome.org/mailman/listinfo/gnumeric-list
Re: Median: Oasis and Fast Sorting Algorithm
On Sat, 2007-10-02 at 00:38 +0200, Leonard Mada wrote: > 2. The *OASIS open formula document* puts the sorting of the array as a > prerequisite to defining the median. This is misleading and should > therefore be replaced with a more algorithm neutral definition. > Unfortunately, the subscription to OASIS is too expensive for me, > therefore I hope that other persons that do have access to the > development board point this out. You are clearly misreading the document. The Open Formula document does not prescribe algorithms but only describes the return value of the Median function. So the value ought to be the middle value (or the average of the two middle values) if the data were sorted. No implementaion would be required to in fact sort the data! (...) > Although this definition is somehow more complex, I believe it is more > accurate and more algorithm neutral. How can it be _more_ "accurate"? The OpenFormula description is 100% accurate. I think it is imperative that the least complex accurate definition is used in the OpenFormula document. It is up to implementation to chose the preferred algorithm. (The ideal algorithm may vary depending on the type of data they typically encounter.) Andreas -- Andreas J. Guelzow Pyrenean Shepherds ___ gnumeric-list mailing list gnumeric-list@gnome.org http://mail.gnome.org/mailman/listinfo/gnumeric-list
Re: Median: Oasis and Fast Sorting Algorithm
Well, the OASIS formula has a big problem with situations like the following: data set is: 1,1,1. So the list contains 4 values of 1. So, the median is the middle value, BUT there is really just one value repeated 3 times. Or consider the following list: 1, 2, 2, 2, 3, 4. So the calculation of the median does take just 2 of the 3 values of 2, which is a little bit ambiguous. Which one is the middle value? Are 2 two's more middle than the 3rd two? Just my thoughts. Leonard Andreas J. Guelzow wrote: > On Sat, 2007-10-02 at 00:38 +0200, Leonard Mada wrote: > > >> 2. The *OASIS open formula document* puts the sorting of the array as a >> prerequisite to defining the median. This is misleading and should >> therefore be replaced with a more algorithm neutral definition. >> Unfortunately, the subscription to OASIS is too expensive for me, >> therefore I hope that other persons that do have access to the >> development board point this out. >> > > You are clearly misreading the document. The Open Formula document does > not prescribe algorithms but only describes the return value of the > Median function. So the value ought to be the middle value (or the > average of the two middle values) if the data were sorted. No > implementaion would be required to in fact sort the data! > > (...) > > >> Although this definition is somehow more complex, I believe it is more >> accurate and more algorithm neutral. >> > > How can it be _more_ "accurate"? The OpenFormula description is 100% > accurate. > > I think it is imperative that the least complex accurate definition is > used in the OpenFormula document. It is up to implementation to chose > the preferred algorithm. (The ideal algorithm may vary depending on the > type of data they typically encounter.) > > Andreas > > > > ___ gnumeric-list mailing list gnumeric-list@gnome.org http://mail.gnome.org/mailman/listinfo/gnumeric-list
Re: Median: Oasis and Fast Sorting Algorithm
On 10/02/2007 10:10 AM, Leonard Mada wrote: > Well, the OASIS formula has a big problem with situations like the > following: > data set is: 1,1,1. So the list contains 4 values of 1. Looks like 3 to me, not 4. One of us has a big problem with the counting algorithm :-) > So, the median > is the middle value, BUT there is really just one value repeated 3 times. So who cares? The median value is 1. Is your alternative going to return some value other than 1 > > Or consider the following list: 1, 2, 2, 2, 3, 4. So the calculation of > the median does take just 2 of the 3 values of 2, which is a little bit > ambiguous. (2+2)/2 == 2. (2+2+2)/3 == 2. I see no ambiguity here. > Which one is the middle value? Are 2 two's more middle than > the 3rd two? Again, who cares *which* two? Is your alternative going to return some value other than 2 ___ gnumeric-list mailing list gnumeric-list@gnome.org http://mail.gnome.org/mailman/listinfo/gnumeric-list
Re: Median: Oasis and Fast Sorting Algorithm
John Machin wrote: > ... > So who cares? The median value is 1. Is your alternative going to > return some value other than 1 Please define mathematically the middle value! It is NOT trivial as my definitions showed. Anything else would be ambiguous. This should be a standard, so make a better definition. Well, I could have used a much shorter definition: the median is the value that halves the list so that there are two sets of equal size with numbers in the first set being higher than the median and numbers in the second set being lower. As noted, this definition avoids the sorting, too. (One could extend this definition for even and odd number of elements. Or even a much shorter definition: the 50th percentile. BUT all these definitions are ambiguous, see later.) The one thing that I do NOT agree at all with the OASIS definition is, that it includes the wording "sorting". Sorting is definitely NOT necessary to calculate the median. You can take any array, even one that is NOT sorted, and determine the median without first sorting it. This is much to often stated wrongly in so many textbooks, BUT sorting is really not necessary. So, this is NOT a prerequisite that should enter a standard definition. May I even point out, that for even number of elements, one may define/have an upper median and a lower median. Alternatively, in serious mathematical uses, the median is usually calculated using a weighted approach. Therefore, the median of 1,2,2,3,4,5 is NOT (2+3)/2 = 2.5, BUT rather (2+2+3)/3 = 2.66. So, it does make sense to have a very strong and unambiguous definition in a standard. The *weighted median* may be introduced later into the standard and then the ambiguity would be complete. So, let's do a good job now and not when it is too late. Leonard ___ gnumeric-list mailing list gnumeric-list@gnome.org http://mail.gnome.org/mailman/listinfo/gnumeric-list
Median: Oasis and Fast Sorting Algorithm
This post deals with algorithms to calculate the median and the
repercussions of such algorithms on the definition of the median.
To calculate the median, many textbooks indicate that the array must be
*sorted first*. This is both unnecessary as well as very *expensive* as
a full sort is very time-consuming.
1. I will therefore try to introduce some methods that do NOT need full
sort to calculate the median. These are significantly faster than the
classic algorithm. Because the sorted array is NOT further used in a
spreadsheet after calculating the median, the sort is in most
circumstances superfluous.
2. The *OASIS open formula document* puts the sorting of the array as a
prerequisite to defining the median. This is misleading and should
therefore be replaced with a more algorithm neutral definition.
Unfortunately, the subscription to OASIS is too expensive for me,
therefore I hope that other persons that do have access to the
development board point this out.
I do not know, which algorithms do gnumeric and OOo Calc use when
calculating the median, BUT, because these alternative algorithms offer
many advantages, they should be strongly considered (IF the current
algorithm uses the usual full sort).
1. FAST ALGORITHMS
==
Some fast algorithms are presented on the following page:
http://ndevilla.free.fr/median/median/node20.html
I have imagined a different algorithm, too, although I never tested it.
I will briefly describe the case with 2*n elements (2n+1 should be similar):
1. take first (n+1) elements
2. sort these elements using e.g. Quick Sort (if enough memory) or a
different O(n log (n)) algorithm => this sorted array is x[0] to x[n]
(has n+1 elements)
3. there are still (n-1) elements to process
4. WHILE (still unsorted elements) {
5. IF (next element > x[n]) => drop this element and drop x[0]; => we
have (n-2) elements left to process; and (n) elements in the sorted
array: x[1] to x[n]; => renaming array to x[0] to x[n-1];
6. ELSE IF (next element < x[0]) => drop this element; drop x[n], too;
=> (n-2) elements left to process; (n) elements left in sorted array:
x[0] to x[n-1];
7. ELSE: drop x[0] and x[n]; new array is x[1] to x[n-1] (n-2 elements);
put new element in this array and sort this new array (because this
array is already sorted, adding one value should proceed with log(n)
speed) => will have now (n-1) elements => renaming (new x[0]) to (new
x[n-1]);
8. REPEAT (instead of 'n' use last element in sorted array)
Initially: (n+1) values in sorted array; (n-1) still to process
1st iteration: (n) values; (n-2) values;
2nd iteration: (n-1) values; (n-3) values;
...
(n-2) iterations: 3 values; 1 value left to process;
last iteration: in the end, the sorted array will consist of only 2
values => median = (x[0]+x[1])/2
This algorithm should have at most O(n/2 * log(n) -1 + log[(n/2 -1)!])
(where '!' is factorial), but it is probably faster. [I am not sure,
that my calculation is actually accurate, so take this result carefully.]
It is NOT recursive, it computes actually the median accurate even for
an even number of elements and does NOT consume as much memory as other
algorithms.
2. OASIS Open Formula Format
===
The OASIS open formula document (2007-02-08) describes the median as:
> MEDIAN
> Summary: Returns the median (middle) value in the list.
> Syntax: MEDIAN( { NumberSequence X}+ )
> Returns: Number
> Semantics:
> MEDIAN logically sorts the numbers (lowest to highest). If given an
> odd number of values, MEDIAN returns the middle value. If given an
> even number of values, MEDIAN returns the arithmetic average of the
> two middle values.
This is a bit misleading. *MEDIAN logically sorts the numbers* is NOT
really necessary to calculate the median. Actually, such an algorithm is
notoriously slow. There are far better alternatives to get the median,
and therefore a more neutral definition for the median seems warranted.
Also, this definition is ambiguous for sequences like 1,1,2,3, where the
two middle values are in effect 3 elements (two ones and the element 2).
DEFINITION
==
This is an algorithmic definition, too, BUT leaves the option open which
algorithm is chosen.
1. Odd numbers: The median is that particular element from the list that
bisects the list into 2 halves, so that one can select 2 non-overlapping
sets with an equal number of elements, so that all elements are greater
or equal to the median in one set and smaller or equal in the 2nd set
(the element that is the median is excluded).
2. Even numbers: The median is the mean of those 2 values that bisect
the list in two equal halves, so that we can find 2 non-overlapping sets
with an equal number of elements, one set in which all elements are
larger or equal to the median and the second where all elements are
smaller or equal to the median (the 2 elements from which the median was
calculated are excluded).
Test Case:
1,2,3 => m
Re: Median: Oasis and Fast Sorting Algorithm
The finding on the k-largest element in an unordered sequence of n elements is a linear-time and (if memory serves) constant-space problem in terms of n. See Knuth for details. The trouble with the algorithm is that it is rather complicated. More code means more chances of bugs. Unless you are Knuth, of course. Add to that that there are several different "medians" out there -- I think we have no less than three in Gnumeric -- out there and sorting starts to look appealing. Morten ___ gnumeric-list mailing list gnumeric-list@gnome.org http://mail.gnome.org/mailman/listinfo/gnumeric-list
