Meet The Spreadsheet That Can Solve NYC Transit (and the Man Who Made It)

For the last 60 years, at the very least, New York City has been a pain to get around. It consistently ranks as one of the worst cities in the country for gridlock. The legendary public official Robert Moses re-built New York’s transportation in the middle of last century for cars. If that solution ever truly worked, it certainly doesn’t now. There are too many of them. And now, the subway system, once the crown jewel of urban transit in America, has deteriorated over time thanks to neglect and mismanagement.

In 2008, then-mayor Michael Bloomberg proposed a $8 congestion charge levied on all drivers south of 60th Street in Manhattan, known as the Central Business District (CBD), to reduce traffic and help fund public transit improvements. This wasn’t a crazy idea. London had instituted a similar fee in 2003 with great success: 15-20 percent boosts in bus speeds and 30 percent reductions in congestion. Singapore and Stockholm also have congestion pricing structures. But in New York, the proposal died in the state legislature.

Now, for the first time since 2008, with the subway in the sorriest state it’s been in since the 1980s, congestion pricing is back. In August, Governor Andrew Cuomo announced his support for the concept, calling it “an idea whose time has come.” In October, he appointed a panel to study it. And, like an ancient alien buried in the ground waiting for a signal, an energy policy analyst named Charles Komanoff got back to work.

As the last few years rolled by with nary a peep about congestion pricing, Komanoff put aside his project which, he believes, is the key to unlocking New York’s transportation potential. It is a giant, 5.2 megabyte spreadsheet that he’s spent the better part of a decade developing. And now, he hopes, its time has come.

*

The spreadsheet is called the Balanced Transportation Analyzer, or BTA. It has 72 separate worksheets, many of which contain over a thousand rows and dozens of columns. Komanoff made the spreadsheet for a single purpose: to be the most comprehensive accounting possible of how a congestion charge in Manhattan would affect New York City.

Komanoff, sitting in his office in Manhattan’s financial district sporting his cycling skull cap because of the building’s under-functioning heating system, explained the BTA’s origins while he cut up cardboard boxes for recycling. The spreadsheet was born in 2007 when Ted Kheel, a labor lawyer and “fierce advocate of mass transit,” as Komanoff once wrote, hired him to answer one question: Could congestion pricing generate enough revenue to make mass transit free? By that fall, Komanoff believed he found the answer. A charge of $16 applied to drivers 24/7 could fund free buses and subways. For everyone. Always.

Charles Komanoff. Image: Doug Goodman

But Komanoff, who has math and economics degrees from Harvard, wasn’t satisfied, especially when someone told him he had made an error. “I was sort of naive,” Komanoff said as he sliced through the cardboard, “and I didn’t understand how amazingly intricate and fragile the whole funding structure is for public transit.” After Kheel’s death, in 2010, his family continued funding the project.

*

Komanoff’s underlying premise is that every effect congestion pricing has can be quantified in dollars. By monetizing absolutely everything, from saved time to health benefits to environmental impact, Komanoff can show which plan is best and, more importantly, why.

The name for this behemoth spreadsheet was Kheel’s idea. His main point, Komanoff recalled, was that it had to have the word “balanced,” which Kheel believed was the key to good urban transportation policy. Komanoff liked that.

One of the BTA’s early insights, which Felix Salmon wrote about for Reuters in 2009, was just how unbalanced New York’s transit system currently is. Salmon wrote, “Driving a car into Manhattan on a weekday causes about $160 of negative externalities to everybody else.”

The BTA is Komanoff’s attempt to show transit balance is possible in the most comprehensive manner. Each of the 72 worksheets contributes a different element to the overall calculation. One estimates the change in traffic patterns for weekdays, another for weekends. One estimates the impact the charge will have by county. Others break down the impact on trucks and taxis.

Then there are the worksheets that deal with public transit, parking placards, and so on. Of the BTA’s 72 worksheets, two are for the impact of FreshDirect delivery trucks. One even accounts for the effect horse-drawn carriages around Central Park have on traffic congestion. Most of those worksheets, though, function like the developer tools tab in Chrome; you can open it and inspect the source code if you want, but it is by no means necessary.

Instead, there are two worksheets anyone opening the BTA will use: “User Inputs” allows anyone to adjust 44 different factors that impact who is charged what under a hypothetical congestion pricing scheme. How much do you want the congestion charge to be? Do you want to adjust the bridge and tunnel tolls? What about trips through the congestion zone? Do you want to waive the bridge toll for the first trip of month for E-ZPass holders within MTA counties (it’s OK if you don’t know what that means)? How much do you want the subway and bus fares to be? What about the two commuter rail lines, Metro-North and LIRR? All fare and toll options, by the way, can be adjusted by time of day and day of the week.

Once the user has adjusted everything to their liking, they head over to the Cost-Benefit worksheet, which is more or less the BTA’s results page. Here, the user can see how their plan impacts 24 different factors ranging from the environment, saved time, and “wellness,” translated into the All Mighty Dollar. It even includes how the congestion charge will affect bus boarding times, noise, taxi hail time, air quality, and safety. Everything is weighed into a single number, modestly titled “Net Benefit of Selected Scenario.”

Under the default plan loaded on the BTA, the MoveNY plan, congestion pricing results in a net benefit of $2.64 billion per year to the people of New York in the form of increased revenue, public transportation improvements, a healthier environment, better biking infrastructure, and saved time. Komanoff whipped out a calculator as we spoke and broke that down: $320,000 per hour, $84 a second.

In 2013, the MoveNY campaign hired HNTB Corporation, an engineering consultancy, to audit the BTA, since MoveNY relies on the BTA to model its proposals. While HNTB’s report found a few small issues which amounted to a two percent change in annual net revenue, its general conclusion was that the BTA is comprehensive, dynamic, transparent, and realistic. When comparing the BTA’s projections with the effects congestion pricing had on London, Stockholm, and Singapore, the report found the projections “consistent” with what was seen in those real-world examples.

“But don’t worry,” Komanoff said, “I don’t keep myself up at night thinking it’s bad because we’re losing $90. I’m up at night thinking fuck, this would be a great thing to do for the city because it would just make it a better place.” But knowing merely that it would be better isn’t enough. Instead, he gets fired up knowing “it would make it a better place in these ways.”

*

Looking at this worksheet, Komanoff pointed to one of the bars in the chart labelled “Travelers’ Amenity.” This, he says, is the main thing he wanted to show me.

After I wrote an article for this website in which I cited Komanoff’s research and argued, somewhat cheekily, the congestion charge to enter Manhattan should be $100, Komanoff got in touch to tell me I was wrong. It wasn’t balanced. Under the proposed MoveNY plan, approximately 15 to 17 percent of all car trips into the CBD, will no longer take place. But when the fee is jacked up to $100 under the Aaron Gordon Plan, a whopping 47 percent of trips are killed off.

Hell yeah, I said. What’s wrong with that?

Komanoff flipped over to the Cost-Benefit worksheet. The “Travelers’ Amenity” bar was literally off the chart. It had gone from negative-$200 million to almost $4 billion in the red. This bar, Komanoff told me, represented the people who will be priced out of driving. Think of it like a check on basic fairness.

“I’m trying to be fair and true,” he said of the Travelers’ Amenity calculation, since these are the people who stand to lose the most with a congestion charge, but also about the BTA in general. “I’m really trying to count everything.”

*

At one point early in the tour, Komanoff casually described the BTA’s development as an “iterative” process, which is an understatement. Komanoff has more than 2,000 versions saved on a hard drive. His big project right now is trying to incorporate Uber into the calculations—a far bigger factor in New York City transit in 2017 than in 2014—which involved reverse-engineering its fare structure using customer receipts.

After the hour-long introduction to the BTA, which Komanoff described as “the basics,” we decided to play around a bit more with the Aaron Gordon Plan. I noticed all the revenue gained from the $100 charge had not been re-invested in public transit, which, to me, was ignoring its main benefit. Can we adjust that?

Komanoff was clearly excited at the question. He took pride in how almost every parameter can be adjusted, a feature the HNTB audit praised for its transparency. This is what differentiates the BTA, which is freely available for download, from every paper or study on congestion pricing; he’s not merely saying it’s good, but giving everyone the chance to improve upon it. Everyone can find their own balance.

Still, there is a cost to this—a balance, if you will—that Komanoff knows full well. As one might expect with a gigantic Excel file, the BTA is not easy to use; the HNTB report generously called it “slightly cumbersome.” Unless you’re willing to sit down for hours and read through all the introductory tabs and have a pretty functional knowledge of Excel formulas, it will be little more than an incomprehensible jumble. Go ahead. Try it for yourself. See how long you keep the window open before giving up on it. The first time I opened the BTA, I made it two minutes.

At one point, I asked Komanoff is he’d ever considered converting the BTA into some kind of web app to make it accessible to more people. He expressed interest in the idea, but worried he wouldn’t be able to modify the model as easily. Moreover, he values the spreadsheet’s transparency and flexibility, which would be lost in a basic web platform. Of course, a gigantic spreadsheet with 72 worksheets and hundreds of thousands of cells has its own issues of opaqueness.

Komanoff, though, knows the spreadsheet too well to be too bothered by that. Back to our task of investing more heavily in public transit, he navigated to the exact cell he had in mind, even though it was on a completely different sheet, with only a few keystrokes: Transit, Row 193. The BTA estimates the subway gets 1.2 percent faster for every $100 million invested in it with diminishing returns of five percent for every additional $100 million, figures Komanoff pulled from an MTA report. Just for kicks, we jacked up the rate of return up to five percent and returned to the Cost-Benefit sheet.

“What the hell?!” Komanoff shouted. With this one, tiny change, the supposed net benefit of the congestion charge had become, in fact, a gigantic drain on the city, to the tune of several billion dollars a year.

“You know what happened?” Komanoff diagnosed. “I was counting as a benefit the unspent revenue, because society can do stuff with that. And, Jesus Christ, maybe I’m being too conservative!” He let out a chuckle. “This is MTA rathole!” he exclaimed, referring to the billions of dollars more the MTA tends to spend on mega-projects than other major transit authorities.

“Does your spreadsheet account for MTA rathole?” I asked incredulously.

“It just did!”

The spreadsheet was alive, accounting for things Komanoff didn’t even know it could account for. Ten years of teaching the spreadsheet, and now the spreadsheet was teaching him. Upon realizing what had happened, Komanoff chuckled again. “I haven’t had this much fun with the spreadsheet in a really long time.”