Solved

How do I do a pro ration calculation in excel

Posted on 2010-11-26
4
1,207 Views
Last Modified: 2012-05-10

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
Comment
Question by:swedishmotors
  • 2
  • 2
4 Comments
 
LVL 81

Accepted Solution

by:
byundt earned 500 total points
ID: 34218239
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
 
LVL 1

Author Comment

by:swedishmotors
ID: 34227613
I get a number error when I try this. Prorated-Warranty-Calc.-.xls
0
 
LVL 81

Expert Comment

by:byundt
ID: 34227699
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
 
LVL 1

Author Comment

by:swedishmotors
ID: 34230507
Thank you very much for your help.
0

Featured Post

Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question