Financial projection modeling attempts to tell us simply a) how much, and b) when…that’s it! As such, all future projection values calculated in your financial model can be broken down into the following three major framework components:
= ( Base Value ) * ( Growth Factor ) * ( Start/Stop Timing Triggers )
where the Base Value is the output being measured, such as apartment unit rental income,
the Growth Factor is the compounded amount by which we are increasing the base value to a particular future period, and
the Start/Stop Timing Triggers are the binary values 1 or 0 that define when the mathematical product of the base value and growth factor should be allowed to flow through and populate a particular time period in that line item’s row, or not.
A more nuanced understanding of formula construction leads one to integrate the possibility of two additional components. The first is a Periodic Adjustment, which may or may not apply depending on the unit from which the base value is being calculated as it relates to the nature of the period in the model’s columns.
For example, if your Assumptions include an input for apartment rental income on a yearly basis, and your model is built such that each column is equal to one month, then the formula will need to divide the base value by 12 to correctly show only the amount of rent received in each month.
The second potential additional component is a Distribution Adjustment, which relates to whether the base value must be further adjusted on a proportional basis around one or more milestones that define the transaction.
For example, if we consider there to be three phases in a ground-up real estate development transaction: pre-construction, construction, and post-construction, and a particular line item is distributed in different proportions within those three phases, such as 60% on a straight-line basis during pre-construction, 20% during construction, and 20% during post-construction, then we must include a formula component that that appropriately allocates the correct proportion (percentage) to each of these three phases.
Accordingly, the comprehensive version of the REFM 5-Component Framework for Financial Model Projection Formula Construction is:
= ( Base Value ) * ( Growth Factor ) * [ Periodic Adjustment ] *
[ Distribution Adjustment ] * ( Start/Stop Timing Triggers )
where the Periodic Adjustment and the Distribution Adjustment, both shown in brackets, are optional formula components.
Here’s an example for a 20,000 SF office tenant’s monthly rent in Year 5 of the lease:
= ( Annual Base Rent of $30.00 PSF x 20,000 RSF ) * (1.03^4) * [1/12] * [ 1 ] * ( IF(current month is within apartment building ownership time period + 12 months,1,0) )
where the assumed annual rent growth rate of 3% is grown for 4 years, and then adjusted to a monthly value ($56,275.00),
and where we purposefully let the rent value run out for 12 months past the point of intended sale to allow for the calculation of a 12-month forward NOI for the purpose of sale capitalization.
Here is a spreadsheet that shows all of this.
The real skill and art comes into not making mistakes in calculating the Base Value, and in making your formulas across multiple lines and worksheets accommodate for one another given their inherent interconnected nature.
Thoughts?