Simulate Circuits in Excel with some Ladderal Thinking

apologies for pun overload

So, filter fans, I hope you tried out my recent spreadsheet-modified simulation schematic method (“Excel tunes up your schematic files”). It’s a nice way of creating small circuit chunks with calculated component values, especially when the sums can’t be carried out using the math capabilities of your simulator’s preprocessor.

But say you now need to adjust component values to achieve some system goal, such as predefined frequency response or time behaviour, and there’s no closed method for working out those values. You’ll have to spend quite a bit of money to get proper circuit optimization features in a SPICE-compatible simulator; none of the free or affordable packages support it. And in today’s busy world, it’s hard to find the time to write bespoke analysis and optimization programs. I count myself lucky that I had to do lots of that in the past, because it’s such a good way of learning about practical circuit calculations.

In “An Excelent Fit, Sir!” we saw that the Solver tool in a spreadsheet can mold the coefficients of a factorized transfer function to fit a frequency response specification. That’s useful if you can define the transfer function first, then implement it with a suitable topology. Now, if you’re already comfortable turning transfer functions into component values and interconnections, then – congratulations, you’re a filter designer!

Let’s assume, though, that this time round you’ve got to optimize the response of an existing circuit without altering the topology, and that it isn’t a simple cascade of second-order biquad blocks. Maybe it’s like figure 1 – the bandpass filter from “Fainting in Coils” which you’ll find elsewhere in this Substack:

Figure 1: the bandpass filter from “Fainting in Coils”.

Now, you could analyze this by hand (homework! No, just kidding), or with a symbolic math package. This gives you a bunch of fat expressions for the component values, involving the coefficients of the specific form of transfer function that the circuit realizes.

Then someone in manufacturing tells you that this component here has to be that value because it’s got some approval, and, oh, of course those two inductors must stay equal, and at this point you might just throw up your hands in despair and wish that you could calculate the darned circuit’s response right there on the spreadsheet.

Shazam! Your wish is my command. Actually, the process of calculating the frequency response of a bunch of arbitrarily interconnected linear components is pretty straightforward. The bothersome part is the housekeeping – tracking what’s connected to what, and how to map this efficiently into the ‘nodal admittance matrix’, a set of complex-coefficient equations that can be solved for any of the voltages and currents in the network. This is really all that SPICE is doing in an .AC analysis. All the hard stuff lies in finding the circuit’s operating point, or in simulating the system in the time domain with all the ugly non-linearities created by semiconductor devices.

For our .AC analysis we can make things waaay easier by scratching out that word ‘arbitrarily’ and restricting ourselves to the common ‘ladder’ form of circuit, shown in figure 2. This form doesn’t require a full matrix solution, and can be solved with far simpler methods, which are described in circuit theory textbooks. We’ll see shortly why we use impedance to quantify the series branches and admittance for the shunt branches (the ones that go to ground). Note that the left-most (input side) branch is always a series one (for a voltage-driven network) and the right-most (output side) branch is always a shunt one:

Figure 2: the general form of a ladder network.

There are several ways of computing the frequency response of a ladder network. The most general involves breaking the ladder up into a cascade of interconnected ‘two-ports’. Each two-port’s voltage/current relationship is expressed as a 2x2 ‘chain matrix’ (look it up), and the overall port properties of the entire network are determined by multiplying up all these matrices. This can be done in a spreadsheet – I’ve tried it – but it’s more cumbersome than necessary for simple two-ports that are just lumped passive elements.

Much easier is this simple iterative procedure, which steps along the ladder and directly delivers the value of the attenuation An (the reciprocal of the gain) when you reach the end. The inputs to this algorithm are the complex impedances Zi of the series branches and the admittances Yi of the shunt branches.

Starting with A-1=0 and A0=1, calculate Ai=ZiAi-1+Ai-2 for i odd (series branch) or Ai=YiAi-1+Ai-2 for i even (shunt branch). After the last iteration at Yn, you can calculate the gain as -20log10(|An|) and the phase as ‑arctan(im(An)/re(An)) – and you’re done!

The complex-number calculations can of course be done by manipulation of the real and imaginary parts using regular arithmetic. But with a considerable saving in effort – though a definite reduction in readability – you can use the standard spreadsheet complex math functions, such as IMSUM() to add complex numbers, and IMPRODUCT() to multiply. For pure passive components the impedance and admittance expressions are easy to write down. The impedance of an inductor is just COMPLEX(0,2*pi()*<frequency>*<value>), and so on. Writing formulae for impedances in series in the series arms, and admittances in parallel in the shunt arms, is also trivial.

Now in Figure 1, the inductor to ground has a series resistance (within the component, not visible on the schematic). So here, first write down an expression for the series impedance of L and R, and then take the reciprocal to get the admittance. Then add the admittances of the other parasitics and of the shunt capacitor. In figure 3 you can see the calculations, and the formulae that produced them, laid out at a single frequency (26 kHz) for clarity (well, I hope it’s clear!):

Figure 3: all the calculations to give figure 1 filter’s response at 26 kHz.

Figure 4 shows the calculated amplitude response of the filter from 10 kHz to 100 kHz which (as it should do!) matches the response given in “Fainting in Coils”:

Figure 4: amplitude response of figure 1’s filter, calculated by Excel.

So, there we have it: response computation of circuits using a spreadsheet. And you get to see the clockwork of the simulation process in front of you; it’s much more hands-on, don’t you think? And of course you already know how to create a complete LTspice schematic from the modified circuit data (“Excel tunes up…” further down this Substack).

Soon, I’ll show how combining this ladder analysis technique with the use of the Excel Solver gives a means for automatically tweaking circuit values to get a better fit to the response you want – in either the frequency or the time domain. It’s even capable of creating complete filter designs from scratch, including unusual constraints. Ladders pray… that you’ll find this useful!