I have a calc sheet that maintains a loan amortization/payment history. Each time I make a payment I enter the payment amount and the calc sheet shows me the payment breakdown into principle, interest, escrow, etc… All cells are
Currency formatted. The column definitions are as follows:
D: Escrow Amount
F: Principle Balance
G: Payment Amount
H: Interest Amount
F17 is populated with this formula:
=F16+H17-G17+D17. This just means, the current Principle Balance equals the previous principle balance plus the current interest charge plus the current escrow charge minus the current payment amount.
It is likely that the escrow amount in
D will be a fixed amount for a year until it is recalculated based on current tax rates. So rather than typing the escrow amount in each row (for each payment) I would like to convert
D into a formula which basically says, if there was any balance left on the loan after the previous payment, then just copy the previous escrow amount into this months payment. This way, I only have to enter an escrow amount each time that value changes, once a year. The rest of the time the sheet auto populates it with the previous value. But, once the loan is paid in full, and there is no current balance to be paid, then the escrow amount should show 0 (zero) because there is no payment to be made. To accomplish this I am using this very simple formula:
=IF(F17 > 0,D17,0)
=IF(F18 > 0,D18,0)
=IF(F[n-1] > 0,D[n-1],0)
This just means the current escrow amount is the same as the previous escrow amount if there is any principle remaining to be paid. If there is no principle left to be paid then the escrow amount is 0 (zero).
At least, this is what I think it SHOULD mean. But the formula is not working, meaning the boolean expression checking if the principle remaining is greater than zero always returns true, even when there is no principle balance remaining.
$0.00, but actually it is
I don’t understand what I am doing wrong. I know my logic is correct, but there is probably a problem with data types in the formula. Is there any reason an
IF statement would always return true for a formula populated,
Currency formatted, cell when checking if the value is greater than 0 (zero)?
What do I need to change to get this to work?