Hello.

I have attached the spreadsheet that I'm working with. What I'm trying to do is utilise the 'expiry premium calculator' section of the spreadsheet to automatically calculate the expiry premium.

The expiry premium is the premium at the end of the insurance term, in my case, 365 days. This is established by any additional premiums or return premiums that may have occurred throughout the insurance term.

If the renewal premium in 01/07/13 was £100 and there were no additional/return premiums within the year, the expiry premium would be £100.

If there was an amendment 6 months in to the policy period (01/02/14) with an additional premium of £50 then this charge is pro-rata'd for 6 months (01/02/14 to 30/06/14) so we'd then need to calculate the period prior to this in order to get the annual charge and therefore calculate the expiry premium. In this example the expiry premium would be £200 because effectively the charge would have been £100 if it was collected for the whole 365 days.

SO... rather than calculating this manually I want my spread sheet to do it. Especially because most policies will have multiple amendments made to them throughout the year.

The calculator needs to be able to cope with additional and return premiums.

I've attached my spreadsheet.

I'm assuming that for this to work we will need to know the renewal date (cell C36) the date of the amendment (H38) and the additional/return premium charged (I38). This should then automatically calculate the expiry premium at cell E47.

Hope you can help and I have provided enough information.

I tried messing around with this: http://www.excelforum.com/excel-gene...ing-dates.html but I didn't have the brain power to tweak it to what I needed.

Thanks.

Matt.

Renewal Review 2014 (Revised).xlsx