Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1558
  • Last Modified:

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
0
swedishmotors
Asked:
swedishmotors
  • 2
  • 2
1 Solution
 
byundtCommented:
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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now