+ Reply to Thread
Results 1 to 7 of 7

Pro Rata Premium Calculation

  1. #1
    Registered User
    Join Date
    06-30-2014
    Location
    Bristol, England
    MS-Off Ver
    2010
    Posts
    6

    Question Pro Rata Premium Calculation

    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

  2. #2
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Rocester, New York, USA
    MS-Off Ver
    Excel 2007:10
    Posts
    2,019

    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. #3
    Registered User
    Join Date
    06-30-2014
    Location
    Bristol, England
    MS-Off Ver
    2010
    Posts
    6

    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. #4
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Rocester, New York, USA
    MS-Off Ver
    Excel 2007:10
    Posts
    2,019

    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. #5
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    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.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  6. #6
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Rocester, New York, USA
    MS-Off Ver
    Excel 2007:10
    Posts
    2,019

    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. #7
    Registered User
    Join Date
    06-30-2014
    Location
    Bristol, England
    MS-Off Ver
    2010
    Posts
    6

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Formula to Convert Gross Insurance Premium to Net Insurance Premium
    By jaguar1317 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-19-2014, 08:18 PM
  2. Pro Rata Calculation
    By Klsapp81 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-29-2011, 07:54 PM
  3. Calculating a value pro rata using dates
    By Sai Sari in forum Excel General
    Replies: 6
    Last Post: 07-31-2010, 03:29 PM
  4. pro rata pecentage
    By Kiwi06 in forum Excel General
    Replies: 3
    Last Post: 07-17-2010, 08:48 AM
  5. Formula for Pro Rata Multiplication
    By CC_mfc in forum Excel General
    Replies: 6
    Last Post: 09-25-2007, 08:21 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1