[Solved] IF Syntax for Loan Spreadsheet

[WAS2019]

I have the following formulae for calculating Loan Payables/Receivables:
For Principal Payment column [Cell E5]:
=IF(C5>(F4+D5);C5-(F4+D5);0)
For Interest Balance column [Cell F5]:
=(F4+D5)-IF(C5>(F4+D5); 0; (F4+D5)-C5)
For Principal Balance column [Cell G5]:
=G4+B5-E5
For Total Balance column [Cell H5]:
=F5+G5
Where
B5 = Loan
C5 = Payment
D5 = Interim Accrued Interest
F4 = Previous Interest Balance

The Interim Accrued Interest calculates fine, so I haven't included the formula. The Principal Balance calculates fine also.

But neither the Principal Payment, Interest Balance nor Total Balance columns calculate any amounts but simply show $0.00.

What have I done wrong?

[FJCC]

It is very hard to tell what the problem is without seeing your file. Can you post a small example here? You can delete everything except a few rows and change the numbers if they are confidential. To upload a file, click Post Reply and look for the the Upload Attachment tab just below the box where you type a response.

[RusselB]

You might want to review the Help file for the Financial Functions.

[WAS2019]

FJCC,
It's easier to upload the entire file, nothing much being confidential.
So here it is.

[FJCC]

The logic of the formula in column F, your Interest Balance, is wrong

Code: Select all

=(F4+D5)-IF(C5>(F4+D5);0;(F4+D5)-C5)

Notice that if the condition C5 > (F4 + D5) is FALSE, then you effectively have

Code: Select all

(F4 + D5) - (F4 + D5) + C5

which is equal to C5, your payment.
I think the formula you intended to have is

Code: Select all

IF(C5>(F4+D5);0;(F4+D5)-C5)

In words: IF the payment (C5) is greater than the past interest balance (F4) plus the current accrued interest (D5) THEN reduce the interest balance to zero OTHERWISE subtract the payment (C5) from the sum of the past interest balance (F4) and the current accrued interest (D5).

A somewhat simpler formula would be

Code: Select all

=MAX(0; F4 + D5 - C5)

Edit: There may be other problems but I need to go now. I will check back later.

[FJCC]

Continuing from my last post. The formula in E5 (Principal Payment) is correct but in E6 you start using

Code: Select all

=C6-(IF(C6>(F5+D6);(C6-(F5+D6));0))

This has the same problem as the formula in column F. If C6 > (F5 + D6) is TRUE, you calculate

Code: Select all

C6 - C6 + (F5 + D6)

which is just the Total Interest and not the Principal Payment use

Code: Select all

=IF(C6>(F5+D6);(C6-(F5+D6));0)

or

Code: Select all

=MAX(0; C6 - (F5 + D6)

[WAS2019]

FJCC,
Thanks Much!
My first employer had a plaque w/ a crest and the initials
TTT
standing for Think Things Through. I failed to follow it here.

For some reason I supposed that the *entire* condition would yield either the first or second variable, rather than that the formula portion _preceding_ the IF formula would still be calculated. Hopefully I will remember this and apply TTT for future spreadsheets.
As to your second post, I believe I recognized the original E5 Principal Payment formula was incorrect, modified it, and then deferred copying it to the below cells until the Interest Balance formula was resolved. I have now copied it, as well as (of course) corrected the latter formula.
You were entirely correct on deciphering my intent, and thus realizing what needed to be changed to achieve the objective. It now works fine!
As far as the MAX function, I was wholly unfamiliar w/ it. While I have retained the corrected IF function for this spreadsheet, I'll review the MAX function operation and consider employing it in the future.
Again, THANKS MUCH!