Budgeting for Periodic Costs
From Zanecorpwiki
The goal here is take a simple table of periodic expenses and end up with two things:
- the amortized monthly expense
- the actual expense outlays in each period
First, set up your expenses:
The periodic cost and the period (shown here in months) are the key fields.
Now, we want to figure out how much money to set aside every month to cover these costs. The formula is simply the periodic cost over the period:
=B3/C3
This gives us the amortized monthly cost of the expense. Summing those expenses gives us the total amount to set aside monthly:
=SUM(D3:D14)
Now, for the tricky bit, we want to figure out how much cash we actually have to spend in each period. In order to make it a bit more compact. The two keys are the COLUMN() and SUMPRODUCT() functions. COLUMN() returns the index (starting at 1) of the current column, in this case converting our column location in the spreadsheet into a period. SUMPRODUCT multiplies two arrays. We'll use this and the MOD() function to convert our period and cost data into actual payments.
=SUMPRODUCT(MOD((COLUMN()-1),$C$3:$C$14)=0,$B$3:$B$14)
Let's dissect this a bit. First:
MOD((COLUMN()-1),$C$3:$C$14)
First, the COLUMN()-1 turns the column position into an absolute period. I'm starting in the second column (because we label the first) so that's why we throw the -1 in there. We usually think of MOD() as taking two numbers and returning the remainder, but here we're giving it an array as the second argument. This causes the function to apply the first value to each item in the array and return an array with the results. So, in the 1st period (associated with column 2), we'd get a result like:
[1 1 1 1 1 1 1 1 1 1 1 1]
and in the 12th period, we'd get:
[0 0 2 2 2 2 0 0 2 2 2 2]
The zero's tell us we can expect a payment in that period, so to convert that information to something useable, we do:
MOD((COLUMN()-1),$C$3:$C$14)=0
The '=0' applies a conditional test to the array, which results in another array of 0s for false and 1s for true, so our 12th period result becomes:
[1 1 0 0 0 0 1 1 0 0 0 0]
Then back to the full formula:
=SUMPRODUCT(MOD((COLUMN()-1),$C$3:$C$14)=0,$B$3:$B$14)
The second array is an array of the actual costs. Each element in the array is multiplied by the corresponding element in the "in this period" array we just went through, so, as we wanted, in the 12th period we account for the insurance, taxes, and our miscellaneous repairs.
=SUMPRODUCT([1 1 0 0 0 0 1 1 0 0 0 0], [650 2600 5500 5500 600 600 300 200 250 250 250 250]) =SUM([1*650 1*2600 0*5500 0*5500 0*600 0*600 1*300 1*200 0*250 0*250 0*250 0*250]) =SUM(650, 2600, 0, 0, 0, 0, 300, 200, 0, 0, 0, 0) =3750
So, in the 12th month of every year, we can expect to pay out $3,750 dollars.
One last trick: I like to figure my costs by month but do my reporting by quarters to keep things sane. To handle that, we do:
=SUMPRODUCT(MOD((COLUMN()-1)*3,$C$3:$C$14)=0,$B$3:$B$14)+ SUMPRODUCT(MOD(((COLUMN()-1)*3)-1,$C$3:$C$14)=0,$B$3:$B$14)+ SUMPRODUCT(MOD(((COLUMN()-1)*3)-2,$C$3:$C$14)=0,$B$3:$B$14)
And get something like:
