Okay, let's rework the formula. If I understand you correctly, you are
wanting to calculate the total premium for a single given year, based on the
policy effective date and policy termination date. The Premium date is the
year that you are trying to calculate the premium for. You are trying to
calculate the number of days in the Premium Year, and then use the number of
days of the premium year, divide by 365, then multiply by the Annual premium,
and that is your resultant. That being said, why not use a formula like so:
=(MAX(MIN(TermYear2,DATE(PremYear,12,31)),DATE(Pre mYear,1,1))-MIN(MAX(EffYear2,DATE(PremYear,1,1)),DATE(PremYear ,12,31)))/365*AnnPremium
TermYear2 = actual end date of policy
EffYear2 = actual start date of policy
PremYear = year you are trying to calculate for.
AnnPremium = Annual premium amount.
It first determines the earliest date 12/31 of Prem Year, or Termination
date (the MIN). Then it takes that and calculates which is later, that or 1/1
of Prem Year.
Then it determines the later of 2 dates, the effective date of the policy,
or 1/1 of Prem Year, and compares that to which is earliest, that or 12/31 of
Prem Year.
If TermDate is less than PremYear, both calculations will come up to 1/1 of
prem year. If EffDate is greater than PremYear, both calculations will come
up to 12/31 of prem year. It ill take the first value and subtract the second
value, divide this by 365, and then multiply it by the Annual Premium.

Hope this helps.

--
John C


"plally" wrote:

I modified your formula a bit to accomodate my cells. Here is the modified
version.

When I inserted the six end parenthesis, Excel did not like that and
suggested changes to the formula.

=IF(OR(R4V3,S4<V3,),0,
(IF(AND(R4=V3,S4V3),YEARFRAC(V2-B4)*U4),
IF(R4=S4,T4*U4),
IF(AND(R4<V3,S4=V3),((C4-V1)/365)*U4)))

I am now getting the error: #N/A


"Roger Govier" wrote:

Hi

Using some of the explanation for your second posting, and combining it with
your first posting, I think that this is what you want.

=if(or(R4V3,S4<V3,),0,
if(and(R4=V3,S4V3),yearfrac(V2-B4)*U4),
if(R4=S4,T4*U4), (((TERM DATE - EFF DATE)/365)/365*ANN PREM),
if(and(R4<V3,S4=V3),((C4-V1)/365)*U4))))))

You will need to translate the TERM DATE etc to the relevant cells holding
the values.
--
Regards
Roger Govier

"plally" wrote in message
...
So, this is what I'm trying to do.

I'm trying to calculate premiums (PREM) for a given year (PREM YEAR) based
on the policy issue date (EFF DATE) and policy termination date (TERM
DATE).
I also need to look at policy issue year (EFF YR) and policy termination
year
(TERM YR). All policies have an annual premium (ANN PREM).

If the EFF YR PREM Year or TERM YR < PREM Year, Prem = 0
If EFF YR = PREM YEAR and TERM YR PREM Year, then perform the following
Calculation: (((last day of prem yr - effective date)/365)*ANN PREM)
If EFF YR = TERM YEAR, perform the following calculation: (((TERM DATE -
EFF DATE)/365)/365*ANN PREM)
If EFF YR < PREM Year and TERM YR = PREM Year, perform the following
calculation: ((TERM DATE - day 1 of Prem Year)/365)*ANN PREM)

my formula points to a cell that includes PREM YEAR, Day 1 of Prem Year
and
Last day of Prem Yr.

Thanks for taking a look at this.

"John C" wrote:

I tried to decipher what you are meaning, but I can't tell where one IF
statement ends, and the next begins....
=if(or(R4V3,S4<V3,),(if(and(R4=V3,S4V3),(yearfra c(V2-B4)*U4),if(R4=S4,T4*U4),if(and(R4<V3,S4=V3),(((C4-V1)/365)*U4),0))
Might I suggest writing the formula inside out? in other words, writing
your
last IF statement first, then modifying it with the 2nd to last.
For example:
=IF(AND(R4<V3,S4=V3),((C4-V1)/365)*U4,0) : Note, this assumes that the ,0
is
for this IF statement. Your statement is difficult to decipher if the ,0
is
for this one, or for the original statement.
Go Back to the statement, add the next IF statement outside of your first
(which I 'think' your last IF statement is if your 2nd to last is
negative....
=IF(R4=S4,T4-U4,last if statement)
etc.

If this doesn't help, let us know what conditions you are testing for,
and
what the result you want to be is.


--
John C


"plally" wrote:

Here is my If statement. I am getting an error message "too many
arguments".
Can you tell me what I'm missing?

=if(or(R4V3,S4<V3,),(if(and(R4=V3,S4V3),(yearfra c(V2-B4)*U4),if(R4=S4,T4*U4),if(and(R4<V3,S4=V3),(((C4-V1)/365)*U4),0))

Thanks in advance,