Compound Projections using Pow() and Age()

Gantt Chart with Descriptions Projecting rental income with QlikView, using pow() and age() to calculate rental for the lifespan of each lease, based on the escalation % and lease end date.
Rental Projection

I recently needed to forecast monthly rental for all tenants, for a property client, with 3 pieces of information: the lease start date, the lease end date and the escalation %. The formula needed to calculate this is similar to the way you would calculate compuind interest; the Rental multiplied by the Escalation % to the power of the number of years into the lease, e.g., if Rental = R10000 and escalation = 8%, in year 3 of the lease the rental =

10000 * ( 1.08 ³ ) = 12597.12

In QlikView pow() is the "to the power of" function and age() gives you the age in years between two dates.

I created a calendar for each tenant with all of the months between their rental start and end dates and then used the following formula to calculate the projected rental in each month of the lease:

round(Rental * pow((100+[Esc%])/100, age(Month, [Rental Start])), 0.01)		as [Projected Rental]

In year 1, age = 0, so the Escalation % is raised to the power of zero, which = 1.

You can use the same principle to calculate anything that increases by a % over time - compound interest, projected sales, salary increases, etc.

Posted by Colin Hancox

Senior QlikView Consultant. Currently providing Business Intelligence solutions for various large companies in the retail and manufacturing sectors, using QlikView on Oracle and Microsoft AX databases.
Copyrights © 2016 All Rights Reserved by Insight Consulting.