Rental instalments formula

[Phops]

Hi,

Wondering if anybody knows a way to model this. I'm trying to prepare a rent forecast in 6 monthly splits where rents increase (compound) annually but paid monthly.

An example is we have a 5 yr rental lease with an annual increase of 10% per year and the rent is paid monthly. What would my total rents paid be after years 0.5, 1, 1.5, 2 etc (6 monthly splits)

Initially i was using the FV formula. It works fine for whole years, however it doesnt seem to make sense for any period less then a whole year.

For example i was expecting to see that after the first 6 months that the total rent paid would be $50,000. However the formula shows $48,809.

The formula that im using for the $48,809 is:-

=-FV(0.1,0.5,100000,,0)

Does anybody know what im doing wrong or know an alternative formula?

The results that i'm wanting to see is:-

Year 0.5 = $50,000
Year 1 = $100,000
Year 1.5 = $155,000
Year 2 = $210,000

keep in mind that this a really simple example because with leases, they do start mid month.

Thanks in advance

[GeorgeF]

The initial Annual Lease amount may be placed in D1, e.g, $100,000. The A column is Dates. A1 thru A10 are the 6 month dates (0.5, 1.0, 1.5, 2.0, 2.5, 3.0, etc.).
The B column are the 6 month rental pyments. B1 and B2 equal half the Annual Lease, D1/2 or $50,000, in this case. Then B3 is =IF(B2=B1,1.1*B2,B2). Sweep this down column B such that B10=IF(B9=B8,1.1*B9,B9). That will give you equal rents during the year but each complete year will increae by 10%, which is a compound charge.
Column C is Total Income. C1=B1, C2=C1+B2. C3=C2+B3. Sweep this down until C10=C9+B10. This column shows the Total Rent received up through the current 6 month period.
Getting back to D1, that value may be changed for each of the different leases. A complete Table may be set up for the 10-year period for each of them in case they are different lease amounts only by altering D1.
I hope this helps. GeorgeF