# How do I do a pro ration calculation in excel

How do I do a proration calculation in excel?
I'd like to take inputs from the user -
Amount to prorate
Starting date - date product purchased
end date - date of failure/warranty claim
base warranty period - period where coverage is 100%
prorated warranty period - Period where warranty is prorated.  starts at day one regardless of base period.

Example:
Product is \$400
Purchased 12/1/07
Warranty claim 6/1/10
Base Warranty 24months
Prorated period 6 months

\$400 is prorated for 30months
LVL 1
###### Who is Participating?

Commented:
Assuming 12/1/07 is in cell A1, 6/1/10 is in cell A2, then the warranty benefit might be calculated as:
=400*MAX(0,1-DATEDIF(A1,A2,"m")*(DATE(YEAR(A1),MONTH(A1)+24,DAY(A1))<=A2)/30)

Full benefit for 24 months or less. \$80 benefit pro-rated to 0 over the last 6 months. Each successive month gets \$13.33 less benefit.

0

Author Commented:
I get a number error when I try this. Prorated-Warranty-Calc.-.xls
0

Commented:
The formula was using dates in A1 and A2. You put yours in A1 and B1. Try moving the date from B1 to A2.

0

Author Commented:
Thank you very much for your help.
0

All Courses

From novice to tech pro — start learning today.