Setting up a spreadsheet to calculate apportionment of service cha

[Guest]

On the sale of a leasehold property, service charge and ground rent are
payable at £X per annum from the start of the accounting period - Y - to the
end of the accounting period - Z. If completion of a sale - A - takes
place on a date between Y and Z, I need to set up a formula on an Excel
template (using Office XP) to enable me to calculate the balance of money due
back to the Client from A to Z.

I have been working it out on paper as X divided by 365 to get the daily
rate, multiplied by the number of days from A to Z.

To complicate matters, not all the accounting periods start and end with the
same dates, the months all have a different number of days in them and as a
complete novice to Excel, I just can't figure out how to do it but feel
that it ought to be possible.

Any pointers would be humbly and gratefully appreciated.

 

[IC]

You can do basically the same in Excel as you are doing on paper.

Assume period start date is in A2, annual charge is in B2 and sale date is
in C2. In D2 type:

=B2-(B2/365*(C2-A2))

This will give the return amount due in D2.

Ian