A Model Railway depicting Thurstaston station on the Hooton to West Kirby branch on the Wirral Peninsula


To assist me in my loco building, I've developed a couple of spreadsheets to calculate gear ratios and frame spacer width.  These can be downloaded from the links below.  The files are in Microsoft Excel 97-2003 format.   Take a copy of the speadsheet and put your figures in the turquoise highlighted cells.  I've included two worksheets in each file; one ready for use and a worked example.  It may help to set up and keep a separate worksheet for each loco.

Gear ratios

This spreadsheet takes the driver diameter, and works out the driver rpm to achieve a target mph for both for the prototype and model.  The next input is the top speed you want your model to achieve.

The no-load speed of your chosen motor is needed.  For Mashima motors, there doesn't seem to be a definitive value but some figures are available from the EM Society handbook and the Branchlines Motors & gears catalogue.

Inputting the no-load speed of your motor and a % of this to use as the maximum will return the gear ratio required to achieve your required top speed at this motor speed.  As a check, the columns on the right show the wheel and motor revolutions at a range of scale speeds based on the resulting gear ratio.  The resulting colours show:
Green - motor revs below 75% of no load speed Yellow - motor revs between 75% and 100% of no load speed Red - motor revs above no load speed, so not feasible.

Having got the derived ratio, you can select the most appropriate actual ratio from those provided by your preferred gearbox supplier.

Frame spacing

The top part of this spreadsheet is set up for P4; for other gauges you will need to amend the initial wheel tyre back to back measurement accordingly.  It works by prompting for the dimensions of all the items I can think of that may exist around the axle between the back of the wheel and the inside of the frame.  If any aren't applicable, e.g. shorting strips, then put a zero in that row.  The dimensions are then applied to both sides with the resulting figure being the required distance between the frames.

The lower part of the worksheet can be used once the calulated or actual distance between the frames is known.  As above, the dimensions of items around the axle between the frames are entered, along with the overall width of the chosen gearbox over the axle bushes.  The space available between the gearbox and bearing is shown for each side.  Note that all dimensions are in mm.
Go to Top

The Prototype | Train workings | Historical Photographs | The Prototype Now| The Layout | Model Photographs | On my workbench | Research and Links