Allocation Order Formulas

[KMH23]

Hello,

I work for a real estate company that does not want to invest in property management software. Every property has a report that is set up in Excel to keep track of rent payments, etc.

If the tenant pays for storage or parking in addition to rent, then the payments must first be allocated to storage, then parking, then rent. Right now I manually break out every allocation for every unit which is very time consuming.

I would love to have a formula where I can enter the total payment and it will auto distribute in order. Does something like this exist?

The columns are broken out by market and what they should be paying:

Column F - Base Rent (what they should pay to be used as a reference for allocations)
Column G - Base Parking (what they should pay to be used as a reference for allocations)
Column H - Base Storage (what they should pay to be used as a reference for allocations)
Column I - Current Rent Payment (what I manually allocate based on payment received - allocate to here third)
Column J - Current Parking Payment (what I manually allocate based on payment received - allocate to here second)
Column K - Current Storage Payment (what I manually allocate based on payment received - allocate to here first)

I hope this makes sense.

Thanks,

 

[Joe4]

Welcome to the Board!

Where is the total Payment amount being entered (which column)?
What if their payment amount exceeds the Base rates for all three? What happens to the overage?

 

[Joe4]

Assuming your data starts in row 2, and your payment is being entered into cell A2, here are formulas that I think should do what you want.

Cell K2: =MIN(A2,H2)
Cell J2: =MAX(MIN(A2-H2,G2),0)
Cell I2: =MAX(A2-J2-K2,0)

If you want to cap the value in I2 to not exceed the amount of base rent in F2 (if they pay more than all three combined), change the formula to this:
Cell I2: =MIN(MAX(A2-J2-K2,0),F2)

 

[KMH23]

Welcome to the Board!

Where is the total Payment amount being entered (which column)?
What if their payment amount exceeds the Base rates for all three? What happens to the overage?

Hi Joe,

Thank you for your reply. The total payment amount is currently being entered into Column I (Current Rent) and then the parking and storage is subtracted and added into the appropriate columns.

If the payment amount exceeds all three bases, the overage will go into the Current Rent - Column I.

I can create a new column to record payments if necessary. All payments begin on line 8.

 

[Joe4]

So, the amount is being entered into column I, and then you want it to be allocated to the other columns, and column I to be updated as well.
That is going to require VBA, as cells in Excel can either have hard-coded amounts, or formulas, but never both simultaneously (so column I could not hold both your original value and formula to calculate what is left).
Is VBA acceptable? If so, you cannot enter the total payment into the same column where you want the rent calculated.

 

[KMH23]

So, the amount is being entered into column I, and then you want it to be allocated to the other columns, and column I to be updated as well.
That is going to require VBA, as cells in Excel can either have hard-coded amounts, or formulas, but never both simultaneously (so column I could not hold both your original value and formula to calculate what is left).
Is VBA acceptable? If so, you cannot enter the total payment into the same column where you want the rent calculated.

I added a column to the report and used the formulas you provided above and this is FANTASTIC! This is already a breath of fresh air - thank you so much.

We are just starting to learn how to create macros for clearing out info after each fiscal year. How can I use VBA for allocation? Any suggestions or ideas are much appreciated.

 

[Joe4]

How can I use VBA for allocation?

Basically, it would just follow the same rules as I used for my formulas up in post 3.
However, I really don't see much of an advantage to using VBA for something like this. If it can be done pretty easy by formula, there usually isn't a need for VBA.