Start and End month conversion formula

I have problems with coming up with the exact dollar match of $3345.90 based on the original term of 5.87.  I need help with finding a better formula to get the total price match per the term months. A file that shows the formula I am using is attached.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David Johnson, CD, MVPRetiredCommented:
is Term the interest rate per year?  What I did was convert the interest rate/year to the daily interest rate (term/365) and used the DAYS between the End Date and the Start Date Where do you get $3,345.90 from?
MelbutAuthor Commented:
The 3345.90 is the actual quote amount based off of the number of term months which is 5.87. The term is equal to months not interest.  I apologize for not clarifying that.
☠ MASQ ☠Commented:
Your math is correct (including your nested IF).  are there any additonal costs? - the difference between the raw mathematical solution and the quote figure is around 0.425% is that an arrangement/admin fee?
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

MelbutAuthor Commented:
Unfortunately there are no additional cost/fees that I am aware listed and should not be in embedded in the bottom line.  Also, The formula under column F s/b (=I2*D2*E2) and not calculating off of col H2.

If I manual add the % or difference to the end of the formula in column I ( ie. =ROUND(H2,1)+0.07 and =ROUND(H3,1)+0.014) for rows 2 and 3 the total matches the quote amount.  However, not all of the 500 or so records have this issue, Its sporadic through the sheet, but I have to go through manually  and find the discrepancy and % or diff may be slightly different on each.  

The Term is the driver.  Column U needs to match back up with the term month in col G. for example

Term = 5.87    Conversion month off start and end dates = 5.84561129 which is lower than the Term and by rounding comes to 5.8 which makes it higher than the term.
Hi Melbut,

Your problem *might* be due to the original person who setup this arrangement using potentially ambiguous time periods - namely, 'months', since a month is not a well defined amount of time (it can be anything from 28 to 31 days).

Since the calculation calls for applying an exact number of days (start and end dates) to an ambiguous 'month', there cannot be a well defined answer, unless you also have some other definitions that handle that problem.

For example, many monthly agreements make it work by saying that the charge is $X per month or part thereof, so that the number of periods (months) is always an integer.

As an example, I have re-done the calculation converting everything to days (multiply the monthly charge by 12 and divide by 365).  Even that is not ideal, since not all years have 365 days of course, with 'year'; also suffering an ambiguous definition under some circumstances.

It gives yet another answer.

I cannot see how to get the target answer you are looking for though.

Hope that helps in some way!


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MelbutAuthor Commented:
Thank you for the explanation and attempt
Hi Melbut,

Where does the target amount come from?

Best answer is probably to go back to the source of that (or at least your immediate source), and ask for a detailed calculation.

You can then compare line by line (maybe period by period), and work your way to a reconciliation like that.

MelbutAuthor Commented:
Question didn't get resolved
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.