|
Listen to this post if you prefer
|
When I got a call from a peer the other day, he had already been banging his head against the wall for an hour as to why a loan repayment formula wasn’t working for him. I opened the model and looked at it for about 60 seconds, and then told him that the formula was fine, but his assumption input for the timing of when the formula should trigger was off. He felt like crying. It had been staring him in the face the whole time, gloriously purposefully formatted in bold blue font to differentiate it as an “operator input”.
I tell this thrilling story to highlight my reasoning when approaching the issue, which was:
1. This peer is incredibly bright and meticulous
2. After an hour of his scrubbing the formula, the formula was likely logically sound (I trusted but verified)
3. After an hour of looking for a bug in a single formula in Excel, even the most persistent and driven of us are mentally and emotionally exhausted and want to see if our laptop will blend (bookmark this link to click In Case Of Emergency — it’s oh so cathartic).
After I verified that the formula was sound, I looked at what was going into the formula that was itself not a calculated value and the answer became apparent. It was simply that the repayment formula was being told to fire way too prematurely by the timing assumption input, and it was thus pulling in a zero dollar value because at the time of repayment there was no loan balance yet. Ugh… I’ve been there many many times.
We can be our own worst enemy when working in Excel through our input errors. The takeaway here is if you are perplexed as to how a problem continues to persist, heed Ayn Rand’s message: “Contradictions do not exist. Whenever you think you are facing a contradiction, check your premises. You will find that one of them is wrong.”
The faulty premise in this case was that the formula was incorrect when in fact it was correct. Unfortunately, our brains like to create foregone conclusions in a millionth of a second that, if flawed, trip us up in a big way.
Of equal danger and one of the most frustrating things about Excel is that Excel is 100% honest and will always carry out our instructions perfectly. Some friend.
Have any other tips to share? Don’t be shy! The sanity of a lot of good men and women is at stake here.
