Need help with formula......

[Dan B]

Hi All,
I can't quite get this to do what I need...
I have a spreadsheet that calculates a monthly payment of an insurance
premium by taking the annual premium and dividing it by 12. There are 2
cells that contain dates....a beginning date and ending date of the policy.
I have a formula that calculates how many months are between those 2 dates.
Then I have the due dates for the monthly payments listed for each month of
the year.
For Example:
A1: 01/01/04
B1: 01/01/05
A2: 12 months (formula calculates this)
A3: Annual Premium (dollar amount)
A4: 01/01/04 C4: monthly payment (A3/12)
A5: 02/01/04 C5: monthly payment (A3/12)
A6: 03/01/04 C6: monthly payment (A3/12)
and so on...

What I need is to have the monthly payments start based on the date in A1.
So if the policy begins in april, I want Jan-Mar to be blank, and
April-December to have the monthly payment, which would be the annual
premium /12 * 6 or (A3/12)*A2.

I hope that makes sense...Thanks for any help on this one.
Dan

 

[Kevin Stecyk]

Dan,

For each month payment, you could write something to the effect of....

=if(Current_Month<Start_Month, "", Proper Formula)

The "" is simply a null. You might want 0 instead of "". You can choose.

So for January....

=if(A4<A1, "", A2*A3/12)

Hope that helps.

Best regards,
Kevin

 

[Don Guillett]

something like
if(month(a1)=4,a3/12,"")

 

[Dan B]

That worked great. Thanks to all.

Dan,

=IF(A4>=A$1,(A$3/12)*A$2,0)
Type it by A4 and copy down.

Regards,
Felipe