Every once in a while I go geek. I don’t apologize for it. It’s been a fixpoint in my life since I was old enough to crawl under the coffee table and push the big buttons on our bulbous and bright red 70s TV set.
The TV finally broke (NOT my fault), but the habit didn’t.
This time around it happened with Excel. I’m not the biggest Excel fan out there, but I needed to make a TCO calculation and basically wanted Excel to do two things for me:
- Distribute project costs over multiple years based on project-timeline.
- Distribute operational costs over multiple years based on how many years were between each payment.
Geeked out yet?
Lo and behold, all I could find online was that this was impossible. You could do nifty macros, you could color every other row, you could sum every Nth row, but if you had an invoice coming in every 3 years and wanted a generic way to get yearly budgets N years in the future, you were basically s… out of luck.
So I did this:
For distributing project costs over n years:
IF(AND(YEAR($startdate)=$currentyear;YEAR($enddate)=$currentyear); $projectcost; IF(AND(YEAR($startdate)=$currentyear;YEAR($enddate)>$currentyear); $projectcost/($enddate-$startdate)* (DATE($currentyear+1;1;1)-$startdate); IF(AND(YEAR($startdate)<$currentyear;YEAR($enddate)>$currentyear); $projectcost/($enddate-$startdate)*365; IF(YEAR($enddate)=$currentyear; $projectcost/($enddate-$startdate)* ($enddate-DATE($currentyear;1;1));0))))
I break the project cost down to a cost-per-day , and then distribute it over the years based on how many project days there are in each year.
The first IF is to determine if the start and end-date is within the same year, and the next three are to differentiate the first year from a “middle” year and the last year as each of these calculations are slightly different. Pretty straightforward.
For projecting operational costs with n years between them
IF($recurrence<>0; ROUNDDOWN((-(MOD(($currentyear-YEAR($startdate));$recurrence)) +$recurrence)/$recurrence;0)*$cost;)
This one is a little more complex and was the one I couldn’t find anywhere. The first half figures out if the time from the startyear to currentyear divided by the recurrence is a whole number (i.e. that it’s time to pay). If it is, it creates a 1, and then I just multiply that with the cost.
Now I don’t pretend to be an Excel wizard in any sense of the word, so don’t ask me technicalities about why I used one function instead of another. All I had was some very helpful colleagues who were excellent guides into Excel-syntax and once I understood most of how it worked, the rest sort of came on its own .
So if you can use it, feel free to share and use it as much as you like. I’ve attached an example of the whole thing working. Just play around with the input and try adding as many years as you like to see how it works.