Allocating part payments to rental ledger

Hello
I have made up a spreadsheet to help me manage my properties, which work well when the tenant pays the total rent themselves.
The problem I have is that when the tenant is subsidsed by another party that pays me every 28 days (every 4 weeks rather than monthly).

The spreadsheet that I have made up is usually protected that all that needs to be entered is the date and payment amount in the rent section to prevent formulas being changed, however for this attachment it is not protected......
Looking at the attached spreadsheet, I would like to be able to enter the date and amount in column B & D, but the amount I get every 4 weeks is calculated into the sheet fortnightly to give me the exact amount the tenant is liable for fortnightly.
For example, the bold red figures are the the subsidised amount but when I enter, the whole amount comes off the total, making it look that the tenant is well in advance with rent, till the subsidised rent is used up instead of applying 50% of the subsidised amount (as the tenant pays fortnightly). Is there a way to achieve this?
Have also included section where I can add rent increases, and it means that I just have to change the references to H7 & H9 where they reference back to D8:D11 to reflect the new rents...... Is that the easiest way to do it?

I hope this makes sense.

Thanks in advance

Confused: Payment Dates/Next Payments dates are 7 days different?

What calculation needs to be done in what column(s)?

Hi
On this sheet I have manually entered what I would like the end result to be.

I receive a subsidised amount on behalf of the tenant for every 28 days as at A14, but seeing the tenant only pays every 14 days (fortnightly rent payments), I would like to apply the subsidy every 14 days to reflect at the tenant payment date what the tenant is actually due to pay. I have been calculating this manually before to figure out what the actual payment the tenant was due to pay. The subsidy amount varies, but for this exercise to make it easy to work out I selected 25% subsidy.

My thinking the formula for E16:E100 would mean and if/and/or statement
so E16 would equal the following arguments
if A16="Direct" then E16 would equal D16
if A16="1Subsidy" then E16 would equal D16/2
if A16="2Subsidy" then E16 equals the remainder of the subsidy at the previous "1Subsidy" in column D

Is this the way it could be done or should it be linked through another sheet?

You could compare this sample I have played with and the previous one.

Thanks in advance

Hi John

Payment date is for the date payment is received and next payment is calculated to the closest 7 days of full rent when the next payment is due, and shows minimum amount due to make up a full weeks payment.

Thanks JeteMc
The formula got stuck at E16, probably for the formula at D16 that refers to the manual input at A16 for "2Subsidy".
Or is there another workaround that will enter calculations for the "2Ssubsidy" on row 16 automatically (as per it is manually shown) when the next direct payment is added instead of manually entering the "2Subsidy" to complete that row?

Thanks