how to calculate cost streams over time
Elizabeth Betts Jul 29, 2016 2:15 PMHi,
I’m trying to show our budget (both past and future) and I’m struggling. All of the budget examples I found have a single date for their payments while I have a start and expiration date for scheduled ongoing payments.
I’m attaching a sample of my data. I work in real estate and I have rent streams for each of our properties. A rent stream consists of a few key elements including (per the columns in the
attached .xls file)
 The site unique ID
 The amount of the payment
 The reason for the payment
 The frequency of the payment (0 is a onetime payment on the commencement date, 1 is a monthly payment, 12 is an annualpayment  the frequency represents the number of months in the period)
 When we started making the payments (commencement date)
 When the payment are scheduled to end (expiration date)
Per my attached tableau workbook I have been able to graph my data at the year level, but it’s not quite right on 2 accounts:
 The results per year are based on the monthly cost of the rent steams in that year… however, I don’t know how to take month timing into account. For example, I have a rent streams on CO001499 starting on 11/1/2007 totaling $2,660 per month. I would like to show that in 2007 we paid $5,320 (November and December at $2,660 each). I don’t know how to make this adjustment without impacting the whole rent stream (via tableau’s “Running_Sum” for future years (eg: all of those rent streams are active in 2008 so we paid $31,920 that year  $2,600 * 12 months). This same issue is in reverse for when leases expire.
 I’m not quite sure how to deal with frequency… I think it needs to fold into the same equation as will be used for monthly payments
I had considered using these formulas to come up with a monthly sum. But again, these recalculate the whole rent stream and thus impact each of the years (not just the start / stop year).
For when leases start: ([Amount]/[Frequency])*(13month([Commencement]))
For when leases end: ([Amount]/[Frequency])*month([Expiration])
FYI: I modeling my work on this article…
http://kb.tableau.com/articles/knowledgebase/showrecordswithinperiodoftime
Any help would be greatly appreciated.
Thank you,
Beth

example lease data.xlsx 33.7 KB