# IRR and NPV in Mathematica, the modern way

Back in 2010 I posted an explanation of how to compute net present values and internal rates of return in Mathematica. It worked but it was a little verbose. Considering that Mathematica can do things like optical character recognition with a single command, it seemed a shame that it couldn’t do these basic financial functions.

Wolfram has fixed this, and computing IRR and NPV in Mathematica is now much more civilized.

Using the same sample cash-flow as last time (but reformatting the date to list-format, as Mathematica’s Cashflow[] function demands for some reason),

`testFlow = {{{2001, 1, 1}, -1}, {{2002, 1, 1}, .3}, {{2005, 1, 1}, .4}, {{2005, 1, 10}, .5}};`

``` ```

`TimeValue[Cashflow[testFlow], .05, {2001, 1, 1}]`

returns an NPV of `.0256519`, just as it should. And

`FindRoot[TimeValue[Cashflow[testFlow], r, {2001, 1, 1}] == 0, {r, .05}]`

gives us the IRR, in this case `.0584316`.

Mathematica clearly performs a compilation the first time my NPV is run, and while the native NPV and IRR computations are quicker than mine the first time one of them is run by about a factor of 50, the second time one of them is run (whether on the same data or not) my code is quicker than theirs by about 30%. Mine is also ecumenical about date formats, but in practice I find myself using their code anyway, as it is so concise.

# The Wolfram Language is Here?

Stephen Wolfram released a formal introduction to The Wolfram Language two days ago but I am watching it only now.

The demonstration answers a question I had had since first hearing about this language — How is it different from Mathematica? The answer seems to be that this is the Mathematica programming language, IDE, and APIs, but conceptually separated from the Mathematica environment and given its own name. Thus, those of use who used to say we “programmed in Mathematica” will now say we “program in the Wolfram Language,” and the code we create in Mathematica can also be run in other environments (in web browsers, on embedded devices, etc.).

It’s nice to hear Wolfram credit this as a learning language; many people regard Mathematica as complex and obscure, but the reality is that for anybody who can handle leaving the world of imperative and object-oriented programming, it is extremely easy to learn, and to use and it’s extremely flexible. Separating the language from Mathematica may encourage novices to try it.

Wolfram doesn’t announce a release date in the video but I suspect it will be simultaneous with the release of Mathematica 10, which may be very soon.

# Bloomberg to Mathematica

Several years ago, my team and I created a tool for importing data via the Bloomberg API directly into Mathematica. It works perfectly and we made both the code and the executable interface available via the Wolfram library. Apparently, Wolfram has removed it, perhaps because it competes with identical functionality in the Wolfram Finance Platform. But the Bloomberg to Mathematica link is free, and a Wolfram Finance Platform license costs \$10,000, so people have asked me to put this online somewhere.

So here it is.
[2016 update — I’ve removed the old link as the executables and source code there were outdated. Get the current version at GitHub]

You need to have a Bloomberg Professional license, and as the Bloomberg data API works only on Windows, you need to be running Bloomberg and Mathematica on Windows (I’ve tested this in Windows XP through 8, and with versions of Mathematica through 9.0. Let me know if you run into problems with other setups).

The source code is included here; feel free to make modifications if you like. If you do anything interesting please share it back, it would be nice to see Mathematica used more widely in finance, and free sharing of tools like this would have to help.

Update, 2015:
I have added a new interface notebook for those using Mathematica 10. It fixes some bugs, increases flexibility, and most importantly, takes input and produces output using Mathematica’s new date and temporal data types, and using an Association rather than a list. It’s cleaner and more robust than the old method (though the old notebook will continue to work for those afraid of change.) If you upgrade from the old interface to the new one, note that all the functions have changed names, and their default installation path has as well. It’s all documented in the new notebook in the zip file above.

(Sample use of the old code shown below)

```cpi2 = bbg2Mdates[ BBGetNF1["JNCPIYOY Index", "Px_Last", "19820101", "", "monthly"]];```

```tpxNorm2 = SternChangeCharNF1[ bbg2Mdates[ SternNormalizeNF1[ BBGetNF1["TPX Index", "Px_Last", "19820101", "", "monthly"]]], name -> "TPX"];```

```Show[DateListPlot[cpi2[[2]], Frame -> {True, True, False, False}, Joined -> True, Filling -> 0, FillingStyle -> {Blue, White}, GridLines -> False, PlotRange -> All, PlotStyle -> Purple], DateListPlot[tpxNorm2[[2]], Frame -> {True, True, False, False}, Joined -> True, GridLines -> False, PlotStyle -> Pink], PlotLabel -> "日本 inflation has been low since \!\(\* StyleBox[\"before\",\nFontSlant->\"Italic\"]\) the market crashed", Axes -> {True, False}, PlotRange -> All, Epilog -> {(Text[ Style["stock market", FontFamily -> "Times New Roman", FontSize -> 10, Pink], {AbsoluteTime["6/1/1986"], 4.4(*vertical position*)}, {-.01, 0}(* how close to the end *)]), (Text[ Style["CPI", FontFamily -> "Times New Roman", FontSize -> 10, Purple], {AbsoluteTime["1/1/1983"], 3.6(*vertical position*)}, {-.01, 0}(* how close to the end *)])} ]```

A demonstration of the new code can be found at http://www.wheels.org/monkeywrench/?p=817

[ Update March 7, 2016: New source code and executables are available on Github, at https://github.com/MichaelSternNYC The Win32 binaries and instructions for use can be found at https://github.com/MichaelSternNYC/bloomberg-to-mathematica

If you want to load Bloomberg data into a version of Mathematica older than 10.0, contact me and I can send you an older release. ]

# The most worthless subway stations in New York, part ii

In the last post, I demonstrated loading a GIS shapefile for New York City into Mathematica, loading the official subway station entry data from the city and overlaying it onto the map.

Below is the code to prepare and display an interactive version of the tool that also identifies and displays the stations on each line closest to each other and most remote.

```preStationsForComp = Drop[stations, 1][[ All, {3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 26, 25}]]; preStationsForComp[[All, 13]] = preStationsForComp[[All, 13]]/1000000.; preStationsForComp[[All, 14]] = preStationsForComp[[All, 14]]/1000000.;```

``` stationsForComp = Union[Map[{#[[1]], #[[2]], #[[3]], #[[4]], #[[5]], #[[6]], #[[7]], #[[ 8]], #[[9]], #[[10]], #[[11]], #[[12]], {#[[13]], #[[14]]}} &, preStationsForComp], SameTest -> ((#1[[1]] == #2[[1]]) && (#1[[2]] == #2[[2]]) && (#1[[ 3]] == #2[[3]]) && (#1[[4]] == #2[[4]]) && (#1[[5]] == #2[[ 5]]) && (#1[[6]] == #2[[6]]) && (#1[[7]] == #2[[7]]) && (#1[[ 8]] == #2[[8]]) && (#1[[9]] == #2[[9]]) && (#1[[10]] == #2[[ 10]]) && (#1[[11]] == #2[[11]]) && (#1[[12]] == #2[[12]]) &)]; (* one randomly selected entrance per station/line *) entranceFromLine[line_] := Select[stationsForComp, (#[[2]] == line || #[[3]] == line || #[[4]] == line || #[[5]] == line || #[[6]] == line || #[[7]] == line || #[[8]] == line || #[[9]] == line || #[[10]] == line || #[[11]] == line || #[[12]] == line) &][[All, {1, 13}]] entranceFromSimplifiedData[line_, station_] := Select[stationsForComp, (#[[1]] == station && (#[[2]] == line || #[[3]] == line || #[[4]] == line || #[[5]] == line || #[[6]] == line || #[[7]] == line || #[[8]] == line || #[[9]] == line || #[[10]] == line || #[[11]] == line || #[[12]] == line)) &] ```

```Manipulate[ Module[{key, closestations, farstations, lineEntrances, nearstationPair, farstationPair, closestToPointer}, key = Select[index, #[[2]] == whichline &][[1]][[1]]; closestations = First[screenedVitalDistances[[key]][[2]]]; farstations = Last[screenedVitalDistances[[key]][[2]]]; nearstationPair = {entranceFromSimplifiedData[whichline, closestations[[1]][[1]]][[1]], entranceFromSimplifiedData[whichline, closestations[[1]][[2]]][[ 1]]}; farstationPair = {entranceFromSimplifiedData[whichline, farstations[[1]][[1]]][[1]], entranceFromSimplifiedData[whichline, farstations[[1]][[2]]][[1]]}; lineEntrances = entranceFromLine[whichline]; closestToPointer = First[SortBy[Map[{#, GeoDistance[#[[2]], p]} &, lineEntrances], Last]]; Column[{Show[bg, Graphics[{PointSize[Medium], Blue, Table[Tooltip[Point[lineEntrances[[i]][[2]]], lineEntrances[[i]][[1]]], {i, 1, Length[lineEntrances]}]}], Graphics[{PointSize[Large], Red, {Tooltip[Point[Last[farstationPair[[1]]]], First[farstationPair[[1]]]], Tooltip[Point[Last[farstationPair[[2]]]], First[farstationPair[[2]]]]}}], Graphics[{PointSize[Medium], Orange, {Tooltip[Point[Last[nearstationPair[[1]]]], First[nearstationPair[[1]]]], Tooltip[Point[Last[nearstationPair[[2]]]], First[nearstationPair[[2]]]]}}], Graphics[{PointSize[Large], Black, {Tooltip[Point[Last[closestToPointer[[1]]]], First[closestToPointer[[1]]]]}}], PlotRange -> {{-74.05(*w*), -73.69(*e*)}, {40.54(*s*), 40.92(*n*)}}, ImageSize -> 340, ImagePadding -> 2], Style[TableForm[{closestations, farstations, {closestToPointer[[1]][[1]], closestToPointer[[2]]}}, TableHeadings -> {{"closest pair", "farthest pair", "closest to pointer"}, {"stations", "distance (m)"}}], FontFamily -> "Times"]}, Center, 3]], {{whichline, "F", "which line?"}, index[[All, 2]]}, {{p, {-73.87, 40.73}}, Locator}] ```

In our next installment, what are the most distant stations in the whole system?

# What are the most worthless subway stations in New York?

I was recently riding on the subway and it ocurred to me that some stations are much, much closer together than is typical. You can ride 30 blocks from West 42nd Street to West 72nd in a straight shot on the 2 or 3 lines, or 39 blocks between East 86th Street to East 125th Street on the 4 or the 5, but then sometimes you’re stuck mosying along between 110th and 116th, or between 14th and 18th. I heard a comedian make a joke about the pointlessness of stations four blocks apart, years ago. Nobody laughed, but I think he was onto something — some of these stops are very close together.

But how close? Scads of GIS files about New York City are freely available, and Mathematica has had the native ability to deal with these since version 7, though I’ve never used these functions. Let’s see what we can find out about the New York City Subways.

I used borough outlines from http://www.baruch.cuny.edu/geoportal/data/nymag/ and subway entrance locations from http://mta.info/developers/sbwy_entrance.html. I found three potentially tricky elements to the calculation \[LongDash] first, the subway entrances are not necessarily centered around their respective subway platforms; I chose a single entrance arbitrarily for each station but if I chose an outlying one, it will introduce a small error in my result. Second, the subway entrace locations are in {latitude,longitude} format (actually, {latitude,longitude}*1,000,000), and this is the format expected by Mathematica’s GeoDistance[] function, but the basemap is formated in {longitude, latitude} format, so we have to reverse the order of the elements in each location when we switch between mapping and determining distances. Third, the city’s database of subway entrances identifies an entrance as pertaining to a given line even if it is connected to that line only by an underground pedestrian tunnel. Since we care about what the trains are doing only, these have to be screened out by hand.

`stations = Import[NotebookDirectory[] <> "StationEntrances.csv"];`

``` ```

```allLines = Union[Flatten[Drop[stations, 1][[All, Range[4, 14]]]]]; allLines = Select[allLines, # != "" &]; index = Table[{i, allLines[[i]]}, {i, 1, Length[allLines]}]; ```
This function lets us extract all subway stations pertaining to a given line.

```matchLine[line_] := Union[Select[ Drop[stations, 1], #[[4]] == line || #[[5]] == line || #[[6]] == line || #[[7]] == line || #[[8]] == line || #[[9]] == line || #[[10]] == line || #[[11]] == line || #[[12]] == line || #[[13]] == line || #[[14]] == line &], SameTest -> (#1[[3]] == #2[[3]] &)]```

``` ```

`byLineUnique = Map[matchLine[#] &, allLines];`

Let’s graph it to see if this looks reasonable. Note that I specify the PlotRange to eliminate Staten Island, which has no subways.
``` bg = Import[NotebookDirectory[] <> "nymag_nyc_geog/nyc_pumas_2008.shp"];```

``` ```

```Show[bg, Graphics[{PointSize[Medium], Red, Point@(Map[Reverse, Drop[stations[[All, {25, 26}]], 1]/1000000.])}], PlotRange -> {{-74.05(*w*), -73.69(*e*)}, {40.54(*s*), 40.92(*n*)}}]```

Subway entrances as rendered by Mathematica

Looks good to me. We could easily graph each line in a difference color, connect the dots with lines, etc., but this will do for now.

The station entrance data includes all sorts of things we don’t care about, so let’s simplify it.

```vitalTable = Table[{index[[i]][[2]], Map[{#[[3]], {#[[26]], #[[25]]}/10^6} &, byLineUnique[[i]]] // N}, {i, 1, Length[byLineUnique]}];```

For the moment I don’t care about the order the stations are in, I’m going to check every station against every other one.

```vitalDistances = Table[{index[[i]][[2]], SortBy[Union[ Map[{#[[All, 1]], Round[GeoDistance[Reverse[#[[1]][[2]]], Reverse[#[[2]][[2]]]], .1]} &, Permutations[vitalTable[[i]][[2]], {2}]]], Last]}, {i, 1, Length[byLineUnique]}];```

Below is a list of subway stations combinations that aren’t traversed by the trains themselves and therefore shouldn’t be counted as “too close”. We’ll screen these out.

```disallowedCombos = {{"14 St", "6 Av"}, {"South Ferry", "Whitehall St-South Ferry"}, {"Chambers St", "Park Place"}, {"Atlantic Av", "Atlantic Av-Pacific St"}, {"Brooklyn Bridge-City Hall", "Chambers St"}, {"Franklin Av", "Botanic Garden"}, {"Botanic Garden", "Franklin Av"}, {"59 St", "Lexington Av/59 St"}, {"51 St", "Lexington Av/53 St"}, {"74 St-Broadway", "Jackson Heights-Roosevelt Av"}, {"14 St", "8 Av"}, {"62 St", "New Utrecht Av"}, {"Park Place", "World Trade Center"}, {"42 St-Bryant Pk", "5 Av"}, {"Lorimer St", "Metropolitan Av"}, {"Court Sq", "Court Sq-23 St"}, {"42 St-Port Authority Bus Terminal", "Times Sq-42 St"}, {"Chambers St", "World Trade Center"}, {"Borough Hall", "Court St"}};```

``` screenedVitalDistances = Table[{index[[i]][[2]], Select[SortBy[ Union[vitalDistances[[i]][[2]], SameTest -> (#1[[1]] == Reverse[#2[[1]]] &)], Last], ! MemberQ[disallowedCombos, #[[1]]] &]}, {i, 1, Length[byLineUnique]}]; ```

``` Map[{#[[1]], First[#[[2]]][[1]], First[#[[2]]][[2]], Last[#[[2]]][[1]], Last[#[[2]]][[2]]} &, screenedVitalDistances]```

The stations closest to each other and farthest from each other for each subway line in New York.

In the next post, I will demonstrate an interactive tool (which I have also uploaded to the Wolfram Demonstrations Project) that shows all the stations for a given line, marking the closest ones in green and the farthest ones in red, with tooltips identifying every station.