Amortization Template - How to Stop at Zero

[narayan1121]

I've created an amortization table for a loan, that feeds into some pro forma financial statements. I made it to allow me to change the number of payments, but as it stands now, if the number of payments is less than the number of rows in the amortization schedule, it keeps calculating past zero and into negative numbers.

So my question is: how to I make the rest of the schedule appear blank after the End Balance reaches zero, but will fill in automatically if I change the number of payments?

Thanks in advance for your help.

 

[Justinian]

Why don't you do conditionally formatting in the entire table to hide the negative values with white font?

 

[narayan1121]

That's a good idea, but it would only work for about half of it. The dates, cumulative principal/interest paid, and payment stay positive though.

 

[Joe4]

That's a good idea, but it would only work for about half of it. The dates, cumulative principal/interest paid, and payment stay positive though.

You can conditionally format the entire row based on the value in one field.
Just use the Absolute Reference sign ($) before the column in your conditional formatting formula, i.e.
=$E100<0

 

[narayan1121]

I tied it to 2 different fields to make sure it worked under a variety of scenarios (because of rounding), but outside of that this worked well.

Thanks to Justinian and Joe4!