• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Extension Fee Calc with stop and start Date

J

jdavis

New Member
I am having problems getting the calculation of the extension fee to stop on a given month. I suppose using MAX or something like that would do the trick, but I don't understand how to use it. The attached should show you what I am trying to do.

I do appreciate your help.
 

Attachments

Try:
J6: =IF(AND($A$3>D6,$A$3<G6),A6*H6,0)
Copy down

You can see the logic in the If statement and adjust to suit your requirements
The And requires that all conditions within the () are true to be True and hence paid
 
Hui...

I can get that part, but I need the calculation to stop in the month prior to the loan payoff I:I. Payoff could be anytime during the current month of A3.
 
Hi Jim -

The formula is working as written and as described by you - the difference between rows 7&9 and 8 is that in Row 8 your Original Maturity Date is the same as the Month end date (the Row 5 date you're comparing it to).

Evaluate the formula (Formulas - Evaluate) and see the logic work - Perhaps just comparing the month and year (not the actual day) will get you what you want?
 
David,

I have got the work-around but still not sure how to use month/year. Also how do I get the calculation to stop the month prior to payoff?

=IF(AND(MONTH(AL$5)>MONTH($D10),EDATE(AL$5,-1)<EDATE($D10,$G10)),$B10*$J10,0)

Jim
 
Will identifying the last day of the month prior to payoff help you in this calculation? I think it will ... so you can use the following ... your Date loan Paid off is in K10 in your example ... =DATE(YEAR(K10),MONTH(K10),0) will return the last day of the prior month (ie the zero day of this month is the last day of last month ...) You then compare your Row 5 date with the final day you calculated ...
In your example on Row 10, the final payoff date is 05/05/2015 - which makes the last day of the prior month 04/30/2015 - if the value of Row 5 is greater than 04/30/2015, then the payment is 0 ...
 
David,

This works fine when there is a payoff date, but when I apply the formula to a row with no payoff date (K:K is blank) the result is zero, AE9.

=IF(AND(MONTH(AE$5)>MONTH($D9),EDATE(AE$5,-1)<EDATE($D9,$G9),AE$5<=DATE(YEAR($K9),MONTH($K9),0)),$B9*$J9,0)

Jim
 
jdavis said:
David,

This works fine when there is a payoff date, but when I apply the formula to a row with no payoff date (K:K is blank) the result is zero, AE9.

=IF(AND(MONTH(AE$5)>MONTH($D9),EDATE(AE$5,-1)<EDATE($D9,$G9),AE$5<=DATE(YEAR($K9),MONTH($K9),0)),$B9*$J9,0)

Jim
Click to expand...

Why not check for a value in the payoff date cell - if one exists, utilize the formula you derived, otherwise do nothing ...

You can use ISBLANK -

there is no ISDATE but this will check for a valid date =ISERROR(DATEVALUE(A1))
 
David,

I still get zero. Is my IsBlank statement not in the right position?

=IF(AND(ISBLANK(K9),MONTH(AE$5)>MONTH($D9),EDATE(AE$5,-1)<EDATE($D9,$G9),AE$5<=DATE(YEAR($K9),MONTH($K9),0)),$B9*$J9,0)


Jim
 
Sorry Jim - I've been golfing and playing squash all day .... :)

Upload your file -it's so much easier to analyze a file than a formula

D
 
Well I was able to fix your issue, but another one has appeared - your logic for comparing the month is flawed - you need the month&year to be compared, not only the month .... as that will lead to erroneous results.
I'm now off to have a root canal which will have me out of commission for a few hours at least - I'll put up the "fixed" file - I just fixed it for row 9 in the area you were working, but as I said it is not returning the correct result due to the month issue ...
 

Attachments

Ouch!

I saw that when I extended the formula across the entire row the the calculations repeated in the same months.

I will wait for your return.

Jim
 
You must log in or register to reply here.
Back
Top