February 2010 - mathfun@mailman.xmission.com

[math-fun] Moving averages and net present values
by Henry Baker 23 Feb '10

23 Feb '10

I was hacking a spreadsheet today & wanted to compute a moving average. I wasn't terribly particular about which moving average, but didn't find any moving averages in the list of functions that the spreadsheet program (OpenOffice Calc) offered. So I started looking for other functions that could be hacked to compute a moving average. Since the various "present value"-type functions offered a "discounting interest rate", I figured that I might be able to hack one of them to provide a moving average for a series of values. The first problem is the lack of rigor in the definition of these financial functions. I speculate that all OpenOffice has to do is to match whatever Microsoft Excel does; the documentation is completely irrelevant. Furthermore, Microsoft doesn't have to define their functions either; merely by being the de facto standard, their functions do what they do, and any attempt to change them would destroy the financial world. Which leaves us to experiment with these functions like some phenomenon in the natural world -- we will never know for sure what these functions were attempting to compute, but we can make models of what we think they might have been trying to compute and test these models against the actions of Excel/Calc. I went to Wikipedia, and even that description left a lot to be desired in rigorously defining what function we're trying to compute. For example, when discounting, does one discount using simple interest or compound interest? A mathematician instantly goes to the limit & suggests continuous compounding -- i.e., exponential weighting. Thus, given a (constant) instantaneous interest rate i, $100 today (t=0) is worth $100*exp(i*t) at time t, whether t is positive _or_ negative. But I didn't see exponentials in any of the definitions of net present value. Also, the net present value functions found in spreadsheet programs all seem to want some positive values and some negative values. While making investments (negative values) and collecting returns (positive values) may have been the initial impetus for computing net present values, there's no mathematical reason for requiring both positive and negative values in order to be able to compute net present values. (On the other hand, when one is computing _internal_ rates of return, the interest rate is undefined in the case where all of the values are positive, or all are negative.) --- I've been able to cob together an approximation to a moving average built upon the XNPV function, but I get some pretty insane discounting interest rates, which makes me believe that I'm not modeling XNPV very well. Suppose, for example, that I have a series of 30 numbers and I'd like a moving average of approx 3 of these numbers. If we set up a series of dates of 30 consecutive days, then we will need a pretty hefty interest rate to discount everything past approx 3 days. We'd need a discount rate of the order of magnitude of 33% per day, or on the order of 12,000% per year. However, when I plug in numbers of this sort into XNPV, I don't get the effect of a moving average of approx 3 days. Has anyone else managed to figure out what these spreadsheet functions actually do?

4 4

Re: [math-fun] Moving averages and net present values
by Henry Baker 22 Feb '10

22 Feb '10

Google produced some different documentation with a formula: http://wiki.services.openoffice.org/wiki/Documentation/How_Tos/Calc:_XNPV_f… XNPV = sum_i(pay_i/(1+rate)^((d_i-d_0)/365)) where pay_i is the i'th payment/receipt, d_i is the i'th date (in terms of whole days), and i runs from 0. So if I want to hack XNPV to compute sum_i(pay_i*exp(-(d_i-d_0)/madays)), then I equate the terms: pay_i/(1+rate)^((d_i-d_0)/365) = pay_i*exp(-(d_i-d_0)/madays) Solving for rate in terms of madays, we get rate = exp(365/madays)-1. (This formula is unstable when madays is >> 365.) In particular, if madays=3, then rate=6.9x10^52 ! No wonder I'm getting insane numbers ! So, #1 Although the XNPV documentation _said_ that at least one number had to be negative, the software (thankfully) doesn't check that & computes the correct answer anyway. #2 It _is_ possible to hack XNPV to compute a moving average, although the "interest rates" required to do it are absurd. >I was hacking a spreadsheet today & wanted to compute a moving average. I wasn't terribly particular about which moving average, but didn't find any moving averages in the list of functions that the spreadsheet program (OpenOffice Calc) offered. > >So I started looking for other functions that could be hacked to compute a moving average. Since the various "present value"-type functions offered a "discounting interest rate", I figured that I might be able to hack one of them to provide a moving average for a series of values. > >The first problem is the lack of rigor in the definition of these financial functions. I speculate that all OpenOffice has to do is to match whatever Microsoft Excel does; the documentation is completely irrelevant. Furthermore, Microsoft doesn't have to define their functions either; merely by being the de facto standard, their functions do what they do, and any attempt to change them would destroy the financial world. Which leaves us to experiment with these functions like some phenomenon in the natural world -- we will never know for sure what these functions were attempting to compute, but we can make models of what we think they might have been trying to compute and test these models against the actions of Excel/Calc. > >I went to Wikipedia, and even that description left a lot to be desired in rigorously defining what function we're trying to compute. > >For example, when discounting, does one discount using simple interest or compound interest? > >A mathematician instantly goes to the limit & suggests continuous compounding -- i.e., exponential weighting. Thus, given a (constant) instantaneous interest rate i, $100 today (t=0) is worth $100*exp(i*t) at time t, whether t is positive _or_ negative. > >But I didn't see exponentials in any of the definitions of net present value. > >Also, the net present value functions found in spreadsheet programs all seem to want some positive values and some negative values. While making investments (negative values) and collecting returns (positive values) may have been the initial impetus for computing net present values, there's no mathematical reason for requiring both positive and negative values in order to be able to compute net present values. > >(On the other hand, when one is computing _internal_ rates of return, the interest rate is undefined in the case where all of the values are positive, or all are negative.) > >--- > >I've been able to cob together an approximation to a moving average built upon the XNPV function, but I get some pretty insane discounting interest rates, which makes me believe that I'm not modeling XNPV very well. > >Suppose, for example, that I have a series of 30 numbers and I'd like a moving average of approx 3 of these numbers. If we set up a series of dates of 30 consecutive days, then we will need a pretty hefty interest rate to discount everything past approx 3 days. We'd need a discount rate of the order of magnitude of 33% per day, or on the order of 12,000% per year. > >However, when I plug in numbers of this sort into XNPV, I don't get the effect of a moving average of approx 3 days. > >Has anyone else managed to figure out what these spreadsheet functions actually do?

1 0

Re: [math-fun] Moving averages and net present values
by Dan Asimov 22 Feb '10

22 Feb '10

<< . . . Microsoft doesn't have to define their functions either; merely by being the de facto standard, their functions do what they do, and any attempt to change them would destroy the financial world. >> I strongly doubt that any serious financial institution would accept any software other than such in which every function's precise specifications is known and exactly what they want it to be. At least that's the way things were in Bank of America's Global Trading Dept. 1987-91, where we almost always wrote our own software. Too much money was at stake to screw up. --Dan ________________________________________________________________________________________ "Outside of a dog, a book is man's best friend. Inside of a dog, it's too dark to read." --Groucho Marx

1 0

[math-fun] Showing off to some kids
by Bill Gosper 22 Feb '10

22 Feb '10

my oft-quoted but never proved claim that "cyclotomic" sums and products of arbitrary trigs over a full period always have closed forms: sum(sin(x+%pi*j/n)*sin(y+%pi*j/n)/(sin(w+%pi*j/n)*sin(z+%pi*j/n)),j,1,n) = n*(cos((n-2)*z+y+x)/sin(n*z)+cos(y-x)*(cot(n*w)-cot(n*z))-cos(y+x+(n-2)*w)/sin(n*w))/(2*sin(z-w)) n %pi j %pi j ==== sin(x + -----) sin(y + -----) \ n n > ----------------------------- = / %pi j %pi j ==== sin(w + -----) sin(z + -----) j = 1 n n cos((n - 2) z + y + x) n (---------------------- + cos(y - x) (cot(n w) - cot(n z)) sin(n z) cos(y + x + (n - 2) w) - ----------------------)/(2 sin(z - w)), sin(n w) which is sort of interesting for the "n-2"s, and its w=z limit, sum(sin(x+%pi*j/n)*sin(y+%pi*j/n)/sin(z+%pi*j/n)^2,j,1,n) = n*sin((n-2)*z+y+x)/sin(n*z)+n^2*sin(z-x)*sin(z-y)/sin(n*z)^2 n %pi j %pi j ==== sin(x + -----) sin(y + -----) \ n n > ----------------------------- = / 2 %pi j ==== sin (z + -----) j = 1 n 2 n sin((n - 2) z + y + x) n sin(z - x) sin(z - y) ------------------------ + ------------------------, sin(n z) 2 sin (n z) which is sort of interesting for the n^2. I wish to thank Mma 7 for being no help at all simplifying the RHSs (which were initially mucho messy), thus making me feel useful. (A package to do these seems feasible, but decent simplifying would be ursine.) --rwg

2 1

[math-fun] what is a "geometric space"?
by Fred lunnon 22 Feb '10

22 Feb '10

While recently attempting to compile a glossary of terms related to geometric algebra, I found myself in several places wanting to use the term "geometric space". Finally, of course, it too needed to be defined: at which point it became embarrassingly clear that I am unable to do so. The spaces/ symmetry groups with which GA is principally concerned are pretty standard: e.g. Euclidean, inversive/ conformal/ Moebius, spherical/ orthogonal, equilong/ Laguerre, contact/ Lie-sphere, 3-space line geometry. The constraints of the formalism actually restrict these to "quadratic space" --- but what I have in mind is more general than that --- for instance, geometry of lines in n-space (involving multiple quadratics). Conventional classifications based on topology, such as "metric space" --- as Dan Asimov recently pointed out --- rely on point-based notions which simply fail to apply in most of the cases above, even the familiar inversive geometry, because the "metric" (a quadratic form) is indefinite. Geometry of points on a real manifold bears some relation to what's required: but that relies on too much prior machinery for comfort. And the mind-numbingly abstract approach of "schemes", "stacks" and their like opens a whole counter-intuitive can of worms, which I have no intention of attempting to broach. So, I need a helping hand here, folks --- can somebody please come up with simple, elementary, intuitively plausible definition for what I am trying to discuss here? Fred Lunnon

1 1

Re: [math-fun] Graph question
by Steve Witham 21 Feb '10

21 Feb '10

>From: "David Wilson" <davidwwilson(a)comcast.net> > >Each edge is a corridor, each vertex a circular room. Corridors enter at >doors in fixed positions around the room, so that you can tell which >corridor is left or right of another with respect to the vertex. >From: Fred lunnon <fred.lunnon(a)gmail.com> > >It's a very nice problem --- tho' I can't help wondering if it's already >been tackled --- by Erdos et al, perhaps? Don't know the Erdos number, but in season 4, episode 23, "The House That Jack Built", of _The Avengers_ TV show, which is just about the best episode of any TV show EVAH, Emma Peel tackled the problem in a different way. ======== SPOILERS ========= Weep dorp. Humm humm humm.... Wa-deep dorp. Humm humm humm.... Weep dorp. Humm humm humm.... Wa-deep dorp. Humm humm humm.... Weep dorp. Humm humm humm.... Wa-deep dorp. Humm humm humm.... Weep dorp. Humm humm humm.... Wa-deep dorp. Humm humm humm.... After a bit of frantic maze-running, Mrs. Peel cooled her heels and drew a lipstick X on the clear cover of the creepy beeping machine in the center of the circular room. When she got to the room at the end of the first corridor, there was the X. This led her to realize (before punching through a wall and showing us the audience) that the corridor was on a track so that either end could be automatically wheeled around to the room, producing a crazy- making virtual infinite lattice effect. Of course the resourceful Mrs. Peel is not so easily driven crazy, rather the point of the episode. http://theavengers.tv/forever/peel1-23.htm Thumbnail of the room: http://tinyurl.com/y8zfbfh --Steve

1 0

[math-fun] first calculate 0.85250246642742 and then Google it
by wouter meeussen 20 Feb '10

20 Feb '10

Yes, the wrong way round. But only after calculating the Statistical Process control parameters "d3" during 15 days, could I use the results to Google for it. I found that I wasn't the first to be frustrated by the 'generally accepted' 3-digit precision in SPC-tables like http://www.ct-yankee.com/spc/ and I wanted to know more, just like http://en.wikipedia.org/wiki/User:NorwegianBlue/refdesk/statistics Had I only found this site during my hours of Google-searching *before* doing the ugly double integral! Anyway, let's say that numerical accuracy to about 10 digits should suffice to uniquely make a floating point constant Googlable. Similar to what Simon Plouffe aspired with smaller hardware in his 'invertor'. http://bootes.math.uqam.ca/cgi-bin/ipcgi/lookup.pl?Submit=GO+&number=0.8525… Wouter. http://users.telenet.be/Wouter.Meeussen/Statistical%20Process%20Control%20d…

2 2

Re: [math-fun] Graph question solution.
by Andy Latto 19 Feb '10

19 Feb '10

On Thu, Feb 18, 2010 at 8:11 AM, David Wilson <davidwwilson(a)comcast.net> wrote: > Let a building be constructed in the form of a finite undirected connected > graph with no other special properties (e.g, not necessarily loopless or > planar). > > Each edge is a corridor, each vertex a circular room. Corridors enter at > doors in fixed positions around the room, so that you can tell which > corridor is left or right of another with respect to the vertex. > > You are placed in a random corridor in this building. You are allowed to > walk through the building under the restriction that you leave a room at a > door left or right of the one which you entered. Rooms and corridors may be > identical, so you cannot hope to recognize any room or corridor you have > previously visited, nor are you allowed to mark rooms or corridors. > > Your task is to visit every room in the building. Is there a fixed sequence > of left-right choices that will allow you to do this, regardless of the > building configuration? Yes. Proof sketch: Lemma 1. For any such graph, and any starting location, there is a sequence of left-right choices that visits every room. (This was trickier than I would have thought to prove; in particular, it needs to make essential use of the finiteness of the graph). Lemma 2. For any such graph and any such starting point, the measure of the set of all infinite LR sequences that visit all rooms is 1. Since there are a countable number of (Graph, starting point) pairs, the measure of the set of all infinite LR sequences that visit all rooms in all finite connected graphs is 1. So not only does there exist such a sequence, you have to work hard (or be infinitely unlucky) to find a sequence which is *not* one of the ones you're looking for! This shows that there is a finite such sequence that works for any finite collection of graphs and starting points, such as the set of all graphs with less than N vertices. It seems like there should be a proof of this purely combinatorial fact that doesn't rely on infinite sequences and measure, but finding one is annoyingly difficult. Andy

2 1

Re: [math-fun] Graph question solution.
by Michael Kleber 18 Feb '10

18 Feb '10

You can just use countability here, can't you? Enumerate the (graph, starting point) pairs, then build the universal sequence U by, for each pair, adding the LR sequence you'd use to visit all rooms starting from that point after doing everything already in U. --Michael On Feb 18, 2010 11:51 AM, "Andy Latto" <andy.latto(a)pobox.com> wrote: On Thu, Feb 18, 2010 at 8:11 AM, David Wilson <davidwwilson(a)comcast.net> wrote: > Let a building be constructed in the form of a finite undirected connected > graph with no other special properties (e.g, not necessarily loopless or > planar). > > Each edge is a corridor, each vertex a circular room. Corridors enter at > doors in fixed positions around the room, so that you can tell which > corridor is left or right of another with respect to the vertex. > > You are placed in a random corridor in this building. You are allowed to > walk through the building under the restriction that you leave a room at a > door left or right of the one which you entered. Rooms and corridors may be > identical, so you cannot hope to recognize any room or corridor you have > previously visited, nor are you allowed to mark rooms or corridors. > > Your task is to visit every room in the building. Is there a fixed sequence > of left-right choices that will allow you to do this, regardless of the > building configuration? Yes. Proof sketch: Lemma 1. For any such graph, and any starting location, there is a sequence of left-right choices that visits every room. (This was trickier than I would have thought to prove; in particular, it needs to make essential use of the finiteness of the graph). Lemma 2. For any such graph and any such starting point, the measure of the set of all infinite LR sequences that visit all rooms is 1. Since there are a countable number of (Graph, starting point) pairs, the measure of the set of all infinite LR sequences that visit all rooms in all finite connected graphs is 1. So not only does there exist such a sequence, you have to work hard (or be infinitely unlucky) to find a sequence which is *not* one of the ones you're looking for! This shows that there is a finite such sequence that works for any finite collection of graphs and starting points, such as the set of all graphs with less than N vertices. It seems like there should be a proof of this purely combinatorial fact that doesn't rely on infinite sequences and measure, but finding one is annoyingly difficult. Andy _______________________________________________ math-fun mailing list math-fun(a)mailman.xmission.com http://mailman.xmission.com/cgi-bin/mailman/listinfo/math-fun

1 0

[math-fun] Graph question
by David Wilson 18 Feb '10

18 Feb '10

Let a building be constructed in the form of a finite undirected connected graph with no other special properties (e.g, not necessarily loopless or planar). Each edge is a corridor, each vertex a circular room. Corridors enter at doors in fixed positions around the room, so that you can tell which corridor is left or right of another with respect to the vertex. You are placed in a random corridor in this building. You are allowed to walk through the building under the restriction that you leave a room at a door left or right of the one which you entered. Rooms and corridors may be identical, so you cannot hope to recognize any room or corridor you have previously visited, nor are you allowed to mark rooms or corridors. Your task is to visit every room in the building. Is there a fixed sequence of left-right choices that will allow you to do this, regardless of the building configuration?

1 0