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
swedishmotorsAsked:
Who is Participating?
 
byundtConnect With a Mentor 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.

Brad
0
 
swedishmotorsAuthor Commented:
I get a number error when I try this. Prorated-Warranty-Calc.-.xls
0
 
byundtCommented:
The formula was using dates in A1 and A2. You put yours in A1 and B1. Try moving the date from B1 to A2.

Brad
0
 
swedishmotorsAuthor Commented:
Thank you very much for your help.
0
All Courses

From novice to tech pro — start learning today.