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'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

2. Re: Pro Rata Premium Calculation

I think this will do it for you in E47:

``Please Login or Register  to view this content.``
I'm not 100% clear on what the heck the coefficient you're using Pro rata is supposed to be, so you might have to jigger the math in the second range in the SUMPRODUCT, but I think this approaches what you want.

3. Re: Pro Rata Premium Calculation

Thank you very much, that's perfect.

Now being the perfectionist that I am, is there a way to make the cell blank rather than display '#DIV/0!' before the details are input? I'm sure I've read and even used a formula before but it's complete escaped me.

Thanks.

4. Re: Pro Rata Premium Calculation

ah, we'll just wrap the SUMPRODUCT with an IF checker.

``Please Login or Register  to view this content.``
That way it'll only run the SUMPRODUCT if you've (1) entered a start_date, and (2) entered at least one MTA_date.

5. Re: Pro Rata Premium Calculation

Since both of you use a excel version 2007 or higher, you also can use the formula, iferror

c47 =
``Please Login or Register  to view this content.``

6. Re: Pro Rata Premium Calculation

I thought about using IFERROR, but if H38:H45 is populated and C36 isn't, then it's gonna invert the array and deliver wrong results.

Well, if I was really clever, I would have realized I was allowing an error condition when I set the formula up in the first place, but w/e.

7. Re: Pro Rata Premium Calculation

Yeah the IFERROR does't work properly and doesn't calculate it correctly so I've stuck with the original suggestion. Thank you again for your help, much appreciated

There are currently 1 users browsing this thread. (0 members and 1 guests)