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.
Forumula-issue.xlsx
Forumula-issue.xlsx
ASKER
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.
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?
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
Alan.
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.
Alan.
ASKER
Question didn't get resolved
Forumula-issue.xlsx