Using Excel to Calculate the RPM and Gearmesh Frequency of a Multistage Planetary Gearbox

To calculate the RPM, gearmesh frequency, and output-to-input ratio of a multistage gearbox, we must first calculate the output at each stage. To calculate the output, we must know the inputs. Each stage can only have one output but can have multiple inputs. The inputs of a stage can come from both the inputs and outputs of earlier stages.  

Using a three-stage planetary gearbox as an example, we will now calculate the values in an Excel application. We will need the following information:  

  • Number of stages 
  • The configuration (1–9) of each stage 
  • The RPM input(s) to the first stage 
  • The origin of the inputs for each following stage 

Figure 1 shows an example of a three-stage planetary gearbox with an input of 200 RPM. The number of teeth is shown for each component. We want to know the RPM output. 

Figure 1: Planetary gearbox example 

We can see that there are three stages. Next, we need to determine the configuration of each stage.  

For Stage 1, the input comes via the planet carrier. The ring is fixed, and the output is from the sun gear. These are the markers of Configuration 2.  

Figure 2: Find the configuration of Stage 1 

For Stage 2, the input comes via the ring gear. The planet carrier is held fixed, and the output is from the sun gear. These are the markers of Configuration 5.

Figure 3: Find the configuration of Stage 2 

For Stage 3, there are multiple inputs. Input 1 is to the ring gear, and input 2 is to the planet carrier. The output is from the sun gear. These are the markers of Configuration 8.

Figure 4: Find the configuration of Stage 3 

We now know the configuration of each stage. We have already established that the input to Stage 1 is 200 RPM. The next step is to determine the origins of the inputs at each of the following stages.

Figure 5: Determining the origin of the inputs at each following stage  

For Stage 2, the input to the ring gear comes from the planet carrier of Stage 1. For Stage 3, the input to the ring comes from the sun gear of Stage 2, and the input to the planet carrier comes from the sun gear of Stage 1.  

We now have all the information we need for the Excel application. The data can be pasted into the relevant fields, shown in yellow in Figure 6, and then we click “run” to calculate the RPM, gearmesh frequencies, and ratios.

Figure 6: Add the required information to the Excel application and click “run”  

For this gearbox, the total ratio between the output at the last stage and the input at the first stage is 34.17.