
An Excelbased transistor amplifier calculator.


For simple DoItYourself amplifiers such as the one described under my Karaoke Mixer project, I often turn to the circuit topology in Figure 1. The biasing scheme in Figure 1 is certainly not the most stable of the possible schemes. A classic "fourresistor" biasing generally offers better stability over temperature, transistor process and power supply variations. However, the scheme is stable enough for many practical applications and has the advantage of only requiring two resistors: the collector resistor RC, and a feedback resistor Rf. The overall gain of such a stage is actually surprisingly well stabilized against transistor Beta.
Figure 1  The Circuit
Because this is a circuit I use frequently, I decided to make an Excel spreadsheet to ease the calculations. The spreadsheet started as a simple resistor value calculator (given a bias point) and later evolved to include some more advanced features such as noise estimation. I decided to share this spreadsheet in this article in hopes it will help other Electronics enthusiasts in these same tasks. The spreadsheet can be download through the link below:
Transistor Amplifier Calculator, Rev 11 
UPDATE: Added an Excel 2003 compatible version as some users can't open the latest excel formats.
Transistor Amplifier Calculator, Rev 11 (Excel 2003 compatible) 
The spreadsheet is divided into four main 'worksheets' (bottom tabs) each with a different purpose. The following sections describe each of them, Look at this article as the spreadsheet "User Guide".
The 'About" worksheet outlines the three main steps assumed in the design (see Figure 2). Usually a transistor amplifier stage is designed starting with some biasing conditions from which the initial resistor values will be derived. Since the exact resistor values are normally not available, the second worksheet 'R Given' allows the user to enter standard resistor values and see in turn how these affect the 'real' biasing point. You may need to iterate back and forth between these two first worksheets until a satisfactory solution is found. For most designs, this will be all that is needed. More advanced users however, may want to estimate the total output noise. The last worksheet "Noise Analysis" provides an estimate of the total output noise and also a Power Spectrum Density plot over frequency.
Figure 2  Three Steps Approach
Let's now look at the three main design worksheets in some more detail. The example used throughout is the first microphone amplifier stage in my Karaoke Mixer project.
Figure 3 shows the "Bias Given' spreadsheet. As shown in Figure 3, I used the convention that inputs to the spreadsheet are marked in green, whereas calculated outputs are in orange. Since the first stage of a multiplestage amplifier is the one that most determines the overall noise performance (formally this is known as the Friis equation), this stage was designed to minimize noise. Since noise is a strong function of the collector current, I chose a relatively small 400 uA current. The power supply was set at 5V (same as the USB supply) . I also chose to set the VC collector bias voltage near the middle of the supply (5/2 = 2.5V) so as to minimize nonlinearity effects as the output approaches the supply rails. These nonlinearities can cause harmonic distortion which is an important consideration for audio applications. This is not as critical at the first stage in the chain as in latter stages since the output votage is small, but it is a good initial target nonetheless. Optionally, you can also enter values for input and output capacitors and the spreadsheet will calculate the 3dB Highpass (HP) and Lowpass (LP) frequencies. The spreadsheet automatically calculates the gain, resistor values and the input and output impedance among other results. Notice that you can also use many of Excel's builtin data analysis tools such as "Goal Seek" to try to find an optimum value gby varying another value in the spreadsheet. This is one of the advantages of using excel rather than a normal simulation package such as LTSpice (though some of that is also possible with the later).
Figure 3  'Bias Given' Worksheet
Armed with the "ideal" resistor values obtained in the "Bias Given" worksheet, the user can then mve to the "R Given" worksheet as shown in Figure 4. For convenience, I also listed the 10% tolerance "standard" resistor values. You will typically select the nearest standard value from the list and enter it for Rf and Rc. The spreadsheet will then calculate the actual bias points. If these satisfy the requirements you are almost done. If not, then you can try selecting a different set of values that does and.or go back to the "Bias Given" spreadsheet to find a different bias point.
Figure 4  R Given Worksheet
Noise analysis is a more "advanced" topic and is probably where I spentt most of the time developing the spreadsheet. This is a simplified (and approximate) calculation following the techniques explained in the excellent book "Analysis and Design of Analog Integrated Circuits". Keep in mind this is only a rough approximation. In fact, I found that both LTSpice and my own practical measurements were a bit lower (10 to 20%) than the values calculated with the spreadsheet. It is good however to have some design margin builtin and the spreadsheet can be educative in the sense that is is very easy to play with parameters and see how they affect the total noise. The spreadsheet requires the user to enter a "source" and load resistance and some biasing parameters (linked to the "R Given" worksheet). Since noise is normally specified as an rms voltage over a frequency range of interest, the user is allowed to enter the upper frequency limit (fmax) as a parameter. The spreadsheet then calculates the noise by doing a numerical integration of the PSD over the frequency range specified.
It's educative to see how the collector current affects the output noise strongly (which is why I set it low to start with). It is also interesting to see that increasing the source resistance has a huge effect on the output noise as expected.
Figure 5  Noise Given Spreadsheet
The spreadsheet will also automatically plot the estimate output Power Spectrum Density function in a chart. This is shown in Figure 6 alongside the total noise over some common frequency ranges of interest (like DC to 20 KHz for audio applications).
Figure 6  PSD Plot
Comments, questions, suggestions? You can reach me at: contact (at sign) paulorenato (dot) com