APR of Payday Loan


Quote of the Day

There are only two hard things in Computer Science: cache invalidation and naming things.

Phil Karlton. I worked on cache memory designs while at HP and I can attest to the difficulty of cache invalidation. Also, I have struggled with coming up with meaningful names for software objects. Neither task is simple.


Rep. Katie Porter (Wikipedia)

I watched a video (Figure 2) that shows Rep. Katie Porter (Figure 1) grilling the Director of the Consumer Financial Protection Bureau (CFPB) Kathy Kraninger in regards to the calculation of a payday loan's Annual Percentage Rate (APR). This blog post will show how to compute the APR for the payday loan example Rep. Porter attempts to get Ms. Kraninger to compute. I am not sure Ms. Kraninger knows exactly what APR is. The questioning is a bit uncomfortable because Ms. Kraninger is in no mood for a math exercise and Rep. Porter is not going to give up. Ms. Kraninger clearly is uncomfortable, but her discomfort is nothing compared to the discomfort experienced by a payday loan customer.

This exercise will compute the APR for a Rep. Porter's payday loan example of $200 principal with a $20 origination fee, 10% interest, and 14-day term. A finance person should be able to estimate this metric in their sleep.



APR is a standardized metric that represents the annualized cost of a loan, including fees and interest, to a borrower expressed as a percentage. It is not an interest rate because it includes fees and cannot be used to compute payments. Because the APR calculation is standardized, it provides a convenient way to compare the relative costs of different loans.

Rep. Porter Video

Figure 2 shows Rep. Porter interrogating the CFPB director.

Figure 2: Rep. Porter Grilling CFPD DIrector over APR.


APR Mathematical Definition

I will use Investopedia's formula for APR (Equation 1).

Eq. 1 \displaystyle APR=\left( {\frac{{Fees+Interest}}{{Principal}}} \right)\cdot \left( {\frac{{365}}{N}} \right)\cdot 100


  • Fees are any non-interest costs associated with the loan is considered a fee.
  • Interest is the interest paid on the principal.
  • Principal is the amount of money owed.
  • N is the loan payback period in days.


Figure 3 shows my calculation for the APR of Rep. Porter's payday loan example. The APR is 521%, which is a horrendous rate.

Figure 3: Payday Loan Calculation Example.


Payday loans are another example of financial services companies preying on the unknowing. A 521% APR is ridiculous. Unfortunately, I see this sort of thing all the time. I caught a broker for a major investment firm try to cheat my retired mother out of her retirement money by churning her portfolio – he had no business putting an 84-year-old's money into high-risk stocks. I have also seen insurance agents sell multiple small life insurance policies to maximize their sales commissions.  Again, clearly not for the benefit of their customers. I hope the CFPB eventually gets some enforcement teeth and some qualified management.

Posted in Financial | 2 Comments

US Government Management of Western Forest Lands


Quote of the Day

Vision is the bottleneck of talent. Most talent is wasted because people do not clearly know what they want. It’s not a lack of effort, but a lack of direction. There are many capable people in the world, but relatively few that focus on what matters.

James Clear. I see this problem EVERY SINGLE DAY.


Figure 1: Percentage of Western State Forest Lands Managed By the US Federal Government.

I am always looking for data analysis exercises for the young folks that I tutor online. While watching the coverage of the massive forest fires on the west coast of the US, I noticed that all the media were reporting that much of the forest land in these states are managed by the US government, which makes them responsible for managing these forests. I thought it would be a good exercise to show the students how to calculate the percentage of forest land that is managed by the US government.

As usual, Power Query is my tool of choice. You can download my workbook from here. I should mention that you will often see different percentages listed for federal government land management percentages. All the differences that I have seen have to do with how you define managed and the type of land. For this exercise, I am focused on forest lands because those are the ones subject to controversy about vegetation management.


What are Federally Managed Lands?

One of the first questions to answer is what are considered federally-managed lands. The task is complicated because there are so many different federal agencies that manage forest land. Also, numerous "local" entities manage forest land. For the purposes of this exercise, I have grouped management into four categories: federal, state, local, and private. The following list shows how I am mapping my four categories to the US Forest Service breakdown, which uses the eleven government categories listed below.

    • Federal
      • National Forest Service
      • National Park Service
      • Bureau of Land Management
      • Fish and Wildlife Service
      • Other federal entities
      • Department of Defense
    • State
    • Local Government
      • County and Municipal Government
      • Other local government
    • Private

Data Source

The US Forest Service maintains a data mart from which you can download Excel workbooks containing all sorts of information on the forests in every state. All of my information comes from this data mart. Simply click on the image of the state you want on the US map and the file will download.

The US Forest Service always prepares a one-page summary for the forests in each state. You can access these summaries here. The summaries are generated using the data from the data mart.


My analysis approach was simple:

  • Download forest data workbooks from the data mart for eleven western states.
  • Select the forest area tabs from each workbook (SR002) and turn the data into an Excel table.
  • Bring all the tables into Power Query.
  • Write a function to convert all the tables to show the percentage of land managed by each entity.
  • Group the data into my four categories.
  • Generate a table of the state data.
  • Plot the table

The plot is shown in Figure 1. My processed, cross-tabulated data table is shown below.

Table 1: Federal Ownership Percentage of Western State Forest Land

Table of Data from US Forest Service Data Mart


I am floored to see that 96% of the forest land in Nevada is managed by the US government. Even California's 57% federal management is pretty high considering the state only manages 2.3% of the forest land.

Posted in Civics Through Spreadsheets, Excel | 3 Comments

Computing Bacteria Reproduction Rate and Doubling Time


Quote of the Day

A people that elect corrupt politicians, imposters, thieves and traitors are not victims... but accomplices.

George Orwell


Figure 1: Data Science Process (Wikipedia).

Figure 1: Data Science Process (Wikipedia).

Until the arrival of the coronavirus, I looked forward every week to volunteering at a local library as a tutor for university students. Now that COVID is raging around me, I have moved the tutoring online. Most of the students are training for some form of a medical career. This week a student presented me with bacterial growth data and was wondering how to estimate the growth rate and doubling time for the bacteria using Excel. This exercise nicely illustrates the entire data analysis process (Figure 1) in a single example and I decided to post my solution here.

I include my workbook here for those who are interested in following the analysis.


Bacterial Growth Characteristics

Figure 2: Bacterial Growth Phase (Wikipedia).

Figure 2 shows the logarithm of the bacteria count versus time (semi-log chart). The chart shows four growth phases:

  • Lag (label A)
    A period of minimal growth as the bacteria adapts to its new environment.
  • Log (label B)
    A period of exponential growth. On a semi-log plot, exponential growth plots as a straight line.
  • Stationary (label C)
    A period of no growth as the bacteria encounters growth limiting factors.
  • Death (label D)
    The bacteria die off from a lack of resources.

The analysis will focus on identifying the log phase and determining the slope of the line.

Bacterial Growth Measurement

Figure 3: Spectrophotometer

Because of the difficulty associated with counting individual bacteria, the data provided in this exercise comes from measuring the optical density of a bacteria sample  using a spectrophotometer (Figure 3). For this post, optical density is to be viewed as proportional to bacterial count. The details of estimating bacteria growth rate using optical density are a bit outside the scope of this blog post. For more information, see this website.

Bacterial Growth Data

Figure 4 shows the optical density data for samples of two types of bacteria (EC = Escherichia coli, SA = Staphylococcus aureus) at two different temperatures (30°C and 37°C).

Figure 4: Bacterial Growth Data from a Student's Lab Notebook.


Exploratory Data Analysis/Modeling

The student told me that she could only present one graph, so that graph had to perform multiple functions. Figure 5 shows my approach, which consists of:

  • Plotting every data point, with the log phase points darker than the rest.
    The region of linear growth is estimated by eyeball.
  • Fit an exponential function to the log phase points using Excel's trendline feature.
    An exponential function graphs as a line on a semilog plot. This line is shown as a thick bar. The equation of the line has the form N=A\cdot {{e}^{{m\cdot t}}}, where N = bacteria count, A = constant, m = exponential growth term, and t = time.
  • Each plot on the graph is labeled with its corresponding least-square fit exponential equation.

Figure 4: Graph of Data, Identification of Log Phase, and Exponential Curve Fits.

Doubling Time Estimate

The course assignment also wanted the bacteria doubling time (τ) calculated. Equation 1 shows the derivation of the relationship between doubling time and the exponential growth term (m).

Eq. 1 \displaystyle N=A\cdot {{e}^{{m\cdot t}}}
\displaystyle 2\cdot N=A\cdot {{e}^{{m\cdot \left( {t+\tau} \right)}}}
\displaystyle \frac{{2\cdot N}}{N}=\frac{{A\cdot {{e}^{{m\cdot \left( {t+\tau} \right)}}}}}{{A\cdot {{e}^{{m\cdot t}}}}}
\displaystyle 2={{e}^{{m\cdot \tau }}}
\displaystyle \therefore \tau =\frac{{\ln \left( 2 \right)}}{m}

Using Equation 1, we can compute the doubling times for the four test cases shown above.

Figure M: Bacteria Doubling Times.

Figure 5: Bacteria Doubling Times.


This bacterial growth problem provided a good example of how to apply Excel to a simple laboratory data analysis problem.

Posted in Excel, General Mathematics, Health, optics, Statistics | 2 Comments

Estimated Range Between Radios Based on RSSI


Quote of the Day

Something I wish I’d been reminded of when I was learning to code – just watching coding tutorials is like going to the gym just to watch someone else lift weights. It’s valuable to see how experts do it, but to actually build your coding skills—you’ve got to code!

Madison Kanna, software developer. I am a huge fan of her twitter feed and blog. I see many people watch Youtube videos and think that simply watching videos will make them proficient at complex tasks. Unfortunately, there is a huge gap between watching and doing.


Figure 1: Illustration of Radio Signal Spreading. (Source: Me)

Figure 1: Illustration of Radio Signal Spreading. (Source: Me)

I recently have been working on Bluetooth Low Energy (BLE) systems and estimating the distance between two devices based on the Received Signal Strength Indicator (RSSI) value is one of my tasks. There are all sorts of uses for this distance information in wireless systems.  A few years ago, I worked on one wireless product for department stores that would use customer distance and angle information to determine where a customer was in the store and where they lingered while they were shopping. This information can be used to assess the 'stickiness' of displays and to send messages to the customer's phone about nearby products they may be interested in.

These types of calculations are also performed in other applications. For example, I have done similar calculations with cell phone systems. While cell towers normally use GPS to determine phone positions, if GPS is not available they can use power-based range estimates to locate phone positions (example). This certainly is part of the E911 standard, which specifies emergency cell services in the US.

All these applications estimate range using some form of Equation 1.

Eq. 1 \displaystyle R={{10}^{{\frac{RSSI_{TdB}-RSSI_{RdB}}{{10\cdot N}}}}}


  • R is estimate range between the two radios (in meters).
  • N is the path-loss exponent (unitless, value of 2-4, with 2 being for free space). At most frequencies, N=2. In the case of 2.4 GHz, losses can be higher (link, link).
  • RSSITdB is received signal power level (in dBm) at 1 meter from the antenna.
  • RSSIRdB is received signal power level (in dBm) at R meters range.

This post will present a derivation of Equation 1.


Equation 1 is nothing more than the inverse square-law for electromagnetic waves with signal powers expressed in dB and the signal losses modeled with range powers from 2 to 4. We begin the derivation by restating the inverse square law for electromagnetic waves (Equation 2).

Eq. 2 \displaystyle RSSI_R=K\cdot \frac{{{{P}_{T}}}}{{{{R}^{N}}}}


    • K is a constant that will cancel out in further work.
    • RSSIR is the received power (W).
    • PT is the transmit power (W).

The range calculations do not normally use the actual transmitted power, but use the received transmit power at a reference distance (usually 1 meter), which is modeled by Equation 3.

Eq. 3 \displaystyle RSSI_T=K\cdot \frac{{{{P}_{T}}}}{{{{1}^{N}}}}

Let's now construct the ratio of Equation 3 to Equation 2 (Equation 4).

Eq. 4 \displaystyle \frac{{{RSSI_T}}}{{{RSSI_R}}}=\frac{{K\cdot \frac{{{{P}_{T}}}}{{{{1}^{N}}}}}}{{K\cdot \frac{{{{P}_{T}}}}{{{{R}^{N}}}}}}={{R}^{N}}

We can convert Equation 4 to dB as shown in Equation 5.

Eq. 5 10\cdot \log \left( \frac{RSSI_T}{RSSI_R} \right)=10\cdot \log \left( R^N \right)
\Rightarrow 10\cdot \log \left(RSSI_{T}\right)-10\cdot \log \left(RSSI_{R}\right)=10\cdot \log \left(R^N\right)
\Rightarrow RSSI_{TdB}-RSSI_{RdB}=10\cdot N \cdot \log \left(R\right)
\Rightarrow \frac{RSSI_{TdB}-RSSI_{RdB}}{10\cdot N }=\log \left(R\right)
\therefore R=10^{\frac{RSSI_{TdB}-RSSI_{RdB}}{10\cdot N}}

This derivation shows that Equation 1 is really just a slight reworking of the inverse square law.

Posted in Electronics, Physics | 4 Comments

Gravity on Von Braun Station


Quote of the Day

Late to bed, early to rise, work like hell, and advertise.

— Wernher Von Braun, a saying he kept on his desk. He was not going to repeat the mistakes of other rocket pioneers who shunned publicity.

Figure 1: Wernher von Braun. (Wikipedia)

Figure 1: Wernher von Braun. (Wikipedia)

I have been reading about Wernher von Braun's work with Disney on popularizing the idea of spaceflight with the public. He was a genius at marketing and a tireless champion for space flight. In addition to his television work, he also gave numerous speeches to any group that would listen. I smile as I think about my high‑school physics teacher beaming with pride when he spoke of von Braun arriving in a helicopter to speak to local physics teachers about space and flying to the moon. My physics teacher could not remember the details of what von Braun spoke about, but his entrance and exit in a helicopter made a huge impression on a town of 1800 people!

Figure 2: Collier's Magazine Cover. (Source)

In addition to his work with Disney on animating spaceflight, he also worked with the legendary space artist Chesley Bonestell on a Collier's magazine article that people still marvel at for its visionary portrayal of spaceflight (Figure 2). He really made spaceflight seem possible.

He even tried writing a novel about spaceflight called Project Mars: A Technical Tale, which is available for free from the Internet Archive. I read the book and it was a slog. I will say it had the most mathematics of any novel I have ever read. The appendix is intimidating.

The early work of von Braun put great emphasis on a space station that rotated to create artificial gravity, which he described in detail in the Disney video shown in Figure 3. This space station concept is often referred to today as von Braun Station. In the video, he says that the space station is wheel-shaped with a 200-foot diameter and that it rotates at 3 revolutions per minute. This is enough information for us to calculate the amount of gravity that the station's occupants will experience.

Figure 3: Von Baun Briefing on His Space Station Concept.

We can compute the level of artificial gravity generated by the space station using the equation for centrifugal acceleration under circular motion (Equation 1).

Eq. 1 \displaystyle {{a}_{c}}=\frac{{{{v}^{2}}}}{r}=\frac{{\overbrace{{{{{\left( {r\cdot \omega } \right)}}^{2}}}}^{{{{v}^{2}}}}}}{r}=\frac{{{{{\left( {r\cdot 2\cdot \pi \cdot f} \right)}}^{2}}}}{r}=4\cdot {{\pi }^{2}}\cdot r\cdot {{f}^{2}}


  • ac is the centripetal acceleration (artificial gravity).
  • r is the radius of the space station.
  • f is the rotation frequency.
  • ω is the angular frequency.

We can compute the level of artificial gravity as shown in Figure 3.

Figure 3: Calculation of Space Station Acceleration.

Figure 3: Calculation of Space Station Acceleration.

So this space station concept would generate ~30%⋅g. This level of gravity is similar to that of Mars, which has 38% of the surface gravity of Earth.

I should mention that a very limited experiment with artificial gravity was attempted with the Gemini 11 mission and the Agena docking target they were using for practice. The experiment generated only 0.0005 · g (link), but it was a successful test (note that some sources report 0.00015.· g).

Figure 4: Gemini-Agena Artifiicial Gravity Experiment.

Figure 4: Gemini-Agena Artificial Gravity Experiment.

We can confirm the level of gravity generated with the calculation shown in Figure 5.

Figure 5: Gemini Gravity Calculation.

Figure 5: Gemini Gravity Calculation.

Figure 6 shows a photo of the Agena tethered to Gemini 11.


Figure 6: Agena Tethered to Gemini 11.

Figure 6: Agena Tethered to Gemini 11. (NASA)

Posted in Space | 6 Comments

The Amazing de Havilland Mosquito


Quote of the Day

In 1940 I could at least fly as far as Glasgow in most of my aircraft, but not now! It makes me furious when I see the Mosquito. I turn green and yellow with envy. The British, who can afford aluminium better than we can, knock together a beautiful wooden aircraft that every piano factory over there is building, and they give it a speed which they have now increased yet again. What do you make of that? There is nothing the British do not have. They have the geniuses and we have the nincompoops. After the war is over I'm going to buy a British radio set – then at least I'll own something that has always worked!

Herman Göering, Germany’s wartime aviation minister (Source)


Figure 1: de Havilland Mosquito fighter-bomber.

Figure 1: de  Havilland Mosquito fighter‑bomber. (Wikipedia)

During the routine demolition of an old de Havilland Aircraft building in 2017, a treasure trove of 20K aperture cards (a microfilmed archive) was uncovered that contained plans for the Mosquito fighter-bomber (Figure 1). These plans have been lost since Mosquito production ended in  1950.

This discovery was a gold mine for WW2 aviation buffs. While nearly 8000 Mosquitos were produced, few have survived. Its wooden construction requires meticulous care to preserve and only four airworthy Mosquitos remain (link). Flying these old birds can be very dangerous, with two Mosquito pilots killed back in 1996 (link). The recovered plans are now being used to build a new Mosquito known as the "The People's Mosquito." With a bit of luck, a new Mosquito should be flying in a few years.

In this post, I will be looking at Mosquito production using Excel. For those who like to follow along, you can download my Excel workbook here. This workbook processes serial number data from a De Havilland historical web page.


Mosquito Role

Figure 2: Mosquito Production By Role.

Figure 2: Mosquito Production By Role.

The Mosquito was produced in the following variants:

In many ways, the Mosquito was the ultimate multi-role aircraft. Even though propeller-driven, it proved useful enough to the UK to remain in production until the end of 1950 (link).

Table 1 shows the production numbers by role. Night fighter and fighter-bomber were roles filled by 2/3 of the production.

Table 1: Mosquito Production By Role.
Category Quantity
Fighter-Bomber 3219
Night Fighter 1940
Bomber 1411
Photoreconnaissance 720
Trainer 421
Torpedo Bomber 66
Prototype 1
Grand Total 7778

As is often the case with WW2 data, different sources have different totals. The source used in this post shows 7778 units while other sources list 7781, a discrepancy of 3 units, which I view as minor.

Critical Woodworking Technologies

The Mosquito is a superb example of an aircraft design tailored to use the resources available, particularly those resources associated with wood construction. The Germans tried to build wooden aircraft because they were short of critical raw materials like aluminum. However, Germany's wooden aircraft attempts were unsuccessful in part because they lacked two key technologies that the UK had:

  • Resorcinol Adhesive
    The Mosquito's wooden members were glued together using resorcinol adhesive, which was an adhesive technology the German's did not have. The German's also tried to make wooden aircraft (He 162, Bachem Natter) but failed with one major reason being the lack of an adequate adhesive.
  • Plywood Laminated UsingResorcinol Adhesive
    The UK worked with US plywood manufacturers to develop plywood that was laminated using resorcinol glue. Prior to WW2, the US was manufacturing large quantities of marine plywood for use in the manufacture of civilian boats and wartime-production of PT boats and landing craft. De Havilland sought out US manufacturing that could build structural-grade plywood to their exacting specifications. They settled on a manufacturer in Wisconsin (link), who then went out and bought an estate loaded with virgin timber.


A key motivation for building the Mosquito out of wood was that it would allow the UK to use its well-trained woodworking population to work on a strategic need. The Mosquito's design was robust enough that it could be built at nine sites in three different countries. Table 2 shows a summary of where the Mosquitos were assembled and in what quantities.

Table 2: De Havilland Mosquito Manufacturing Sites.
Country Production Site Quantity
UK 6433
Hatfield 3309
Leavesden 1576
Standard Motors 1066
Percival 245
Airspeed 122
Chester 65
Hatfield: Chester 50
Canada 1133
Toronto 1133
Australia 212
Bankstown 212
Grand Total 7778

Figure 3 shows Mosquito production by year during WW2. Wartime production was 5938 (source). Note that some sources list higher production figures during WW2 (example: 6710). I have defined WW2 production as terminating on VJ Day, so 1945 was a shortened production year.

Figure M: Wartime Mosquito Production By Year.

Figure 3: Wartime Mosquito Production By Year.

Many of the structural components were assembled by furniture manufacturers at High Wycombe from imported plywood (link).

Figure 4 shows a good video on how the Mosquito was manufactured, with much of the aircraft being made from wood.

Posted in History Through Spreadsheets, Military History | 3 Comments

USNS Comfort Bed Count Relative To Other US Hospitals


Quote of the Day

The more a man rises in leadership the more responsible he is not just for what he says but also for what people hear.

General Michael Hayden

Figure 1: USNS Comfort is now berthed in New York City. (Wikipedia)

Because of COVID-19, I am currently sheltering-in-place at my cabin in northern Minnesota. I was reading news reports of the USNS Comfort docking in New York City to provide the city with another 1000 bed hospital. I had no idea as to the average size of a hospital and decided to take a quick look at the number of beds in hospitals around the United States using a histogram.

Fortunately, the Department of Homeland Security (DHS) makes an enormous amount of US infrastructure data available online at the Homeland Infrastructure Foundation-Level Data (HIFLD) website.  On this webpage, you can find details on all the hospitals in the US. As always, there are issues with the data. For example, while it only has bed counts for 6919 hospitals out of the 7581 hospitals in its list. My graph will filter out the hospitals that have no bed count data, which are listed in the file as having -999 beds. For those who are interested in looking at my Excel workbook, it is available here.

The government database lists the following types of hospitals in the US:

  • Children's
  • Chronic Disease
  • Critical Access (rural hospitals)
  • General Acute Care
  • Long-Term Care
  • Military
  • Psychiatric
  • Rehabilitation
  • Special
  • Women's

I am only interested in the General Acute Care and Critical Access types of hospitals, of which there are 5263. These are the hospitals that would bear the brunt of caring for COVID-19 victims. Figure 2 shows the number of US hospitals by bed count.

Figure 2: US Hospital Bed Count Distribution.

Figure 2: US Hospital Bed Count Distribution.

Here is what Figure 2 tells me that:

  • USNS Comfort's 1000 beds put her in the top 0.6% of hospitals in the US by bed count. It is a major hospital by any measure.
  • The bulk of US hospitals (~71%) have less than 200 beds.
  • 90% of US hospitals have less than 400 beds.

I should mention that the USNS Comfort has a sister ship called the USNS Mercy, which is the lead ship of the Mercy class of hospital ships. Both ships are converted supertankers originally built to service the trans-Alaska pipeline.

Posted in Civics Through Spreadsheets, Excel | 3 Comments

Maple Sap is Sweet This Year


Quote of the Day

Statistics are like brief swimming costumes; what they reveal is interesting, but what they conceal is absolutely vital.

— old statistics professor

Figure 1: My Neighbor's Maple Syrup Bag.

Figure 1: My Neighbor's Maple Syrup Bags.

I am being a good citizen and socially distancing myself by staying at my cabin in northern Minnesota – avoiding people is easy in the dense forest that surrounds my residence. When I went out for my daily walk today, I saw that my neighbor had put out maple sap bags (Figure 1). Most folks around here collect their sap in these blue bags.

Some of my fondest boyhood memories are of my father driving the tractor that was pulling a trailer on which my grandfather and I sat on while going from tree to tree gathering pails of maple sap.

My brother has also been collecting sap this spring and he is reporting that it is taking 30 gallons of sap to obtain one gallon of syrup, which is better than the 40-to-1 ratio that he usually reports. This means the sap is a bit sweeter this year than last. Maple syrup typically has a sugar content greater than 66%, but maple sap typically has a sugar content of about 2.5% but can vary anywhere from 1% to 5%.

You can estimate the amount of sap you will need to make a gallon of maple syrup using the Jones Rule, or Rule of 86 (Equation 1). As an example of using the Rule of 86, given a 2.5% sugar concentration sap, you will need 86/2.5 = 34 gallons of sap to make 1 gallon of maple syrup.

Eq. 1 \displaystyle {{N}_{{Sap}}}=\frac{{86}}{{{{C}_{{Sap}}}}}


  • NSap is the number of gallons of maple sap per gallon of syrup.
  • CSap is the sugar concentration of maple sap expressed in percent.
Posted in Cabin, Personal | Leave a comment

WW2 Fighter Aircraft Fuel Fraction


Quote of the Day

Just because you had it rough does mean you need to inflict that mentality on subordinates – there's no pride to be had in suffering for the sake of suffering. Subordinates do not need to carry your burdens to have high quality of work ethic.

— Slide from Air Force briefing on training. I could not agree more. I have seen far too many people made to suffer because someone just wanted to be cruel.


Figure 1: Air Force fighter pilot who played a key role in developing modern fighter plane designs.

Figure 1: Air Force fighter pilot who played a key role in developing modern fighter plane designs. (Source)

I recently read a book called Boyd: The Fighter Pilot Who Changed the Art of War that documents the life of John Boyd, a fighter pilot who was critical to putting fighter aircraft design on a firm mathematical footing now called Energy-Maneuvering Theory. He was a key member of the Fighter Mafia, a group of military and industry experts in the 1970s that advocated for a lightweight fighter alternative to the large, heavy fighters like the F-111 and MiG-25. Their concepts played a significant role in the final designs of the F-16 and F-18.

Boyd also had an impact on modern management with his advocacy of a nimble decision-making strategy called the Observe-Orient-Decide-Act (OODA) loop that has become doctrine in many military, legal, and corporate circles. I certainly encountered this approach in numerous corporate management situations.

One fighter metric that Boyd used was called the fuel fraction, which is defined as the ratio of fuel weight to the plane's take-off weight.  In theory, this metric should provide an indication of the range of the fighter. I became curious as to how the fuel fraction of WW2 fighters compare with modern fighters. No sophisticated data analysis here, just some simple digging in various airplane databases and putting the data into a spreadsheet, which can be downloaded here.



Modern fighter aircraft have fuel fractions in the 30% range. For example, the F-22 has a fuel fraction of 29% and the Eurofighter Typhoon has a fuel fraction of 31% (reference). I was curious as to what the fuel fraction was of US WW2 fighters, so I decided to do a little research.


There are numerous terms in the specifications that are not always used consistently. Here is my take on these terms.

Combat Radius
Out and back range of an aircraft loaded for combat, including a budgeted time for combat. There appears to be no standard definition for this term, so it cannot be used to compare different aircraft.
Empty Weight
The empty weight of an aircraft is the weight of the aircraft without passengers or fuel, but with unusable fuel, full operating fluids, and full engine oil. (link)
Ferry Distance
For the purposes of this post, the one-way range of an aircraft without drop tanks. The aircraft is configured for maximum range and has minimum or no weapons load. You will find references that do refer to ferrying aircraft during wartime with drop tanks. I have tried to find references for ferry range that did not use drop tanks.
Fully Loaded Weight
Takeoff weight of an aircraft under normal circumstances, which includes fuel and typical weapons load.
Transfer Distance
Same as ferry range.


I thought my research would involve simply reviewing various aircraft specifications. However, few sources agreed on any of the specifications for range. I ended up taking the minimum ferry distance from contemporaneous military sources or books on these aircraft. Once I had the ferry range, I simply divided the internal fuel weight by the takeoff weight to get the fuel fraction.

For this exercise, I examined some of the most common US fighters, plus the Japanese Zero. I added the Zero because it was famous for its long range.

I should note that WW2 fighters often used drop tanks for both combat and ferry flights. There were numerous drop tank configurations and I decided to focus on the internal fuel capacities and on the one-way maximum range on internal fuel to keep things simple.


There is nothing sophisticated in this work – simple research, organizing, and plotting. Figure 2 shows my scatter plot of a small sample of WW2 fighters.

I was hoping to see a correlation between the ferry range and fuel fraction, but I see no strong relationship. It is interesting to note that most of the fighters had fuel fractions around 12%, which is much less than the fuel fraction of modern jet fighters.

Figure 2: Fuel Fraction versus Ferry Range.

Figure 2: Fuel Fraction versus Ferry Range.

It is interesting to note that the two fighters with the longest ferry range were the F6F-3 (Hellcat) and the A6M2 (Zero). These were fighters designed specifically to operate from carriers in the Pacific where distances were very long. Note that the fuel fraction of the Hellcat was relatively low, while the fuel fraction of the Zero was relatively high.


The fuel fractions for US fighters during WW2 appears to be  ~12%, which is substantially less than the ~30% value often seen with modern fighters.

Posted in Excel, History Through Spreadsheets, Military History | 2 Comments

Combined Excel Timeline/Column Chart using WW2 Naval Air Data


Quote of the Day

I'd rather fly in a Spitfire but fight in a Hurricane - because the Hurricane was made of non-essential parts. I had them all shot off at one time or another, and it still flew just as well without them.

James 'Ginger' Lacey of 501 Squadron, who was the second-highest ace from the Battle of Britain.


Figure 1: F6F Hellcat, fighter that dominated US carrier sorties in the later part of the Pacific War.

Figure 1: F6F Hellcat, the fighter that dominated US carrier sorties in the later part of the Pacific War.

I recently needed to generate a graph in Excel that combined a column chart with a timeline. The graph turned out well and I decided to share my work here. As my original work is proprietary, I will share the technique here using some US Navy (USN) air operations data from WW2.

I OCRed Table 5 of the WW2 Naval Aviation Combat Statistics (NASC) report, which contains both monthly squadron numbers and action sorties flown by month. I filtered the data to show the sum of the fighter (Wildcat, Hellcat, Corsair) and bomber (Dauntless, Helldiver, and Avenger) sorties.

For those who like to follow along, my workbook is here.


I prepared a column chart of the monthly fighter and bomber sorties. I also added a timeline of major battles to provide some context for the number of sorties. The date ranges of the major battles were obtained from the Wikipedia.

The NASC separated the sortie data into carrier-based and land-based categories.



I used Tabula to grab Table 5 from the NASC and output a CSV file. I then used Power Query to clean the data and to prepare it for graphing.


Figure 2 shows my plot of carrier and land sorties versus time in the chart's upper half and the battle timeline on the chart's lower half. This graph nicely illustrates that:

  • Until late-1943, the USN had relatively few sorties per month compared to the first few months of 1945.
  • The Battles of Coral Sea, Midway, and Guadalcanal can be seen on the graph, but have relatively few sorties compared to the Battles of Manila, Iwo Jima, and Okinawa.
  • There is a long carrier sortie drought during most of 1943.

Figure 2 shows very clearly that the USN needed ~18 months to build itself into a navy that could take on the IJN at sea.

Figure 2: Sorties vs Time with Timeline.

Figure 2: Sorties vs Time with Timeline.


While I do not like to use Excel for graphics, it does allow you to easily combine different chart types. This can be useful for some types of presentations, such as this one.

Posted in Excel, History Through Spreadsheets, Military History | Leave a comment