… before you construct your projection formulas in Excel, spell them out in words.
For example, if I’ve got a formula that needs to be triggered at a certain time, such as the net sales proceeds of a property, I would start by writing (yes, with pen and paper, or by typing out) the following: “If this is the period in which the property is sold, then trigger the calculation of the sale amount, less selling costs, where the calculation of the sale amount is the result of next year’s NOI divided by the Exit Cap rate; otherwise, return zero.”
Once I have my head wrapped around the actions that the formula is supposed to perform and when it is supposed to perform them, then all I have left to do is the relatively simple task of matching Excel functions and arithmetic operators to each of the actions.
The more complicated your formulas become, the more helpful this technique will be. You can take the advice or leave it, but it has worked like a charm for me over and over again in writing formulas correctly, quickly and efficiently.
Enjoy.
P.S. Don’t forget about our easy framework for constructing formulas