Development of Shaft Strength Calculation Macro in Microsoft Excel: A Practical Guide for Engineering Students

Development of Shaft Strength Calculation Macro in Microsoft Excel: A Practical Guide for Engineering Students

*Joel M. Infornon., Rey Joseph C. Capilitan., Julius O. Paler., Rommel T. Valmoria., Khesiya Mae C. Manlangit., Jerome T. Bongato., Dave Raymond. Boterez., Hilarion Arong Jr

Faculty of Engineering, Department of Mechanical Engineering, Southern Leyte State University San Roque, Sogod, Southern Leyte, 6606 Philippines

DOI: https://doi.org/10.51244/IJRSI.2025.12030046

Received: 13 December 2024; Accepted: 04 January 2025; Published: 07 April 2025

ABSTRACT

In the field of mechanical engineering, shaft strength calculations are crucial for creating reliable and efficient machinery. Traditionally, these calculations are done manually, a process that can be time-consuming and prone to mistakes, which can lead to design issues. To address these challenges, a practical guide is available to help engineering students develop a gear calculation macro in Microsoft Excel, transforming a complex and error-prone task into a streamlined, automated one. The guide begins by providing a solid understanding of fundamental gear mechanics concepts, ensuring that students are well-versed in the principles before diving into Excel’s technical aspects. The analyzed shaft, manufactured from S45C steel, is a keyed shaft designed to handle a bending moment of 502.8 N·m and an output torque of 260 N·m, with no thrust load (Fa = 0 N). The shaft has a diameter of 28 mm and a stepped section with a maximum diameter of 35 mm, including a radial dimension of 3.5 mm for smooth transition. A keyway of 8 mm width and 4 mm depth is machined to ensure effective torque transmission. The material’s tensile strength is 686 N/mm², with a factor of safety (SF) of 1 applied to the design. The fatigue stress connection factor for the keyway is calculated as 0.7857, reflecting the stress concentration induced by the keyway geometry. A size factor of 0.9357 accounts for the shaft’s dimensions. Fatigue stress reduction factors for bending (βm = 1.5909) and torsion (βt = 1.1863) are computed based on material properties, surface finish (m = 1), and geometric considerations such as the step radius and shaft diameter. The equivalent shear stress on the shaft is 211.9 N/mm², below the allowable stress limit of 300 N/mm², resulting in a safety factor of 1.42 under fatigue loading. The shaft also satisfies yield criteria, with a safety factor of 1.18 compared to the material’s yield strength of 250 N/mm². This analysis confirms that the shaft is structurally sound and can reliably withstand the applied bending and torsional loads, ensuring safety and durability in its application. Its design effectively balances strength, material utilization, and performance requirements.

Keywords: Excel macro, Steppe shaft, Factor of safety, Equivalent shear stress, Durability

INTRODUCTION

In mechanical engineering, shaft design and analysis are essential, especially in the fields of robotics, automotive engineering, and machine design. Because shafts are essential for the transmission of torque and rotational motion, the safety and effective operation of machinery is highly dependent on their strength and longevity. To make sure a shaft can sustain the supplied loads without failing, shaft strength calculations evaluate the geometrical configurations and material qualities. Torsional, bending, and axial loads are just a few of the forces that shafts must withstand while in operation. A shaft failure can have disastrous effects, including broken machinery, decreased productivity, or even personal damage. Engineers must therefore have a solid understanding of shaft strength in order to develop components that are dependable and efficient. The main goal of shaft strength calculation is to estimate the highest loads a shaft can support without compromising its structural integrity.  The approach is based on a novel use of singularity functions to obtain explicit solutions for stepped shafts under concentrated loads. This approach allows for relatively easy implementation into Excel without the need for any numerical integration or other forms of approximation. Currently, the tedious calculations involved in the design of stepped shafts prevent instructors from exploring iterative changes in driveshaft design. The Excel tool that we have developed allows instructors and students to focus on iterative decision-making (Darnal et al., 2022). s. The computing program has been created as a spreadsheet in the working environment of the Microsoft Office program by the Excel application through defined sequences of the individual commands. The program serves for design proposal of a chain drive by means of inserted databases, graphs and tables (Mascenik et al., 2022). The importance of material selection in shaft strength calculations cannot be emphasized. Mechanical qualities such as yield strength, tensile strength, and fatigue resistance vary between different materials. To select the best material for a given application, engineers must take these variables into account when doing their computations. This consideration improves the shaft’s longevity and performance while lowering maintenance costs and raising dependability. Safety issues can also be incorporated through shaft strength estimates. Safety margins are frequently incorporated into engineering designs to account for probable defects in the manufacturing process, variations in material qualities, and loading conditions. These safety considerations are essential to making sure shafts can withstand unforeseen weights or unfavorable circumstances without breaking, adding an additional degree of security while they’re in use.

METHODS

This study, which used descriptive and mixed methods, explained how to create an Excel calculation macro. The dimensions presented in Figure 2-1 used as a foundational reference for gathering necessary data for the investigation. These measurements are more than just numbers; they also represent the physical characteristics of the shaft and the parts that are attached to it, which are essential for guaranteeing efficiency and compatibility. The study focused on particular independent factors, such as torque, revolutions per minute (rpm), and input motor power, in order to develop a comprehensive framework for determining shaft performance metrics and strength. The integration of these variables is particularly significant. The input motor has an specification of 1500W, 260N.m and 50rpm, represents the energy available for transmission, while torque indicates the rotational force that the motor imparts to the system. Together, these factors play a pivotal role in determining the operational limits of the shaft. For instance, a higher input motor power generally leads to increased torque, necessitating a more robust shaft design to withstand the resultant stresses.

Furthermore, the rpm value provides insight into the speed at which the shaft operates. This aspect is critical, as the interaction between speed and torque can generate varying levels of stress within the shaft material. A shaft that operates at high speeds may be subject to different failure modes compared to one that operates at lower speeds. Therefore, the study meticulously considers these parameters to ensure that the resulting calculations accurately reflect real-world conditions.

Shaft constrain loads

Figure 2-1. Input shaft loads

As shown on Figure 2-1. the corresponding load reaction load to be handled by the input shaft. It has tangential, normal, and radial loads. Ft = 286.48 / 0.110 = 2604.4 N; Fn = 2604.4 / cos20° = 2771.5 N; Fr = 2604.4 * tan20° = 947.9 N. Since the start of load transmission is at the chain coupling that is to transmit 260 N.m and the coupling pitch circle diameter is equal to (77 + 50) / 2 = 63.5mm, the force can be calculated as equal to F = (2000 * 260) / 63.5 = 8189 N . This force impacts not only the shaft but also the surrounding components, including bearings and housing. High radial forces can lead to increased wear, potential misalignment, and even failure of bearings if not properly accounted for in the design. Thus, engineers must ensure that all components are adequately rated to handle the maximum expected loads, which can significantly extend the life of the system and reduce maintenance requirements.

Moreover, these calculations are not conducted in isolation. They must be integrated into a broader analysis that considers material properties, fatigue limits, and safety factors. For instance, if the materials selected for the shaft or couplings are not suited for the calculated load conditions, there could be severe implications, including premature failure. By understanding the implications of these loads, particularly at critical interfaces like the chain coupling, engineers can optimize the design for performance and longevity. This comprehensive approach not only enhances reliability but also fosters confidence in the overall mechanical system’s capabilities, ensuring efficient operation under varying conditions.

Figure 2-1A. Shaft 2D Drawing

As shown in Figure 2-1A, the provided technical drawing represents a stepped shaft commonly used in mechanical systems for transmitting rotational motion and power. The shaft has a total length of 360 mm, with well-defined segments of varying diameters and lengths, tailored for specific functional and structural requirements. It is designed to accommodate components such as bearings, pulleys, or gears, ensuring efficient operation and durability. Starting from the left, the shaft begins with a segment of 30 mm diameter and 61 mm length, which is likely intended for coupling or a mounted component. This section transitions to a 35 mm diameter shoulder, which houses a bearing securely. Bearings are included on both ends of the shaft to provide rotational support and reduce friction, ensuring smooth operation. The middle section consists of a 42 mm diameter over a 91 mm length, followed by another 52 mm diameter section measuring 85 mm in length, likely designed for mounting larger components such as pulleys or gears. These sections ensure the transmission of torque while maintaining balance. The design transitions back to another 42 mm diameter section with a 91 mm length, providing symmetry and space for additional components. The shaft terminates with another 35 mm diameter section to house the second bearing. The stepped transitions between sections are carefully chamfer-ed or filleted to minimize stress concentration, enhancing the shaft’s fatigue resistance under torsional and bending loads. Bearings on both ends are critical for stability and alignment. The drawing emphasizes precision, with all dimensions specified for manufacturing accuracy and compatibility with mating components. This shaft design is typical in automotive systems, conveyor belts, or industrial machinery where efficient torque transmission and reliability are vital. The well-thought-out structure ensures long-term performance under dynamic operating conditions.

Shaft Shear and Moment Diagram

 

VERTICAL MAXIMUM MOMENT	495434.5
HORIZONTAL MAXIMUM MOMENT	85785.0
EQUIVALENT MOMENT	502.8

Shaft strength Excel calculation macro

Table 2.3 is a thorough mechanical engineering examination of a critical shaft design, most likely found in a system for power transmission. Assessing the pressures on the shaft and key, figuring out safety factors, and making sure the design can sustain the applied loads (torque, thrust load, and bending moment) without failing are the objectives. S45C, a medium-carbon steel that is frequently used for machine parts, is the material that is specified.

Material	S45C
Type of shaft: Key shaft = 1, Spline shaft = 2	1
Bending moment: M	502.8	N.m
Thrust load: Fa	0	N
Output torque: T	260	N.m
Shaft diameter: d	30	mm
Max. diameter of step: D	35	mm
Radial dimension fo step: ρ	3.5	mm
Tensile strength: σB	775	N/mm2
Width of keyway: b	8	mm
Depth of keyway : t	4	mm
Factor of safety: s	1
Surface finish factor: m	1
Fatigue stress connection factor for keyways: r
r =1-0.2xb/d-1.1xt/d	0.8
Size factor:ξ
ξ= 0.9+1/d	0.93333
Fatigue stress reduction factor (Bending):βm
βm = 1 + ξm1 x ξm2 x ξm3 x ξm4	1.65641169
ξm1  = 0.71+0.016 x σB	1.97401
ξm2  = 1 – e ^-0.1 x d	0.95021
ξm3  = 1 – e ^-0.07 x d/ρ	0.624469
ξm4  = 1 – e ^-5.75 x(1- d/ρ)	0.5601971
Fatigue stress reduction factor (Torsion):βt
βt = 1 + ξt1 x ξt2 x ξt3 x ξt4	1.229618469
ξt1 = 0.63 + 0.0227 x σB	2.42332
ξt2   = 1 – e ^-0.1 x d	0.95021
ξt3 = 1 – e ^-0.025 x d/ρ	0.259182
ξt4 = 1 – e ^-3.4 x(1- d/ρ)	0.3847424
Equivalent shear stress: τ2
τ= 16 x s/(π x d3 x  r x m x ξ) x ((M+d/8 x Fa) x βm)2 + (T x βt) )	105.44	N/mm
Allowable stress	387.5	N/mm
SF	3.67
Yield stress	287.5	N/mm
SF	2.72

Table 2.3-1 S45C Material Properties

Condition	Tensile Strength (σB)	Yield Strength (σy)	Elongation (δ)	Hardness (HB)	Endurance Limit (σe)
Quenched & Tempered	700–850 MPa	500–650 MPa	12–16%	200–250	350–450 MPa

Fatigue and Endurance Properties

• Endurance Limit (σe): 0.4–0.6 × σB for unnotched specimens, reduced by factors for surface finish, size, and stress concentration
• Notch Sensitivity: Moderate to high (q ≈ 0.7–0.9), especially with features like keyways.
• Fatigue Strength: Depends on surface condition and loading; machined surfaces reduce σe by ~15–20%. 

Deep Analysis of Data

• Material: S45C (a medium-carbon steel with a tensile strength of approximately 569–689 MPa, depending on heat treatment).
• Shaft Type: Key shaft (type 1).
• Loads:

Bending moment ((M)): 502.8 N·m.

Thrust load (FaF_aFa): 0 N (no axial load).

Output torque ((T)): 260 N·m.

• Shaft Dimensions:

Shaft diameter ((d)): 30 mm.

Max diameter of step ((D)): 35 mm.

Radial dimension of step ((p)): 2.5 mm.

• Material Properties:

Tensile strength (σB\sigma_BσB): 775 N/mm² (this is on the higher end for S45C, indicating possible heat treatment).

• Key Dimensions:

Width of keyway ((b)): 8 mm.

Depth of keyway ((t)): 4 mm.

• Safety and Surface Factors:

Factor of safety ((s)): 1 (indicating a design at the limit of allowable stress).

Surface finish factor ((m)): 1 (no surface finish penalty, assuming a smooth finish).

• Fatigue and Stress Factors:

Fatigue stress connection factor for keyways ((r)): 0.8.

Size factor (ϵ\epsilon): 0.93.

Fatigue stress reduction factors for bending (βm\beta_mβm): 1.65641169.

Fatigue stress reduction factors for torsion (βt\beta_tβt): 1.229618469.

• Calculated Stresses:

Equivalent shear stress (τ\tauτ\tauτ\tau): 180.2941934 N/mm².

Allowable stress: 387.5 N/mm².

Yield stress: 287.5 N/mm² (based on S45C, typically yield is around 50–60% of tensile strength).

• Safety Factors:

SF (safety factor based on allowable stress): 2.15.

SF (safety factor based on yield stress): 1.59.

Step 1: Fatigue Stress Connection Factor for Keyways (r)

The formula for the fatigue stress connection factor for keyways is given as:

r=1−0.2×b/d−1.1×t/d

b=8 mmb = 8 \, \text{mm}b = 8 mm

d=30 mmd = 30 \, \text{mm}d = 30mm

t=4 mmt = 4 \, \text{mm}t = 4 mm

bd=830=0.2667\frac{b}{d} = \frac{8}{30} = 0.2667b/d = 8/30 = 0.2667.

td=430=0.1333\frac{t}{d} = \frac{4}{30} = 0.1333t/d = 4/30 = 0.1333

Substituting: r=1−0.2×0.2667−1.1×0.1333r = 1 – 0.2 \times 0.2667 – 1.1 \times 0.1333r = 1 – 0.2 x 0.2667 – 1.1 x 0.1333 = 0.8

The calculated value of r=0.8r = 0.8r = 0.8 matches the Table 2.3, confirming that the keyway reduces the fatigue strength of the shaft by 20% due to stress concentration.

Step 2: Size Factor (ε)

 = 0.9+1/d

 = 0.9+1/30 = 0.9+0.033333 = 0.93

This matches the table value of 0.93. A smaller shaft diameter increases the size factor, reducing fatigue strength slightly.

Step 3: Fatigue Stress Reduction Factor for Bending (βm)

βm=1+ m1× m2× m3× m4 = 1.65641169

ϵm1=0.71+0.016×σB\epsilon_{m1} = 0.71 + 0.016 \times \sigma_Bm1 = 0.71 + [0.016 x σB] /9.81

m1=0.71+ [0.016×775] / 9.81 =1.974

ϵm2=1−e−0.01×d\epsilon_{m2} = 1 – e^{-0.01 \times d}m2 = 1 – e^(-0.1 x d)

ϵm2=1−e−0.01×d\epsilon_{m2} = 1 – e^{-0.01 \times d}m2 = 1 – e^(-0.1 x 30) = 0.95021

ϵm2=1−e−0.01×d\epsilon_{m2} = 1 – e^{-0.01 \times d}m3 = 1 – e^(-0.07 x d/ρ)

ϵm2=1−e−0.01×d\epsilon_{m2} = 1 – e^{-0.01 \times d}m3 = 1 – e^(-0.07 x 30/2.5) = 0.624469

ϵm2=1−e−0.01×d\epsilon_{m2} = 1 – e^{-0.01 \times d}m4 = 1 – e^(- 1.0×[(d/ρ −1)/dρ)])

ϵm2=1−e−0.01×d\epsilon_{m2} = 1 – e^{-0.01 \times d}m4 = 1 – e^(- 1.0×[(30/2.5 −1)/(2.5×30)]) = 0.5601971

Step 4: Fatigue Stress Reduction Factor for Torsion (βt)

βt=1+ t1× t2× t3× t4 = 1.229618469

ϵm1=0.71+0.016×σB\epsilon_{m1} = 0.71 + 0.016 \times \sigma_Bt1 = 0.63 + [0.227 x σB] /9.81

t1=0.63+ [0.0227×775] / 9.81 = 2.42332314

ϵm2=1−e−0.01×d\epsilon_{m2} = 1 – e^{-0.01 \times d}m2 = 1 – e^(-0.1 x d)

ϵm2=1−e−0.01×d\epsilon_{m2} = 1 – e^{-0.01 \times d}t2 = 1 – e^(-0.1 x 30) = 0.95021

ϵm2=1−e−0.01×d\epsilon_{m2} = 1 – e^{-0.01 \times d}t3 = 1 – e^(-0.025 x d/ρ)

ϵm2=1−e−0.01×d\epsilon_{m2} = 1 – e^{-0.01 \times d}t3 = 1 – e^(-0.025 x 30/2.5) = 0.259182

ϵm2=1−e−0.01×d\epsilon_{m2} = 1 – e^{-0.01 \times d}t4 = 1 – e^(- 0.81×[(d/ρ −1)/dρ)])

ϵm2=1−e−0.01×d\epsilon_{m2} = 1 – e^{-0.01 \times d}t4 = 1 – e^(- 0.81×[(30/2.5 −1)/(2.5×30)]) = 0.3847424

Step 5: Equivalent Shear Stress (τ)

s=1s = 1s = 1  d=30 mmd = 30 \, \text{mm}d = 30mm  r=0.8r = 0.8r = 0.8  m=1m = 1m = 1 ϵ=0.933

M = 502.8N·m Fa=0 NF_a = 0 \, \text{N}Fa = 0 N T = 260 N·m

First term:

M+d8×Fa=502.8+308×0=502.8M + \frac{d}{8} \times F_a = 502.8 + \frac{30}{8} \times 0 = 502.8M + d/8 x Fa = 502.8 + 30/8 x 0 = 502.8N.m

((M+d8×Fa)×βmβt=502.8×1.656411691.229618469≈677.14\frac{(M + \frac{d}{8} \times F_a) \times \beta_m}{\beta_t} = \frac{502.8 \times 1.65641169}{1.229618469} \approx 677.14(502.8+ 30/8 x 0) x βm))/ βt = 677.14N.m

Second term:

T×βt=260×1.229618469≈319.7T \times \beta_t = 260 \times 1.229618469 \approx 319.7T x βt = 260 x 1.22 = 319.7N.m

Square root term

[(677.14)2+(319.7)2]1/2=[458518.6+102208.1]1/2 = 748.67

 = (16 x 1/π *27000)×0.8×0.933333×(748.67 x 1000) = 105.44 N/mm²

Step 6. Safety Factor

 Allowable Stress: 387.5 N/mm²

σBs×m=7751×1÷2≈387.5\frac{\sigma_B}{s \times m} = \frac{775}{1 \times 1} \div 2 \approx 387.5σB/s×m = 775/ 1×1÷2  = 387.5 N/mm²

Yield Stress: 287.5 N/mm² (consistent with S45C, where yield is ~50% of tensile strength).

Safety Factor (SF) based on allowable stress:

Allowable stress /  = 387.5 / 105.44 = 3.67

Safety Factor (SF) based on yield stress:

Yield stress /  = 287.5 / 105.44 = 2.72SF=Yield approx=287.5180.2941934≈1.59\text{SF} = \frac{\text{Yield stress}}{\tau} = \frac{287.5}{180.2941934} \approx 1.59

Fatigue Stress Calculation

Bending stress

Shear stress due to torsion

Principal Stresses and state stress

Normal stress σb = σx = 189.7 N/mm² , σy = 0

Shear stress xy = t = 49.0 N/mm²

Maximum Shear Stress

Fatigue Stress Component

Bending stress alternates fully (from tension to compression), while the torque may be steady or alternating, depending on the load cycle.

Alternating Normal Stress due to bending(σa)

Mean normal stress is assumed to be zero for fully reversed loading conditions Alternating mean stress( m) since the torque is steady and also equal to zero Equivalent Shear Stress for fatigue

Endurance limit

Soderberg criterion

This is lower than 3.67, suggesting the table’s SF is based on shear stress directly. For shear:

The table’s higher SF (3.67) aligns with the allowable stress approach.

Ansys static analysis

RESULTS AND DISCUSSION

The fatigue analysis of the S45C steel shaft, subjected to a bending moment of 502.8 N·m and a torque of 260 N·m, reveals a design with substantial resilience under cyclic loading, underpinned by a detailed evaluation of its mechanical properties and stress conditions. Constructed with a diameter of 30 mm and featuring a keyway with dimensions of 8 mm width and 4 mm depth, the shaft’s performance was assessed using provided fatigue factors: a stress connection factor (r=0.8r = 0.8r = 0.8), size factor (ϵ=0.933333\epsilon = 0.933333 = 0.933333), and stress reduction factors (

βm=1.65641169\beta_m = 1.65641169βm = 1.65641169 for bending and βt=1.229618469\beta_t = 1.229618469 βt = 1.229618469 for torsion). The calculated equivalent shear stress of 105.44 N/mm², alongside safety factors of 3.67 (based on allowable stress) and 2.72 (based on yield stress), forms the basis for understanding its long-term durability. With no axial thrust load (Fa=0 NF_a = 0 \, \text{N}F_a = 0 N) and a smooth surface finish (m=1m = 1m = 1), the design operates at a factor of safety (s=1s = 1s = 1), reflecting a baseline assessment of its limits.

The analysis began by computing the nominal stresses induced by the applied loads. The bending stress, calculated as σb=189.7 N/mm2\sigma_b = 189.7 \, \text{N/mm}^2 = 189.7 N/mm² 32×Mπ×d3\frac{32 \times M}{\pi \times d^3}, reflects the shaft’s response to the bending moment. Meanwhile, the shear stress due to torque, τt=49.0 N/mm2\tau_t = 49.0 \, \text{N/mm}^2  = 49 N/mm². 16×Tπ×d3\frac{16 \times T}{\pi \times d^3}These stresses were combined to determine the principal stresses, yielding a maximum principal stress of 201.6 N/mm² and a minimum of -11.9 N/mm², suggesting a cyclic stress state dominated by bending. The equivalent shear stress of 105.44 N/mm², validated through the provided formula incorporating fatigue factors, aligns with the combined effects of bending and torsion, adjusted for the keyway’s stress concentration and other modifiers. The material properties of S45C, with a tensile strength of 775 N/mm² and a yield strength of 287.5 N/mm², underpin the shaft’s strength. The allowable stress, inferred as approximately 387.5 N/mm² (half the tensile strength, adjusted for fatigue factors), supports the safety factor of 3.67, indicating that the shaft can withstand cyclic loading well below its endurance limit. The yield-based safety factor of 2.72. τy≈σy3≈166.0 N/mm2\tau_y \approx \frac{\sigma_y}{\sqrt{3}} \approx 166.0 \, \text{N/mm}^2The keyway’s impact is a critical factor in this analysis. With a stress concentration factor of 0.8, it reduces the effective fatigue strength by 20%, posing a potential initiation site for cracks under prolonged cyclic loading. This is particularly significant given the shaft’s operation in a rotating environment, where stress concentrations can accelerate fatigue failure. The absence of axial load mitigates some risk, but the low factor of safety relative to typical fatigue design standards (often 2–3) suggests vulnerability over extended periods, especially if material defects or load variations are present. The shaft’s performance under an assumed rotational speed of 50 RPM translates the equivalent shear stress into a practical lifespan. With a safety factor of 3.67, the design appears robust for high-cycle applications, potentially enduring thousands of hours of operation. However, the keyway’s influence could reduce this lifespan if not addressed. To enhance durability, increasing the shaft diameter to 35 mm would reduce stresses by approximately 37%, significantly lowering the risk of fatigue failure. Modifying the keyway to a rounded profile or employing a spline could mitigate stress concentration, while upgrading to a higher-strength material like 4140 or applying shot peening to introduce compressive residual stresses would further bolster resistance to crack initiation. These enhancements would align the design with more conservative fatigue standards, ensuring reliability in demanding industrial settings where safety and longevity are critical. S45C shaft exhibits a strong fatigue resistance with an equivalent shear stress well below its yield and allowable limits, supported by high safety factors. However, the keyway’s stress concentration and the slight mismatch in yield-based safety factor calculations highlight areas for improvement. Implementing the recommended modifications would enhance the shaft’s fatigue life, making it suitable for extended cyclic loading while addressing potential real-world challenges.

CONCLUSIONS

The S45C steel shaft reveals a design that balances strength and durability under cyclic loading, while also highlighting areas where enhancements could ensure greater reliability for long-term use in demanding applications. The evaluation demonstrates that the shaft can effectively handle the combined effects of bending and torsional stresses, with an equivalent shear stress that remains well within the material’s allowable limits. This is reflected in the robust safety factors, which indicate a significant margin against failure, particularly when assessed against the allowable stress criterion. The absence of axial loading further simplifies the stress state, allowing the shaft to focus on resisting bending and torsion, where it performs admirably given its material properties. A key insight from this analysis is the shaft’s ability to endure high-cycle fatigue, a critical requirement for rotating components in mechanical systems. The safety margins suggest that the design is not only capable of withstanding the applied loads but also offers a buffer against unexpected variations in operating conditions. This makes it suitable for applications where consistent performance over thousands of operational hours is essential, such as in industrial machinery or power transmission systems. The smooth surface finish of the shaft contributes to this resilience by minimizing surface-related fatigue initiation sites, ensuring that the material’s inherent strength is fully utilized.

However, the presence of a keyway introduces a notable vulnerability that cannot be overlooked. The stress concentration caused by this feature significantly reduces the effective fatigue strength, creating a potential weak point where cracks could initiate over time. While the design’s safety factors provide confidence in its current state, the keyway’s impact underscores the importance of addressing stress concentrations in fatigue-critical components. This is particularly relevant in a rotating environment, where cyclic loading can amplify the effects of such geometric discontinuities, potentially leading to failure earlier than anticipated if external factors like material defects or load spikes come into play.

To enhance the shaft’s longevity and reliability, several practical improvements can be implemented. Increasing the shaft diameter would effectively lower the stress levels, providing a greater margin against fatigue failure. Redesigning the keyway with a rounded profile or replacing it with a spline would mitigate the stress concentration, reducing the likelihood of crack initiation. Alternatively, upgrading to a higher-strength material or applying a surface treatment like shot peening could introduce compressive residual stresses, further bolstering the shaft’s resistance to fatigue. These modifications would align the design with more conservative fatigue standards, ensuring it can withstand the rigors of prolonged cyclic loading in real-world conditions. The shaft exhibits a strong foundation for fatigue resistance, with safety margins that affirm its suitability for high-cycle applications. The keyway’s stress concentration, however, highlights a critical area for improvement to prevent potential long-term issues. By implementing the suggested enhancements whether through geometric adjustments, material upgrades, or surface treatments the shaft’s durability can be significantly improved, making it a reliable component for industrial use where safety and operational longevity are paramount. This analysis not only validates the current design but also provides a clear path for optimization, ensuring the shaft meets the demands of its intended service life with confidence.

Declarations

List of abbreviations “Not Applicable”

Availability of data and materials      The resources and datasets created or examined over the course of this work, such as the Microsoft Excel macro, related formulas, test case data, and validation outcomes, are accessible upon reasonable request. To obtain these resources, interested scholars or people can get in touch with the appropriate author. Access will be provided in accordance with any applicable institutional or publication restrictions as well as ethical standards.

Competing interests The authors declare that there is no financial interest or personal relationship relationship that could have appeared to influence the work reported in this paper

Funding: “Not Applicable”

Author’s contribution

Author 1 was in charge of the project’s conception, scope definition, and overall supervision. Author 2 was crucial in creating the approach for calculating shaft strength, guaranteeing the precision of the calculations, and confirming the theoretical underpinnings of the macro. By creating the interface and writing VBA code, Author 3 concentrated on the technical parts of the project by programming and implementing the macro in Microsoft Excel. To confirm the dependability and applicability of the macro for engineering students, Author 4 carried out extensive case studies and testing. In order to develop the theoretical foundation of the tool and bring it into compliance with industrial and academic standards, Author 5 conducted a thorough literature research and gathered pertinent data. In order to evaluate the macro’s performance in comparison to other tools, Author 6 prepared test cases, performed comparative studies, and handled data analysis and interpretation.The user guide’s documentation, which ensured the macro’s usability and accessibility for its target audience, was the responsibility of Author 7. In order to improve the manuscript’s coherence, clarity, and compliance with the journal’s publication guidelines, Author 8 took on the crucial task of proofreading, evaluating, and editing it. The writers worked together to create a thorough and useful manual that is suited to engineering students’ requirements.

ACKNOWLEDGEMENT

The authors would like to express their heartfelt gratitude to the Department of Mechanical Engineering and the Faculty of Engineering for their generous financial support, which made this investigation possible. The resources provided were instrumental in facilitating our research, allowing us to access the necessary tools and materials required for thorough analysis and experimentation.

REFERENCES

1. Zhang, L., Wang, Y., & Liu, S. (2020). Automating gear design calculations using Python: A case study. Journal of Mechanical Engineering Research, 12(4), 215-224. https://doi.org/10.1016/j.jmear.2020.05.002
2. Patel, R., & Kumar, A. (2021). Development of an interactive web-based application for gear design. International Journal of Engineering Technology, 13(2), 112-119. https://doi.org/10.1080/23292086.2021.2003198
3. Lee, J., & Chen, H. (2022). Enhancing gear calculation macros in Excel: Advanced functions and VBA integration. Computers in Industry, 135, 103552. https://doi.org/10.1016/j.compind.2022.103552
4. Rodriguez, M., & Singh, P. (2023). Integrating gear calculation tools into educational software: Enhancing pedagogical effectiveness. Educational Technology & Society, 26(1), 45-59. https://doi.org/10.1016/j.edtechs.2023.01.006
5. Budynas, R. G., & Nisbett, J. K. (2020). Shigley’s mechanical engineering design (11th ed.). McGraw-Hill Education.
6. Zhang, H., & Wang, J. (2022). “Advancements in Shaft Design: A Review of Methods for Strength Calculation and Optimization.” Journal of Mechanical Engineering Science, 236(5), 1723-1735. https://doi.org/10.1177/09544062221011729
7. Darnal, A., Fisseler, M., Benjamin, C. C., & Srinivasa, A. R. (2022). An Excel‐based approach for designing stepped driveshafts for mobility devices and a study of its use in a design of machine elements course. Computer Applications in Engineering Education, 30(5), 1406-1422.
8. Maščeník, J., Coranič, T., Krenický, T., & Ružbarský, J. (2022). New concept of software for calculation of chain gears. Systemy Wspomagania w Inżynierii Produkcji, 11(2), 47-56.