Golden Spreadsheet

by Geoff Canham, LUNAR #493

My stepson, Matt Kennedy, and I have been working our way through the NARTREK (NAR Training Rocketeers in Experience & Knowledge) levels. The Bronze level (Vol. 5, No. 2) worked through standard kits to challenge us to reach specified goals, and the Silver level (Vol. 5, No. 3) introduced us to a greater variety of rocket types, including rocket gliders and clustered rockets, and involved more intricate construction. Now we are working on the Gold Level, and this involves designing a rocket, calculating its performance, building the rocket, flying it and comparing actual performance to calculated performance.

The NARTREK documentation gives sources of information, including a Web site where you can download software to calculate the performance of a rocket (see Lynn Kissel’s article in Vol. 5, No. 3), but we decided to develop a spreadsheet to do the work. There are actually two stages to calculating the performance, firstly to estimate the Center of Gravity and the Center of Pressure for the rocket, and establish whether it will be stable or not in flight, and secondly to calculate the altitude that it will fly to on specified motors. The software that we have seen does one or other of these stages, but not both (although Lynn’s article mentions at least one that does both). The spreadsheet is written to do both stages in one go.

There are almost always approximations that need to be made in the calculations, and the main approximations in this spreadsheet are:

1. The thrust is assumed to occur at the same level throughout the thrust phase, rather than rapidly reaching a peak, then leveling off at a lower level;
2. The total fuel is assumed to be expended during the thrust phase, whereas some is also used to provide smoke during the coast phase and to eject the recovery system (parachute or streamer).

To compensate for these approximations, an adjustment factor has been included in the formula, which is currently set at 1.30. This value was arrived at by using the values for the various Estes rockets from their catalog and checking the calculated altitudes against the stated values. Since then, we have compared results from the spreadsheet against results from a program downloaded from the Internet, and they come close (sometimes higher, sometimes lower).

A major factor contributing to drag during the flight is the frontal area of the rocket, and the spreadsheet calculates this as being the cross-sectional area of the body tube, based on the body tube diameter. The rocket I am designing has large protuberances on the fins that add substantially to the frontal area, so I calculated and inserted the area directly (overwriting the formula).

All the formulas, and the instructions for using the spreadsheet contained in the Excel spreadsheet stored at

Information date: September 18, 1998 lk