I am trying to create a formula that will automatically amortize prepaid insurance out every month based on an effective date and termination date. The problem I'm encountering is getting the formula to auto-calculate the # of days in each month, and to prorate the 1st and last month of the policy based on when the policy begins and ends.
My spreadsheet is setup as below:
Column E has the amount
Column F has any adjustments made to the amount
Column G is the effective/start date
Column H is the termination date
Column I is the period length (H-G+1) (the+1 to account for the start date being covered)
Rows J1 thru U1 are the months of the calendar year
With the rest of columns J thru U showing the monthly amortized rate
If I had a start date on January 15th and a termination date on December 11th, I need to show prorated rates for January (17 days) and December (11 days) & 100% monthly rate for Feb-Nov.
(And of course if the insurance doesn't start until March, there wouldn't be anything in Jan and Feb's columns.)
Since not all of our policies are a year long, I need it to calculate by counting days, so the amounts would vary by month.
Many many many thanks to any insight anyone can shed my way!
My spreadsheet is setup as below:
Column E has the amount
Column F has any adjustments made to the amount
Column G is the effective/start date
Column H is the termination date
Column I is the period length (H-G+1) (the+1 to account for the start date being covered)
Rows J1 thru U1 are the months of the calendar year
With the rest of columns J thru U showing the monthly amortized rate
If I had a start date on January 15th and a termination date on December 11th, I need to show prorated rates for January (17 days) and December (11 days) & 100% monthly rate for Feb-Nov.
(And of course if the insurance doesn't start until March, there wouldn't be anything in Jan and Feb's columns.)
Since not all of our policies are a year long, I need it to calculate by counting days, so the amounts would vary by month.
Many many many thanks to any insight anyone can shed my way!
